Lee Armstrong Lee Armstrong - 4 months ago 14
JSON Question

Getting data from MYSQL into JSON using PHP

I have the following quite simple test PHP 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 size 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);


?>

Answer

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.