How to Convert Data from MySQL to JSON using PHP + big database

Question

I am building on from this code here where how to Convert Data from MySQL to JSON using PHP is shown. I have got this to work for a subset of my database(39,000 rows) but not the whole database(it basically just hangs getting 500 Internal Server Error in the Net tab in firebug and nothing is written in the files). The code basically writes the DB to 2 files to a json file and a js file with var = <json array>

here is a snipet of what works:

//$sql = "select * from table1; //does not work
//$sql = "select * from table1 LIMIT 0,10"; //for test
$sql = "select * from table1 LIMIT 0,39000"; //works 39000ish is the max

Is there any way I can alter my code to handle this bigger DB of 300,000+ rows and counting?

Here is my complete code:

  <?php
    /* Database connection start */
    $servername = "localhost";
    $username = "root";
    $password = "Password1";
    $dbname = "test";

        //open connection to mysql db
        $connection = mysqli_connect($servername, $username, $password, $dbname) or die("Error " . mysqli_error($connection));

        //fetch table rows from mysql db
        //$sql = "select * from table1; //does not work
        //$sql = "select * from table1 LIMIT 0,10"; //for test
        $sql = "select * from table1 LIMIT 0,39000"; //works 39000ish is the max
        $result = mysqli_query($connection, $sql) or die("Error in Selecting " . mysqli_error($connection));

        //create an array
        $emparray2 = array();
        while($row =mysqli_fetch_assoc($result))
        {
            $emparray2[] = $row;
        }
        echo json_encode($emparray2);

        //close the db connection
        mysqli_close($connection);

        //write to json file
        $fp = fopen('empdata2.json', 'w');
        fwrite($fp, json_encode($emparray2));
        fclose($fp);

        //write to js file with var data = [ <ARRAY>]
        $fp = fopen('data2.js', 'w');
        fwrite($fp, "var data2 =");
        fwrite($fp, json_encode($emparray2, JSON_PRETTY_PRINT));//makes it pretty
        //fwrite($fp, json_encode($emparray2));
        fclose($fp);

    ?>

possible similar questions here:

1 here 2 here 3 here 4 here 5 here

Edit1

For my reference this is that I have to change.

root@a4b8b0a15197:/# cat /etc/php5/apache2/php.ini | grep -n memory_limit
406:memory_limit = 128M
root@a4b8b0a15197:/#

I often ask this but what does "does not work" actually mean? :) — Lifz, Jul 04 '16 at 03:31
Do you use whole 300,000+ data? I can't believe this. I suggest you data pagination. — Andy Ryu, Jul 04 '16 at 03:37
When working with huge amount of data we must Usually fetch data row by row from database and write to file each row simultaneously. — Mostafa Vatanpour, Jul 04 '16 at 03:42
@Lifz it basically just hangs getting 500 Internal Server Error in the Net tab in firebug and nothing is written in the files — HattrickNZ, Jul 04 '16 at 03:50
@Terminus I want to load the js file into crossfilter [here](http://animateddata.co.uk/articles/crossfilter/) and [here](https://becomingadatascientist.wordpress.com/tag/crossfilter-js/) — HattrickNZ, Jul 04 '16 at 03:50
@AndyRyu I do use pagination when I use [datatables](https://datatables.net/examples/basic_init/alt_pagination.html), but in this instance I want all the data — HattrickNZ, Jul 04 '16 at 03:51
Do you have access to the server? You may need to increase some settings in php.ini to allow for a longer processing time. — Lifz, Jul 04 '16 at 03:54
@Lifz yes I have access to the server, let me know and I'll follow it up — HattrickNZ, Jul 04 '16 at 03:58

score 1 · Accepted Answer · edited May 23 '17 at 10:33

1

You can use this function.

<?php
set_time_limit ( 0 ) // no limit execution time.

/* Database connection start */
$servername = "localhost";
$username = "root";
$password = "Password1";
$dbname = "test";

And, you have to increase memory limit like THIS

edited May 23 '17 at 10:33

Community

1
1

answered Jul 04 '16 at 04:14

Andy Ryu

79
2

Is memory_limit the max size of the file to be written or the size of the data from the db? tks – HattrickNZ Jul 04 '16 at 05:08
@HattrickNZ No. Please be generous enough to hold the size. First, I recommend that you start from 200 megabytes. – Andy Ryu Jul 04 '16 at 05:31

How to Convert Data from MySQL to JSON using PHP + big database

1 Answers1