Chunking MySQL array with json encode

Question

I know there are existing some Questions about Chunking a mysql array in php, but my problem is, that I want to keep the output in JSON.

Scenario: I want to get data from mysql, do some stuff with it ( like time formatting ) and output it in JSON. The JSON data is parsed in the browser and visualized over a javascript chart.

Problem: All of the above is working, but because of the huge amount of data, I'm getting an out of memory error, when I select bigger date ranges to output.

The Idea of directly sending out each x-lines of data is not working because of the JSON format it needs to be. Several JSON chunks won't work, it needs to be one for the chart.

So in the end I need to chunk the data but keep it as one big JSON. (And setting up the memory limit is not really a solution.)

Ideas: One Idea would be, to let the browser chunk the date range and ask the data as chunks & then put them together. Of course this would work, but if there is a way to do this server side, it would be better.

Code:

private function getDB($date1, $date2){
    $query = 'SELECT * FROM `db1`.`'.$table.'` WHERE `date` BETWEEN "'.$date1.'" AND "'.$date2.'" order by `date`;';
    // date = datetime !
    $result = $this->db->query($query);

    $array = array();
    while ( $row = $result->fetch_assoc () ) {
        $array[] = array( strtotime($row[ 'date' ])*1000 , (float)$row[ 'var' ] );
        // the formatting needs to be done, so the chart accepts it..
    }

    $result->close();

    return json_encode($array);
}

Answer 1

Since this is not an option,

ini_set("memory_limit","32M")

perhaps you can add LIMIT to the function paramaters and query:

private function getDB($date1, $date2, $start, $pageSize){
    $query = 'SELECT * FROM `db1`.`'.$table.'` WHERE `date` BETWEEN "'.$date1.'" AND "'.$date2.'" order by `date` LIMIT $start, $pageSize;';
    // date = datetime !
    $result = $this->db->query($query);

    $array = array();
    while ( $row = $result->fetch_assoc () ) {
        $array[] = array( strtotime($row[ 'date' ])*1000 , (float)$row[ 'var' ] );
        // the formatting needs to be done, so the chart accepts it..
    }

    $result->close();

    return json_encode($array);
}

Then setup a for loop in javascript, call this with Ajax, incrementing the $start variable each time.

Store each responseText.substr(1).substr(-1) in an array.

When the responseText is "", all of the records have been returned.

.join the array with a comma, then add a new opening and closing "{ }", and you should have a JSON equivalent to all records.

Minimal parsing, and you'll be using built-in functions for most of it.

var startRec=0;
var pageSize=50;
var xmlhttp=new XMLHttpRequest();
var aryJSON=[];
var JSON;
xmlhttp.onreadystatechange=function(){
    if (xmlhttp.readyState==4 && xmlhttp.status==200){
        if(xmlhttp.responseText==""){ //Might need to check for "{}" here instead of ""
            //All records are received
            JSON="{" + aryJSON.join(",") + "}";
            aryJSON=[];
            startRec=0
        }else{
            aryJSON.push(xmlhttp.responseText.substr(1).substr(-1));
            startRec+=pageSize;
            getNextPage();
        }
    }
}
function getNextPage(){
    xmlhttp.open("GET","your.php?start=" + startRec + "&pageSize=" + pageSize,true);
    xmlhttp.send();
}

Answer 2

I would recommend that you have the server send the browser exactly what it needs to create the table. Parsing can be a heavy task, so why have the client do that lifting?

I would have your backend send the browser some kind of data structure that represents the table (i.e. list of lists), with all the formatting already done. Rendering the table should be faster and less memory-intensive.

Answer 3

One way of answer would be, to do the chunking on the server, by giving out the JSON, removing the leading [ & ].

@apache_setenv('no-gzip', 1);
@ini_set('zlib.output_compression', 0);
@ini_set('implicit_flush', 1);

$array = array();
echo '[';
$started = false;
while ( $row = $result->fetch_assoc () ) {
    $array[] = [ strtotime($row[ 'datetime' ])*1000 , (float)$row[ 'var' ] ];
    if(sizeof($array) == 1000){
        if($started){
            echo ',';
        }else{
            $started = true;
        }
        echo substr(substr(json_encode($array),1), 0, -1);
        // converting  [[datetime1, value1],[datetime2, value2]]
        //          to  [datetime1, value1],[datetime2, value2]
        ob_flush();
        $array = array();
    }
}
if($started)echo ',';
$this->flushJSON($array);
echo ']';
flush();

$result->close();

This is working and reducing the ram usage to 40%. Still it seems that Apache is buffering something, so the ram usage increases over the time, the script is running. (Yeah, the flush is working, I debugged that, that's not the problem.)

But because of the remaining increase, the fastest way to achieve a clean chunking is to do this like alfadog67 pointed it out.

Also, to mention it, I had to disable the output compression, otherwise apache wouldn't flush it directly..