PHP: Download MySQL table

Question

I want to download a MySQL table as CSV file but I am getting this error "Allowed memory size of 1342177280 bytes exhausted". I guess that is because the file is first created and then downloaded. How can I achive that the user can start downloading the file from 0, in this case I think that I wouldn't need so much memory.

This is my code so far:

$output         = "";
$table          = "export_table";
$sql            = mysql_query("select * from $table");
$columns_total  = mysql_num_fields($sql);

// Get The Field Name
for ($i = 0; $i < $columns_total; $i++) {
    $heading    =   mysql_field_name($sql, $i);
    $output     .= '"'.$heading.'",';
}
$output .="\n";
// Get Records from the table
while ($row = mysql_fetch_array($sql)) {
for ($i = 0; $i < $columns_total; $i++) {
$output .='"'.$row["$i"].'",';
}
$output .="\n";
}
// Download the file
$filename =  "export.csv";
header('Content-type: application/csv');
header('Content-Disposition: attachment; filename='.$filename);
echo $output;

the ugly solution to your code is ini_set('memory_limit',-1); it will not give this error. — Amit Shah
– Amit Shah, Commented Feb 5, 2016 at 9:46
Search MYSQL SELECT INTO OUTFILE on SO and send me a drink, because you will really be that happy after using it compared to your own method :) — Hanky Panky
– Hanky Panky, Commented Feb 5, 2016 at 9:46
the another solution is you write entire file on server and allow use to down it. — Amit Shah
– Amit Shah, Commented Feb 5, 2016 at 9:47
First of all, you have to transform «I guess» into «I known». Depending on how the error is generated, you can apply one of the suggestions. The solution from @Hanky웃Panky probably will resolve your problem in any case, but is important to known how our code fails. — fusion3k
– fusion3k, Commented Feb 5, 2016 at 10:17

DJack · Accepted Answer · 2016-02-10 08:10:18Z

4

So at the end this is what did what I wanted:

$db = mysqli_connect("localhost", "user", "password", "database");

$result = mysqli_query($db, "SELECT * FROM export_table", MYSQLI_USE_RESULT);

header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header("Content-Disposition: attachment;filename=\"export_table.csv\"");
header("Content-Transfer-Encoding: binary");
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: max-age=0, no-cache, must-revalidate, proxy-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private",false);

$output = fopen('php://output', 'w');

    fputcsv($output, array('ID','Column1','Column2','Column3'));

while ($row = mysqli_fetch_assoc($result))
    {
    fputcsv($output, $row);
    }

fclose($output);
mysqli_free_result($result);
mysqli_close($db);

answered Feb 10, 2016 at 8:10

DJack

6391 gold badge8 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nan Zhou Over a year ago

Another example can also do this. code.stephenmorley.org/php/creating-downloadable-csv-files , for people who need to solve this problem.

Slavic · Accepted Answer · 2016-02-05 09:51:48Z

0

You would have to move your header functions before traversing the result set and, instead of assigning to $output variable, just echo it.

// ...

$filename =  "export.csv";
header('Content-type: application/csv');
header('Content-Disposition: attachment; filename='.$filename);

// Just dump the records you fetch from the db into output
while ($row = mysql_fetch_array($sql)) {
    $line = '';
    for ($i = 0; $i < $columns_total; $i++) {
        $line .= '"' . $row["$i"] . '",';
    }
    echo $line, "\n";
}

This might have its problems, depending on your specifics, however should work as a quick and dirty solution.

answered Feb 5, 2016 at 9:51

Slavic

1,9822 gold badges17 silver badges31 bronze badges

Comments

Ravi Hirani · Accepted Answer · 2016-02-05 10:08:07Z

0

Use this code:-

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'dbName';
$tableName = 'tableName';
$mysqldump=exec('which mysqldump');   

$command = "$mysqldump --opt -h $dbhost -u $dbuser -p $dbpass $dbname $tableName > $tableName.sql";

exec($command);

If you need to download whole database then replace $command by this line:-

$command = "$mysqldump --opt -h $dbhost -u $dbuser -p $dbpass $dbname  > $dbName.sql";

Hope this will help you :)

answered Feb 5, 2016 at 10:08

Ravi Hirani

6,5341 gold badge31 silver badges43 bronze badges

Collectives™ on Stack Overflow

PHP: Download MySQL table

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related