CODEWITHSUNDEEP
phpmysql
Marc
Marc

Reputation: 577

Save the results of a query to CSV and create a file on the server

First time I've had to do this, but I need to save the results of a query to CSV and create a file on the server, prior to it being passed via SFTP to a remote server. I can successfully create the CSV which is downloaded in the browser, per the code below, but cant seem to get it to save a file on the server. I think I may need file_put_contents? If anyone can suggest a method, or indeed a better approach to sending the outputs via sftp, its much apopreciated.

$result = mysql_query("SELECT * FROM `policy_details` WHERE `policyNumber` = '848938'"); 
if (!$result) die('Couldn\'t fetch records'); 
$num_fields = mysql_num_fields($result); 
$headers = array(); 
for ($i = 0; $i < $num_fields; $i++) 
{     
       $headers[] = mysql_field_name($result , $i); 
} 
$fp = fopen('php://output', 'w'); 
if ($fp && $result) 
{     

       header('Content-Type: text/csv');
       header('Content-Disposition: attachment; filename="export.csv"');
       header('Pragma: no-cache');    
       header('Expires: 0');
       fputcsv($fp, $headers); 
       while ($row = mysql_fetch_row($result)) 
       {
          fputcsv($fp, array_values($row)); 
       }
//die; 
}

Upvotes: 0

Views: 3275

Answers (2)

frosty
frosty

Reputation: 769

What I would do is change your php://output file pointer to the file on the server that you want to save to. Move your headers below your while loop, that way you're writing each line from the db to the file. Once you exit your while loop, read the file that you just wrote as one big variable and print echo it with your headers.

    $fp=fopen('/path/to/file/on/server/','w');

    while ($row = mysql_fetch_row($result)) 
     {
       fputcsv($fp, array_values($row)); 
     }
     fclose($fp);

    $csvFile = fopen('/path/to/file/on/server','r');
    $csvData = fread($csvFile,filesize('/path/to/file/on/server');
    fclose($csvFile);


   header('Content-Type: text/csv');
   header('Content-Disposition: attachment; filename="export.csv"');
   header('Pragma: no-cache');    
   header('Expires: 0');

   echo $csvData;

Obviously this isn't the whole script, but these are the pieces I would change to accomplish what you want to do.

Upvotes: 1

fred2
fred2

Reputation: 1110

Immediate thought would be that you don't need to send headers when saving a file. If you are just saving a regular csv file to the local server (and doing the FTP thing separately), I also don't see the benefit of using the 'php://' scheme. I'd use something like

$fp = fopen('/path/to/new/file.csv', 'w+');
if ($fp && $result) 
{     
       while ($row = mysql_fetch_row($result)) 
       {
          fputcsv($fp, array_values($row)); 
       } 
}

On the other hand, you could save the file directly over FTP, and avoid a 2 stage process.

Upvotes: 1

Related Questions