Getting data from MYSQL into JSON using PHP

I have the following quite simple test PHP code开发者_JAVA技巧 that extracts the data and puts it into JSON formatted text.

I get the following error..

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

Where line 33 is the json_encode() line.

Is there a way to make this more efficient? The PHP.ini is already set to 32M as max, hence sized up from the 8M standard!

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


    ?>

You are probably encoding a very large dataset. You could encode each row, one row at a time instead of encoding 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);

That way, each call to json_encode() only encodes a small array instead of a large one. The end result is the same. This is IMO the solution which will use the less memory.

Stop duplicating your array of data

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

that will help reduce your memory usage

Use this one:

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

As a first work-around set it to something like 256M or even 512M.

It is probable the dataset that MySQL is returning to you is quite big. So even if your PHP is very memory-efficient, you still will get the OoM error. So as a more viable long-term solution use the LIMIT statement (SELECT * FROM $table WHERE 1=1 LIMIT 0,30 (start from index 0, get 30 items).

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

Here is my first json that works perfectly

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