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

we could simplify Paolo Bergantino answer like this

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

Answer 2

$sth = mysqli_query("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.

Answer 3

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

Answer 4

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

$con = mysqli_connect(HOST,USER,PASS,DB);


  if (mysqli_connect_errno())
  {
   echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

 $sql = "select * from demo ";

 $sth = mysqli_query($con,$sql);

$rows = array();

while($r = mysqli_fetch_array($sth,MYSQL_ASSOC)) {

 $row_array['id'] = $r;

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

mysqli_close($con);
?>

aarray_push($rows,$row_array); help to build array otherwise it give last value in the while loop

this work like append method of StringBuilder in java

Answer 5

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

Answer 6

Check the code below for using mysql_fetch and json_encode. You will need to iterate through the rows but if you use mysqli the situation will change

$kt_query="SELECT * FROM tbl_xxx";
$kt_result = mysql_query($kt_query) or die('Query failed: ' . mysql_error());
$rows= array();
while($sonuc=mysql_fetch_assoc($kt_result))
{
    $rows[]=$sonuc;
}
print json_encode($rows);