up vote 0 down vote favorite
4

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


    ?>
share|improve this question

4 Answers

up vote 3 down vote accepted

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.

share|improve this answer
I like this! Very nice! – Lee Armstrong Jan 27 '11 at 12:55
@Lee: Unconventional, but that way you don't have to store every row into an array for later encoding. – Andrew Moore Jan 27 '11 at 13:03
Yeah, a nice approach indeed, it runs quicker too actually! – Lee Armstrong Jan 27 '11 at 13:08
1  
Is there anyway to get it to put the row header on each one? – Lee Armstrong Jan 27 '11 at 13:49
1  
@Lee: Sure! Replace mysql_fetch_row($result) with mysql_fetch_array($result, MYSQL_ASSOC) in your while loop. – Andrew Moore Jan 27 '11 at 18:26
show 2 more comments
up vote 5 down vote

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

share|improve this answer
Pffff, was about to write exactly the same... Nice one Mark. – acm Jan 27 '11 at 12:31
yes mee too :d +1 for that :D – Eddsstudio Jan 27 '11 at 12:32
Yes good shout! Didn't realise i was duplicating it there at all! That has worked with 16 bytes over, setting to 64M now it fits :-) – Lee Armstrong Jan 27 '11 at 12:36
up vote 5 down vote

Use this one:

$result = mysql_query("SELECT * FROM listinfo");

$json = array();
$total_records = mysql_num_rows($result);

if($total_records >= 1){
  while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
    $json[] = $row;
  }
}

echo json_encode($json);
share|improve this answer
1  
Why are you using if($total_records >= 1){ and $total_records = mysql_num_rows($result);? – neworld Nov 29 '12 at 13:18
some explanation would be nice – Lothar_K Nov 29 '12 at 13:20
up vote 0 down vote

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

share|improve this answer

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.