PHP that returns null table fields that are neither empty nor null in Mysql

Question

I have a table "payment forms" and I made an API to get all these forms of payment, but even though the database is correct, it informs that the field "name" is null in return, see:

[{"nome":"Dinheiro"},{"nome":"Cheque"},{"nome":null},{"nome":null}]

And it’s not "null" and these are the only two.

Follow my PHP:

PHP:

    <?php


header('Content-type: application/json');
header('Access-Control-Allow-Origin: *');

include 'database.php';

//$cod_fornecedor=$_GET['cod_fornecedor'];


$query="SELECT
   nome
FROM
   formas_pagamento
ORDER BY
   cod_forma_pagamento ASC";


$result=$con->query($query);

$row_cnt = mysqli_num_rows($result);
if ($result->num_rows > 0) 
{
    $count=0;
    echo "[";
    while($row = $result->fetch_assoc()) 
    {
            $count++;
            echo json_encode($row);

            if($count!=$row_cnt)
            {
                    echo ",";
            }


    }
    echo "]";
}
else
{
echo "error";
}

?>

What’s wrong with it?

Answer 1

There are own functions that already make the association of the results, do not need to manually traverse and reinvent the wheel, running the risk of creating errors. Use the function mysqli_fetch_all:

$query = "SELECT nome FROM formas_pagamento ORDER BY cod_forma_pagamento ASC";

$result = $con->query($query);

$resultArray = $result->fetch_all(MYSQLI_ASSOC);

echo json_encode($resultArray);

Answer 2

Why complicate and "invite" errors?. There’s a much easier way to do this (untested code):

$output = [];
while($row = $result->fetch_assoc()) 
{
  $output[] = $row;
}
echo json_encode($output);

Answer 3

The json_encode requires UTF-8, probably this is already the format of your database, but you may not have specified this in the connection, as I explain in this reply:

• /a/43205/3635

If it’s OOP:

$mysqli = new mysqli('HOST', 'usuario', 'senha', 'banco');

if ($mysqli->connect_error) {
    printf('Erro de conexão: %s', $mysqli->connect_errno);
    exit;
}

/*
 * compatibilidade para to 5.2.9 e 5.3.0.
 */
if (mysqli_connect_error()) {
    printf('Erro de conexão: %s', mysqli_connect_error());
    exit;
}

//Define para UTF-8
if (false === $mysqli->set_charset('utf8')) {
    printf('Error ao usar utf8: %s', $mysqli->error);
    exit;
}

With procedural mysqli:

<?php
$link = mysqli_connect('HOST', 'usuario', 'senha', 'banco');

if (mysqli_connect_error()) {
    printf('Erro de conexão: %s', mysqli_connect_error());
    exit;
}

//Define para UTF-8
if (!mysqli_set_charset($link, 'utf8')) {
    printf('Error ao usar utf8: %s', mysqli_error($link));
    exit;
}

It’s not necessary wear utf8_encode recursively, as you did in your reply, just understand what UTF-8 is and know how to apply it, the answer I Linkei shows the step by step.

Answer 4

You can do it in a more simplified way that will work perfectly:

$query="SELECT
   nome
FROM
   formas_pagamento
ORDER BY
   cod_forma_pagamento ASC";

$arr = [];
foreach ($con->query($query, PDO::FETCH_ASSOC) as $row) {
    $arr[] = $row;
}

echo json_encode($arr);

If you have any questions, please visit http://php.net/manual/en/pdo.query.php

Answer 5

Solved using json_encode(utf8ize($Row));

Follow the new full PHP:

   $rest_api = array();
function utf8ize($d) {
    if (is_array($d)) {
        foreach ($d as $k => $v) {
            $d[$k] = utf8ize($v);
        }
    } else if (is_string ($d)) {
        return utf8_encode($d);
    }
    return $d;
}

/** connect to mysql **/
$mysql = new mysqli($config["host"], $config["user"], $config["pass"], $config["dbase"]);
if (mysqli_connect_errno()){
    die(mysqli_connect_error());
}


$query="SELECT nome FROM formas_pagamento ORDER BY cod_forma_pagamento ASC";

$result = $mysql->query($query);
//$result=$con->query($query);



if ($result->num_rows > 0) 
{
    $count=0;
    echo "[";
    while($row = $result->fetch_assoc()) 
    {
            $count++;

            echo json_encode(utf8ize($row));

            if($count!=$row_cnt)
            {
                    echo ",";
            }


    }
    echo "]";
}
else
{
echo "error";
}

?>