Mysql returning null improperly - MYSQLI PHP

Question

I’m trying to make a SELECT through PHP mysqli, but it’s returning null in all parameters of the object returned by $mysqli->query($sql).

Even so it returns the rows I selected from the table, only some values have come null also:

Follows my code:

$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
$cell_number = $_POST['cell'];

$sql = sprintf("SELECT * FROM SMS WHERE Number LIKE '%%%s%%'", $cell_number);
$query = $mysqli->query($sql) or die($mysqli->error.__LINE__);


while($row = $query->fetch_assoc()) {
    $result[] = $row;
}

die(json_encode(array('resultado' => $result, 'debug' => $query, 'sql' => $sql)));

I tried to see if it could be some Mysql syntax error, but I played the query directly in the database and it worked normally. I had the same problem with another code I was doing yesterday and I couldn’t fix it. It’s weird, considering I always do it the same way and it works...

Where am I wrong? Someone can help me?

Thank you.

Answer 1

Once again the great villains of history are the charset and the "achism" of PHP.

json_encode() accepts strings only when encoded in UTF-8. If you encounter any during the coding process, PHP will take that value as NULL, quietly, instead of giving you a touch with some Notice, for example.

You can fix this by changing the encoding of your data before you inform the array to json_encode(). Two easy ways to achieve this is with iconv or mb_convert_encoding().

For example, assuming your information in the bank is stored with charset latin1, you would do that:

while( $row = $query->fetch_assoc() ) {

    $result[] = array_map(

        function( $string ) {

            return iconv( 'iso-8859-1', 'utf-8', $string );

        }, $row
    );
}

Here was used an anonymous function in the mold of a Closure, available from PHP 5.3. I hope you’re using at least this version, but in any case you can do:

while( $row = $query->fetch_assoc() ) {

    $result[] = array_map( 'cb', $row );
}

// ...

function cb( $string ) {
    return iconv( "iso-8859-1", "utf-8", $string );
}

The more laborious but more appropriate alternative would be to create a new database, already in UTF-8 and re-insert all data also in UTF-8.

Of course you wouldn’t have to do it manually, you could create a script that would do it for you, but that’s another five hundred.

Adapted of that answer in the Soen.