PHP mysqli_fetch_assoc not doing returning correct value

Question

I have an old PHP code that has mysql in it.

It gets an array from a SELECT statement, adds it to a JSON object, as a property and echoes the encoded JSON.

I changed it around to use mysqli, but when I try to get the rows, and create an array out of them, it just returns nothing.

Here's the old mysql code:

$con = mysql_connect('host','account','password');
if (!$con)
{
    //log my error
};

mysql_select_db("database_name", $con);
mysql_set_charset('utf8');
$sql = "SELECT field1 as Field1, field2 as Field2 from table where ID = '".$parameter."'";
$query = mysql_query($sql);
$results = array();
while($row = mysql_fetch_assoc( $query ) )
{
    $results[] = $row;
}
return $results;

Version1: Here's the new one that I tried writing:

$con = mysqli_connect('host','account','password','database_name');
$sql = "SELECT field1 as Field1, field2 as Field2 from table where ID = '".$parameter."'";
$results = array();
if($result=mysqli_query($con, $sql))
{
    while ($row=mysqli_fetch_assoc($result)) 
    {
        $results[] = $row;
    }
    return $results;
}
else
{
    //error
}

Version2: Second thing I tried, which only returns 1 ROW:

...same as above until $sql
if($result=mysqli_query($con,$sql))
{
    $row=mysqli_fetch_assoc($result);
    return $row;
}

Version3: Or I tried to completely mirror the mysql structure like this:

$sql = "SELECT ...";
$query = mysqli_query($con, $sql);
$results = array();
while($row = mysqli_fetch_assoc( $query ) )
{
    $results[] = $row;
}
return $results;

Wrapping the resulting array into the JSON:

$obj = new stdClass();
$obj->Data = $results;
$obj->ErrorMessage = '';
die(json_encode($obj)); //or echo json_encode($obj);

None of the mysqli version are working, so I was thinking there might be an important change in the way these arrays are created.

Any tips on what could be wrong on the first mysqli example?

With Version2 I can tell that the SQL connection is there, and I can at least select a row. But it's obviously only one row, than it returns it. It makes me think, that building up the array is the source of the problem, or it's regarding the JSON object...

LATER EDIT: OK! Found a working solution.

ALSO, I played around with the data, selected a smaller chunk, and it suddenly worked. Lesson from this: the function is not responding the same way for 40 rows or for 5 rows. Does it have something to do with a php.ini setting? Or could there be illegal characters in the selection? Could it be that the length of a 'Note' column (from the db) is too long for the array to handle?

Here's the working chunk of code, that selects some rows from the database, puts them into an array, and then puts that array into an object that is encoded into JSON at the end, with a statusmessage next to it. Could be improved, but this is just for demo.

$con = mysqli_connect('host','username','password','database_name');
if (!$con)
{
    $errorMessage = 'SQL connection error: '.$con->connect_error;
    //log or do whatever.
};
$sql = "SELECT Field1 as FieldA, field2 as FieldB, ... from Table where ID='something'";

$results = array();

if($result = mysqli_query($con, $sql))
{
    while($row = mysqli_fetch_assoc($result))
    {
        $results[] = $row;
    }
}
else 
{
    //log if it failed for some reason
    die();
}

$obj->Data = $results;
$obj->Error = '';
die(json_encode($obj));

Question is: how can I overcome the issue regarding the size of the array / illegal characters (if that's the case)?

Where do you define $con? Is it properly connected? What's your SELECT-query, do you know that is working? — Qirel
– Qirel, Commented Mar 8, 2017 at 16:24
Hi! $con is the connection handler, and it's working when it gets to this point. The SELECT also works, because I tried printing it out, and then pasting it into MYSQL workbench and it seems to work. — Laureant
– Laureant, Commented Mar 8, 2017 at 16:28
And where is your mysqli_error() ? What is the error you are getting ? — user3526204
– user3526204, Commented Mar 8, 2017 at 16:28
At the moment there are a few too many unknowns. Enable error-reporting and show all the relevant parts of the code (that includes the connection, how you use this code and the actual query). — Qirel
– Qirel, Commented Mar 8, 2017 at 16:29
Hi! I updated the question a little bit. I turned on error-reporting, and I have 0 errors in this script. All seems well. Actually after I return the array, I do a print Count($array); on it, and it displays the number correctly. But then when I add the resulting array to the object, it fails silently... — Laureant
– Laureant, Commented Mar 8, 2017 at 17:05

Community · Accepted Answer · 2017-05-23 11:46:58Z

Your "Version 1" seems to be correct from a PHP perspective, but you need to actually handle the errors - both when connecting and when performing the query. Doing so would have told you that you don't actually query a table, you're missing FROM tablename in the query.

Use mysqli_connect_error() when connecting, and mysqli_error($con) when querying to get back the actual errors. General PHP error-reporting might also help you.

The code below assumes that $parameter is defined prior to this code.

$con = mysqli_connect('host','account','password','database_name');
if (mysqli_connect_errno())
    die("An error occurred while connecting: ".mysqli_connect_error());

$sql = "SELECT field1 as Field1, field2 as Field2 
        FROM table
        WHERE ID = '".$parameter."'";

$results = array();
if ($result = mysqli_query($con, $sql)) {
    while ($row = mysqli_fetch_assoc($result)) {
        $results[] = $row;
    }
    return $results;
} else {
    return mysqli_error($con);
}

Error-reporing

Adding

error_reporting(E_ALL);
ini_set("display_errors", 1);

at the top of your file, directly after <?php would enable you to get the PHP errors.

NOTE: Errors should never be displayed in a live environment, as it might be exploited by others. While developing, it's handy and eases troubleshooting - but it should never be displayed otherwise.

Security

You should also note that this code is vulnerable to SQL-injection, and that you should use parameterized queries with placeholders to protect yourself against that. Your code would look like this with using prepared statements:

$con = mysqli_connect('host','account','password','database_name');
if (mysqli_connect_errno())
    die("An error occurred while connecting: ".mysqli_connect_error())

$results = array();

if ($stmt = mysqli_prepare("SELECT field1 as Field1, field2 as Field2 
                            FROM table
                            WHERE ID = ?")) {
    if (mysqli_stmt_bind_param($stmt, "s", $parameter)) {
        /* "s" indicates that the first placeholder and $parameter is a string */
        /* If it's an integer, use "i" instead */
        if (mysqli_stmt_execute($stmt)) {
            if (mysqli_stmt_bind_result($stmt, $field1, $field2) {
                while (mysqli_stmt_fetch($stmt)) {
                    /* Use $field1 and $field2 here */
                }
                /* Done getting the data, you can now return */
                return true;
            } else {
                error_log("bind_result failed: ".mysqli_stmt_error($stmt));
                return false;
            }
        } else {
            error_log("execute failed: ".mysqli_stmt_error($stmt));
            return false;
        }
    } else {
        error_log("bind_param failed: ".mysqli_stmt_error($stmt));
        return false;
    }
} else {
    error_log("prepare failed: ".mysqli_stmt_error($stmt));
    return false;
}

Collectives™ on Stack Overflow

PHP mysqli_fetch_assoc not doing returning correct value

1 Answer 1

Error-reporing

Security

References

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Error-reporing

Security

References

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related