Not able to export more than ~50K records

[natasha_sharma]

Hi,

 

I am using below code, no matter what TO and FROM date i select, i am not able to export more than ~50K records to CSV from MYSQL db. 

 

The code works but limited the records i can export. 50K is not a hard cut it keeps on varying.

 

I am using the XAMPP, mysql version 5.6 (MariaDB).

 

Whats wrong?

                       <?php

                            function getbetween($string, $from, $to) {


                                $content = explode($from, $string);

                                //print_r($content);
                                $seccontent = explode($to, $content[1]);


                                return $seccontent[0];
                            }



                            if (isset($_POST['submit'])) {

    $from = $_POST['from'];
    $to = $_POST['to'];
    $query = "select ticker, date_dt, open, high, low, close, wap, os_shares, ttq, total_trades, del_qty, sales, profit, op_assets from `stock_history` where date_t<='$to' and date_t>='$from'";
    $rows[] = array("ticker", "date_dt", "open", "high", "low", "close", "wap", "os_shares", "ttq", "total_trades", "del_qty", "sales", "profit", "op_assets");

    $sql = mysql_query($query);
    if (mysql_num_rows($sql) > 0) {
        while ($list = mysql_fetch_assoc($sql)) {
            $rows[] = $list;
        }
    }
    if ($rows != "") {

        $file = fopen("stock_history.csv", "w");

        foreach ($rows as $row) {
            fputcsv($file, $row);
        }

        fclose($file);
        echo '<a href="stock_history.csv" download>download</a>';
    }

[taquitosensei]

 

Hi,

 

I am using below code, no matter what TO and FROM date i select, i am not able to export more than ~50K records to CSV from MYSQL db. 

 

The code works but limited the records i can export. 50K is not a hard cut it keeps on varying.

 

I am using the XAMPP, mysql version 5.6 (MariaDB).

 

Whats wrong?

                       <?php

                            function getbetween($string, $from, $to) {


                                $content = explode($from, $string);

                                //print_r($content);
                                $seccontent = explode($to, $content[1]);


                                return $seccontent[0];
                            }



                            if (isset($_POST['submit'])) {

    $from = $_POST['from'];
    $to = $_POST['to'];
    $query = "select ticker, date_dt, open, high, low, close, wap, os_shares, ttq, total_trades, del_qty, sales, profit, op_assets from `stock_history` where date_t<='$to' and date_t>='$from'";
    $rows[] = array("ticker", "date_dt", "open", "high", "low", "close", "wap", "os_shares", "ttq", "total_trades", "del_qty", "sales", "profit", "op_assets");

    $sql = mysql_query($query);
    if (mysql_num_rows($sql) > 0) {
        while ($list = mysql_fetch_assoc($sql)) {
            $rows[] = $list;
        }
    }
    if ($rows != "") {

        $file = fopen("stock_history.csv", "w");

        foreach ($rows as $row) {
            fputcsv($file, $row);
        }

        fclose($file);
        echo '<a href="stock_history.csv" download>download</a>';
    }

you might try append "a" instead of "w" so you're not writing to the beginning of the file every time. Not sure if its going through section more than once. The end of the function appears to be missing here. 

[QuickOldCar]

Could be php script is timing out.

 

Can set the timeout limit in php.ini:

max_execution_time = 600

 

.htaccess:

php_value max_execution_time 600

 

top of the script:

ini_set('max_execution_time',600);//in seconds,10 minutes

 

Can set to 0 for unlimited if want to see if completes or a special circumstance you want to ensure continues. I suggest not setting to 0 entire server.

 

This doesn't make much sense....

You don't create an array but then add to it in the loop with [], The $rows array will always have data you hard coded.

Lower you are checking for a variable is not blank.

 

 

Try this.

<?php
ini_set('max_execution_time',600); //in seconds

function getbetween($string, $from, $to)
{
    
    
    $content = explode($from, $string);
    
    //print_r($content);
    $seccontent = explode($to, $content[1]);
    
    
    return $seccontent[0];
}



if (isset($_POST['submit'])) {
    
    $rows = array(); //define empty array
    
    $from  = $_POST['from'];
    $to    = $_POST['to'];
    $query = "select ticker, date_dt, open, high, low, close, wap, os_shares, ttq, total_trades, del_qty, sales, profit, op_assets from `stock_history` where date_t<='$to' and date_t>='$from'";
    
    $sql = mysql_query($query);
    if (mysql_num_rows($sql) > 0) {
        
        //first array record created only if data returned from query
        $rows = array(
            "ticker",
            "date_dt",
            "open",
            "high",
            "low",
            "close",
            "wap",
            "os_shares",
            "ttq",
            "total_trades",
            "del_qty",
            "sales",
            "profit",
            "op_assets"
        );
        
        while ($list = mysql_fetch_assoc($sql)) {
            $rows[] = $list; //add additional records to array
        }
    }
    if (!empty($rows)) { //check not empty array, now should only do if is actual data from query
        
        $file = fopen("stock_history.csv", "w");
        
        foreach ($rows as $row) {
            fputcsv($file, $row);
        }
        
        fclose($file);
        echo '<a href="stock_history.csv" download>download</a>';
    }
}
?>  

[QuickOldCar]

Furthermore with your script mysql_* functions are deprecated, suggest using pdo with prepared statements.

Using unescaped values directly into a query could lead you to an sql attack.

 

Check if post names are actually set and not merely the form was submitted. Those post values are not blank, data type you expect before using them.

 

EDIT:

At a minimum use the mysql_real_escape_string() function

$from = mysql_real_escape_string($_POST['from']);
$to = mysql_real_escape_string($_POST['to']);

Edited March 16, 2016 by QuickOldCar

[Barand]

The intermediate array is unnecessary.

$head = array("ticker", "date_dt", "open", "high", "low", "close", "wap", "os_shares", "ttq", "total_trades", "del_qty", "sales", "profit", "op_assets");


$sql = mysql_query($query);
if (mysql_num_rows($sql) > 0) {
    $file = fopen("stock_history.csv", "w");
    fputcsv($file, $head);                      // write header 
    while ($list = mysql_fetch_row($sql)) {
        fputcsv($file, $list);                  // write data
    }
    fclose($file);
}