JSON encode MySQL results

Question

How do I use the JSON_encode function with mysql query results, do I need to iterate through the rows or can I just apply it to the entire results object?

Answer 1

www.php.net/mysql_query says "mysql_query() returns a resource".

www.php.net/json_encode says it can encode any value "except a resource".

You need to iterate through and collect the database results in an array, then json_encode the array.

Answer 2

$sth = mysql_query("SELECT ...");
$rows = array();
while($r = mysql_fetch_assoc($sth)) {
    $rows[] = $r;
}
print json_encode($rows);

Answer 3

The above will not work, in my experience, before you name the root-element in the array to something, I have not been able to access anything in the final json before that.

$sth = mysql_query("SELECT ...");
$rows = array();
while($r = mysql_fetch_assoc($sth)) {
    $rows['root_name'] = $r;
}
print json_encode($rows);

That should do the trick!

Pär