JSON encode MySQL results

php mysql json
Trick Jarrett picture Trick Jarrett · Dec 20, 2008 · Viewed 531.8k times · Source

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

Paolo Bergantino picture Paolo Bergantino · Dec 20, 2008 
$sth = mysqli_query($conn, "SELECT ...");
$rows = array();
while($r = mysqli_fetch_assoc($sth)) {
    $rows[] = $r;
}
print json_encode($rows);

The function json_encode needs PHP >= 5.2 and the php-json package - as mentioned here

NOTE: mysql is deprecated as of PHP 5.5.0, use mysqli extension instead http://php.net/manual/en/migration55.deprecated.php.

Related questions

PHP MySQL Google Chart JSON - Complete Example

I have searched a lot to find a good example for generating a Google Chart using MySQL table data as the data source. I searched for a couple of days and realised that there are few examples available for generating …

Read More
Replacing \r\n (newline characters) after running json_encode

So when I run json_encode, it grabs the \r\n from MySQL aswell. I have tried rewriting strings in the database to no avail. I have tried changing the encoding in MySQL from the default latin1_swedish_ci to …

Read More
MySQL 5.7+, JSON_SET value in nested path

For a recent development project, we're using MySQL 5.7, so we can take advantages of the latest JSON-functions... I'm building an UPDATE-query, where an nested json-object should be inserted / added into the attributes-column, of type JSON, see query below. UPDATE `table` …

Read More