MySQL returning null wrongly - MYSQLI PHP

Question

MySQL returning null wrongly - MYSQLI PHP

Navigation

#1 by (3 votes)

2

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

ItstillreturnstherowsthatIselectedfromthetable,butsomevaluescomenullalso:

Follow 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've tried to see if it could be some MySQL syntax error, but I threw the query directly into the database and it worked fine. I had the same problem with another code I was doing yesterday and could not resolve. It's strange, considering that I always do the same thing and it works ...

Where am I going wrong? Can anyone help me?

Thank you.

php mysql mysqli

asked by anonymous 22.12.2014 / 20:29

1 answer

CheckBox checked / unchecked does not follow instructions How can I add a tagname (data-state) to my input hidden with attr?

score 3 · Accepted Answer

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

json_encode () accepts strings only when encoded in UTF-8. If you find any during the encoding process, PHP will assume 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 reporting the array to json_encode () . Two easy ways to do this is with iconv or mb_convert_encoding ( ) .

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

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

    $result[] = array_map(

        function( $string ) {

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

        }, $row
    );
}

Here an anonymous function was used in the form of a Closure, available from PHP 5.3. I hope you are 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 most painstaking but appropriate alternative would be to create a new database, already in UTF-8 and re-insert all the data in UTF-8 as well.

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

Adapted from this answer in SOEn.