CODEWITHSUNDEEP
phpmysqlcsv
DJack
DJack

Reputation: 639

PHP: Download MySQL table

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;

Upvotes: 3

Views: 3413

Answers (3)

DJack
DJack

Reputation: 639

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

Upvotes: 4

Ravi Hirani
Ravi Hirani

Reputation: 6539

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

Upvotes: 0

Slavic
Slavic

Reputation: 1952

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.

Upvotes: 0

Related Questions