Getting data from MYSQL to JSON using PHP

Question

I have the following pretty simple test PHP code that retrieves data and puts it in JSON formatted text.

I get the following error.

Fatal error: The valid memory size of 33554432 bytes has been exhausted (tried to allocate 1979603 bytes) in /var/www/test.php on line 33

If line 33 is json_encode() .

Is there a way to make this more efficient? PHP.ini already installed as 32M as max, so the size is from the standard 8M!

  <?php require('../../admin/db_login.php'); $db=mysql_connect($host, $username, $password) or die('Could not connect'); mysql_select_db($db_name, $db) or die(''); $result = mysql_query("SELECT * from listinfo") or die('Could not query'); $json = array(); if(mysql_num_rows($result)){ $row=mysql_fetch_assoc($result); while($row=mysql_fetch_row($result)){ // cast results to specific data types $test_data[]=$row; } $json['testData']=$test_data; } mysql_close($db); echo json_encode($json); ?> 

Answer 1

You are probably encoding a very large data set. You can encode each line, one line at a time, rather than encode it in one big operation.

 <?php require('../../admin/db_login.php'); $db=mysql_connect($host, $username, $password) or die('Could not connect'); mysql_select_db($db_name, $db) or die(''); $result = mysql_query("SELECT * from listinfo") or die('Could not query'); if(mysql_num_rows($result)){ echo '{"testData":['; $first = true; $row=mysql_fetch_assoc($result); while($row=mysql_fetch_row($result)){ // cast results to specific data types if($first) { $first = false; } else { echo ','; } echo json_encode($row); } echo ']}'; } else { echo '[]'; } mysql_close($db); 

Thus, each json_encode() call encodes only a small array, not a large one. The end result is the same. This is an IMO solution that will use less memory.

Answer 2

Stop Duplication of a Dataset

 $json = array(); if(mysql_num_rows($result)){ $row=mysql_fetch_assoc($result); while($row=mysql_fetch_row($result)){ // cast results to specific data types $json['testData'][]=$row; } } 

which will help reduce your memory usage

Answer 3

Use this:

 $result = mysql_query("SELECT * FROM listinfo"); $json = array(); $total_records = mysql_num_rows($result); if($total_records > 0){ while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){ $json[] = $row; } } echo json_encode($json); 

Answer 4

As the first working order, set it to approximately 256 MB or even 512 M.

Probably the dataset that MySQL returns to you is quite large. Therefore, even if your PHP is very memory efficient, you still get an OoM error. Since a more viable long-term solution uses the LIMIT operator ( SELECT * FROM $table WHERE 1=1 LIMIT 0,30 (starting at index 0, we get 30 elements).

EDIT: Oh wow, I didn't even see the problem from the first solution ... Well, it might still be a good idea to LIMIT your request LIMIT

Answer 5

Here is my first json that works great

 <?php // connect to mysql server mysql_connect($host, $username, $password) or die('Could not connect'); // select the db name mysql_select_db($dbname); // enter your sql query $sql = "Select * from Order_Details"; // Creates temp array variable $temp = array(); // Gets table details $result = mysql_query($sql); // Adds each records/row to $temp while($row=mysql_fetch_row($result)) { $temp[] = $row; } // Formats json from temp and shows/print on page echo json_encode($temp); ?>