JSON encode MySQL results

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?

Answers


$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.


Try this, this will create your object properly

 $result = mysql_query("SELECT ...");
 $rows = array();
   while($r = mysql_fetch_assoc($result)) {
     $rows['object_name'][] = $r;
   }

 print json_encode($rows);

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.


Thanks this helped me a lot. My code:

$sqldata = mysql_query("SELECT * FROM `$table`");

$rows = array();
while($r = mysql_fetch_assoc($sqldata)) {
  $rows[] = $r;
}

echo json_encode($rows);

Thanks.. my answer goes:

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";
        }

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


The code below works fine here!

<?php

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

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

  $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);
?>

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;
}   

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

we could simplify Paolo Bergantino answer like this

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

How to create JSON using data from MySQL database

JSON (JavaScript Object Notation) is more preferred nowadays over XML as it’s lightweight, readable and easily manageable for exchanging data across various platforms. we’ll see how JSON Data can be created from Employee table stored in MySQL database.

 echo json_encode($data);

Live : [Example ]


<?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


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


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);

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);		

I have the same requirement. I just want to print a result object into JSON format so I use the code below. I hope you find something in it.

// Code of Conversion
$query = "SELECT * FROM products;";
$result = mysqli_query($conn , $query);

if ($result) {
echo "</br>"."Results Found";

// Conversion of result object into JSON format
$rows = array();
while($temp = mysqli_fetch_assoc($result)) {
    $rows[] = $temp;
}
echo "</br>" . json_encode($rows);

} else {
    echo "No Results Found";
}

$array = array();
$subArray=array();
$sql_results = mysql_query('SELECT * FROM `location`');

while($row = mysql_fetch_array($sql_results))
{
    $subArray[location_id]=$row['location'];  //location_id is key and $row['location'] is value which come fron database.
    $subArray[x]=$row['x'];
    $subArray[y]=$row['y'];


 $array[] =  $subArray ;
}
echo'{"ProductsData":'.json_encode($array).'}';

Code:

$rows = array();

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

 $row_array['result'] = $r;

  array_push($rows,$row_array); // here we push every iteration to an array otherwise you will get only last iteration value
}

echo json_encode($rows);

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);

$rows = json_decode($mysql_result,true);

as simple as that :-)


$sql = "SELECT JSON_ARRAYAGG(JSON_OBJECT('id', tbl.id)) FROM table tbl WHERE... "; 

//And get first row :) 



Need Your Help

What is the difference between a language construct and a "built-in" function in PHP?

php function built-in language-construct

I know that include, isset, require, print, echo, and some others are not functions but language constructs.

What process is listening on a certain port on Solaris?

unix solaris

So I log into a Solaris box, try to start Apache, and find that there is already a process listening on port 80, and it's not Apache. Our boxes don't have lsof installed, so I can't query with that...