Save CSV file to server

[cjackson111]

Hello all. I have a script that exports data from mysql to a csv file. Everything works great. However, I do not know how to save the csv file to the server. Does anyone have any ideas how I would do that? The script is below. Thanks for all help!

 

<?php
$host = 'localhost';
$user = '';
$pass = '';
$db = '';
$table = '';
$file = 'export';

function escape_csv_value($value) { 
$value = str_replace('"', '""', $value); // First off escape all " and make them "" 
if(preg_match('/,/', $value) or preg_match("/n/", $value) or preg_match('/"/', $value)) { // Check if I have any commas or new lines 
return '"'.$value.'"'; // If I have new lines or commas escape them 
} else { 
return $value; // If no new lines or commas just return the value 
} 
} 


$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());
mysql_select_db($db) or die("Can not connect.");

$result = mysql_query("SHOW COLUMNS FROM ".$table."");
$i = 0;
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$csv_output .= $row['Field'].", ";
$i++;
}
}
$csv_output .= "\n";

$values = mysql_query("SELECT * FROM ".$table."");
while ($rowr = mysql_fetch_row($values)) {
for ($j=0;$j<$i;$j++) {
$csv_output .= escape_csv_value($rowr[$j]).','; 
//$csv_output .= $rowr[$j].", ";
}
$csv_output .= "\n";
}

$filename = $file."_".date("Y-m-d_H-i",time());
//header( "Content-Type: application/save-as" ); 

header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: attachment; filename=".$filename.".csv"); 




//header( "Content-disposition: filename=".$filename.".csv");
print $csv_output;
exit;
?>

[Adam]

Just use file_put_contents. You already have the contents ($csv_output) and the file name ($filename) defined.

[cjackson111]

Thanks. I now see that the file is saved on the server, however it does not have the .csv extension to it. Any ideas what I may be doing wrong?

 

I have added the following code --

 

file_put_contents($filename, $csv_output);

[Adam]

Ah yes. I didn't take too much notice of what the contents of $filename were before, but later in the code you're separately adding the extension:

 

header( "Content-disposition: attachment; filename=".$filename.".csv");

 

Either add the extension when you originally define $filename (removing it from lines like above) or just do the same for the file_put_contents() call.

[cjackson111]

Got it. Thanks so much for your help!

[cjackson111]

I am now trying to ftp the saved file to a remote server but can't seem to get it to work. The following is the code I have for that --

 

//FTP file to remote server

     

  $ftpStream = ftp_connect("ftp.domain.com");

$loginResult = ftp_login($ftpStream, "username@domain.com", "password");

if ($loginResult) {

  ftp_put($ftpStream, "target.txt", "target.txt", FTP_ASCII);

}

ftp_quit($ftpStream);

[DanielJay]

Are you still having issues with your code?

 

*Nice to see my code being used in projects. Do you have a site up using this code?

[mikosiko]

Just to share some alternative to this CSV file creation script.... another alternative way to do it  is just using the syntax:

SELECT .... INTO OUTFILE ...

 

only requirement is that the user  must have FILE privileges in the DB... a working example:

<?php

   // Define how to display/report errors in your development environment
   error_reporting(E_ALL);
   ini_set("display_errors", "1");
   
   $host = 'localhost';
   $user = 'yourusername';
   $pass = 'yourpassword';
   $db = 'yourdb';
   $table = 'yourtable';
   $file = "path where you want your file"  // p.e: "C:\\\stats-export2.csv"."_".date("Y-m-d_H-i",time());



   $link = mysql_connect($host, $user, $pass) or trigger_error("Can not connect." . mysql_error(), E_USER_ERROR);
   mysql_select_db($db) or trigger_error("DB not selected." . mysql_error(), E_USER_ERROR);

   $query = "SELECT * INTO OUTFILE '{$file}'
     FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
     LINES TERMINATED BY '\n'
     FROM $table";

   // var_dump($query);  // for debugging purposes only
   $values = mysql_query($query) or trigger_error("Error : ".mysql_error(), E_USER_ERROR);

   if ($values) {
      // File Generated OK
      echo "Ok";
   } else {
      // Something was wrong
      echo "No-OK";
   }   
?>