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?

Vicar

I know that this is a very old question. But nobody shows the simplest alternative to fixing the problem of integers showing up as strings. @mouckatron offers the JSON_NUMERIC_CHECK flag of json_encode() in the answer below. Simple and it works like a charm! #1391483 – Garceau

There is a matching question + answer concerning the string-type-problem at: #28262113 – Penult

Answer 1

$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

Modern PHP versions support mysqli_fetch_all() function that will get your array in one go

$result = mysqli_query($conn, "SELECT ...");
$rows = mysqli_fetch_all($result); // list arrays with values only in rows
// or
$rows = mysqli_fetch_all($result, MYSQLI_ASSOC); // assoc arrays in rows

print json_encode($rows);

Crepitate

I would as advise as you to mention that during the select query to use AS to rename the columns to something for public such as SELECT blog_title as title, this is cleaner and the public do not know what the exact columns are from the database. – Torrent

This code erroneously encodes all numeric values as strings. For example, a mySQL numeric field called score would have a JSON value of "12" instead of 12 (notice the quotes). – Culver

@RobertPitt, security based on concealing names of your columns is security by obscurity! – Solleret

You may use my class: ajaxian.com/archives/php-based-mysql-to-json-converter – Briolette

@Solleret true, but knowing the exact column names makes SQL injection attacks considerably easier – Psychopath

@Tim: If you're getting to the point where your column names being known is the only barrier to SQL injection you've already lost, no? – Crepitate

@PaoloBergtino fair enough. But I would still rather not expose internals. I get a bad design feeling from such an interface. I have an inkling that the OP's real problem(not specified in the post) could be better solved anyway without sending a bunch of SQL result rows encoded as json. – Psychopath

Does "$rows[] = $r" append elements to the array each time? I don't see this usage of "[]" documented anywhere. Thanks. – Arcuation

@PaoloBergantino Can we convert the json data into mysql or we can query on json data ? – Brittaniebrittany

@RobertPitt, I agree but more on the grounds of defining the API interface. This allows the underlying table to be changed. Not necessarily for security reasons. – Milda

How do you then use the JSON object that is returned? var obj = JSON.parse(response); doesn't give me anything but null – Ridgepole

But I do have warning: Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given means mysql_query returns a boolean type and not some kind of class structure? – Pete

@mr5: If you are getting a boolean back for a SELECT query, it means it failed. From the documentation: For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error. – Crepitate

@Culver what's the fix then? – Mingy

@Culver There is a matching question + answer concerning the string-type-problem at: #28262113 – Penult

Make you ensure that numeric integers are not converted to string by JSON Encode. Use $sth = mysqli_query("SELECT ..."); $rows = array(); while($r = mysqli_fetch_assoc($sth)) { $rows[] = $r; } print json_encode($rows, JSON_NUMERIC_CHECK); – Ploy

Can you explain the usage of $rows[] = $r; because dont we like need to append the row to the array something like $row[i] = $r because wont we be replacing all the data in the array by doing $rows[] = $r; – Escalator

This is incorrect code. You must first get a query result, then use the result to fetch the rows. – Reactionary

This should helps you. $result = $connection->query($sql); $nom = 1; $rows[]; while($row = $result->fetch_assoc()) { $rows[$nom] = $row; } print json_encode($rows); – Gimbals

Answer 2

If you need to put selected rows in a distinct element of returned json, you can do it like this: first, get the $rows array like in the accepted answer and then put it in another array like this

print json_encode(['object_name' => $rows]);

Horse

+1 This seems to be the only answer that provides JSON in the same format as the examples at json.org/example . – Binoculars

Yes, this example gives a key per row. – Encrinite

Answer 3

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

http://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.

Ops

mysql_query does not return a result set. that's what mysql_fetch* is for. – Doloritas

Um... yeah... that's what goes in the iterating, between mysql_query and json_encode. Good call, Watson. – Ops

Answer 4

When using PDO

Use fetchAll() to fetch all rows as an associative array.

$stmt = $pdo->query('SELECT * FROM article');
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo json_encode($rows);

When your SQL has parameters:

$stmt = $pdo->prepare('SELECT * FROM article WHERE id=?');
$stmt->execute([1]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo json_encode($rows);

When you need to rekey the table you can use foreach loop and build the array manually.

$stmt = $pdo->prepare('SELECT * FROM article WHERE id=?');
$stmt->execute([1]);

$rows = [];
foreach ($stmt as $row) {
    $rows[] = [
        'newID' => $row['id'],
        'Description' => $row['text'],
    ];
}

echo json_encode($rows);

When using mysqli

Use fetch_all() to fetch all rows as an associative array.

$res = $mysqli->query('SELECT * FROM article');
$rows = $res->fetch_all(MYSQLI_ASSOC);
echo json_encode($rows);

When your SQL has parameters you need to perform prepare/bind/execute/get_result.

$id = 1;
$stmt = $mysqli->prepare('SELECT * FROM article WHERE id=?');
$stmt->bind_param('s', $id); // binding by reference. Only use variables, not literals
$stmt->execute();
$res = $stmt->get_result(); // returns mysqli_result same as mysqli::query()
$rows = $res->fetch_all(MYSQLI_ASSOC);
echo json_encode($rows);

When you need to rekey the table you can use foreach loop and build the array manually.

$stmt = $mysqli->prepare('SELECT * FROM article WHERE id=?');
$stmt->bind_param('s', $id);
$stmt->execute();
$res = $stmt->get_result();

$rows = [];
foreach ($res as $row) {
    $rows[] = [
        'newID' => $row['id'],
        'Description' => $row['text'],
    ];
}

echo json_encode($rows);

When using mysql_* API

Please, upgrade as soon as possible to a supported PHP version! Please take it seriously. If you need a solution using the old API, this is how it could be done:

$res = mysql_query("SELECT * FROM article");

$rows = [];
while ($row = mysql_fetch_assoc($res)) {
    $rows[] = $row;
}

echo json_encode($rows);

Kostival

I would have thought the examples of re-keying results would be better with column aliases as this removes the need for the loops. – Outride

Answer 5

if ($result->num_rows > 0) {
    # code...
    $arr = [];
    $inc = 0;
    while ($row = $result->fetch_assoc()) {
        # code...
        $jsonArrayObject = (array('lat' => $row["lat"], 'lon' => $row["lon"], 'addr' => $row["address"]));
        $arr[$inc] = $jsonArrayObject;
        $inc++;
    }
    $json_array = json_encode($arr);
    echo $json_array;
} else {
    echo "0 results";
}

Regatta

Answer 6

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!

Ineffaceable

this confused code will always return only one row – Edging

Answer 7

The code below works fine here!

<?php

$con = mysqli_connect("localhost",$username,$password,"databaseName");

$query = "the query here";
$result = mysqli_query($con,$query);

$rows = array();
while($r = mysqli_fetch_array($result)) {
    $rows[] = $r;
}
echo json_encode($rows);

mysqli_close($con);

Diley

Answer 8

<?php

define('HOST', 'localhost');
define('USER', 'root');
define('PASS', '');
define('DB', 'dishant');

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$con = mysqli_connect(HOST, USER, PASS, DB);

$sql = "select * from demo ";
$sth = mysqli_query($con, $sql);
$rows = array();
while ($r = mysqli_fetch_array($sth, MYSQLI_ASSOC)) {
    $row_array['id'] = $r;

    array_push($rows, $row_array);
}
echo json_encode($rows);

array_push($rows,$row_array); helps to build an array otherwise it gives the last value in the while loop.

This works like append method of StringBuilder in Java.

Nisa

Answer 9

My simple fix to stop it putting speech marks around numeric values...

while($r = mysql_fetch_assoc($rs)){
    while($elm=each($r))
    {
        if(is_numeric($r[$elm["key"]])){
                    $r[$elm["key"]]=intval($r[$elm["key"]]);
        }
    }
    $rows[] = $r;
}   

Waterrepellent

user JSON_NUMERIC_CHECK flag instead json_encode(getHistory($query), JSON_NUMERIC_CHECK ); – Qp

Answer 10

Sorry, this is extremely long after the question, but:

$sql = 'SELECT CONCAT("[", GROUP_CONCAT(CONCAT("{username:'",username,"'"), CONCAT(",email:'",email),"'}")), "]") 
AS json 
FROM users;'
$msl = mysql_query($sql)
print($msl["json"]);

Just basically:

"SELECT" Select the rows    
"CONCAT" Returns the string that results from concatenating (joining) all the arguments
"GROUP_CONCAT" Returns a string with concatenated non-NULL value from a group

Creamer

Beware that GROUP_CONCAT() is limited by group_concat_max_len. – Ghana

I would not recommend that anyone employ this hacky technique. – Sowens

Answer 11

One more option using FOR loop:

 $sth = mysql_query("SELECT ...");
 for($rows = array(); $row = mysql_fetch_assoc($sth); $rows[] = $row);
 print json_encode($rows);

The only disadvantage is that loop for is slower then e.g. while or especially foreach

Fanchet

Answer 12

For example $result = mysql_query("SELECT * FROM userprofiles where NAME='TESTUSER' ");

1.) if $result is only one row.

$response = mysql_fetch_array($result);
echo json_encode($response);

2.) if $result is more than one row. You need to iterate the rows and save it to an array and return a json with array in it.

$rows = array();
if (mysql_num_rows($result) > 0) {
    while($r = mysql_fetch_assoc($result)) {
       $id = $r["USERID"];   //a column name (ex.ID) used to get a value of the single row at at time
       $rows[$id] = $r; //save the fetched row and add it to the array.
    }
}    
echo json_encode($rows);

Attribute

Answer 13

I solved like this

$stmt->bind_result($cde,$v_off,$em_nm,$q_id,$v_m);
    $list=array();
    $i=0;
    while ($cresult=$stmt->fetch()){    


        $list[$i][0]=$cde;
        $list[$i][1]=$v_off;
        $list[$i][2]=$em_nm;
        $list[$i][3]=$q_id;
        $list[$i][4]=$v_m;
        $i=$i+1;
    }
    echo json_encode($list);        

This will be returned to ajax as result set and by using json parse in javascript part like this :

obj = JSON.parse(dataX);

Surbeck

Answer 14

We shouldn't see any use of mysql_ functions in modern applications, so either use mysqli_ or pdo functions.

Explicitly calling header("Content-type:application/json"); before outputting your data payload is considered to be best practice by some devs. This is usually not a requirement, but clarifies the format of the payload to whatever might be receiving it.

Assuming this is the only data being printed, it is safe to print the json string using exit() which will terminate the execution of the script as well. This, again, is not essential because echo will work just as well, but some devs consider it a good practice to explicitly terminate the script.

---

MySQLi single-row result set from query result set object:

exit(json_encode($result->fetch_assoc()));  // 1-dimensional / flat

MySQLi multi-row result set from query result set object:

Prior to PHP 8.1.0, available only with mysqlnd.

exit(json_encode($result->fetch_all(MYSQLI_ASSOC)));  // 2-dimensional / array of rows

MySQLi single-row result set from prepared statement:

$result = $stmt->get_result();
exit(json_encode($result->fetch_assoc()));  // 1-dimensional / flat

MySQLi multi-row result set from prepared statement:

$result = $stmt->get_result();
exit(json_encode($result->fetch_all(MYSQLI_ASSOC)));  // 2-dimensional / array of rows

PDO single-row result set from query result set object:

exit(json_encode($result->fetch(PDO::FETCH_ASSOC)));  // 1-dimensional / flat

PDO multi-row result set from query result set object:

exit(json_encode($result->fetchAll(PDO::FETCH_ASSOC)));  // 2-dimensional / array of rows

PDO single-row result set from prepared statement:

exit(json_encode($stmt->fetch(PDO::FETCH_ASSOC)));  // 1-dimensional / flat

PDO multi-row result set from prepared statement:

exit(json_encode($stmt->fetchAll(PDO::FETCH_ASSOC)));  // 2-dimensional / array of rows

---

Obey these rules to prevent the possibility of generating invalid json.:

1. you should only call json_encode() after you are completely finished manipulating your result array and
2. you should always use json_encode() to encode the payload (avoid the urge to manually craft a json string using other string functions or concatenation).

---

If you need to iterate your result set data to run php functions or provide functionality that your database language doesn't offer, then you can immediately iterate the result set object with foreach() and access values using array syntax -- e.g.

$response = [];
foreach ($result as $row) {
    $row['col1'] = someFunction($row['id']);
    $response[] = $row;
}
exit(json_encode($response));

---

If you are calling json_encode() on your data payload, then it won't make any difference to whether the payload is an array of arrays or an array of objects. The json string that is created will have identical syntax.

---

You do not need to explicitly close the database connection after you are finished with the connection. When your script terminates, the connection will be closed for you automatically.

Sowens

Answer 15

we could simplify Paolo Bergantino answer like this

$sth = mysql_query("SELECT ...");
print json_encode(mysql_fetch_assoc($sth));

Chromatolysis

I don't think you can. – Sowens