PHP script reqd to export MySql table to CSV

[marcus76]

Hi,

I need a PHP script which i can schedule via a CRON job which will export a table (last 24hrs records) to a CSV file.

Does anyone have such a script or similar code which i can use as a starting point.

Thanks

Marcus

[dcampbell]

Assuming you wish to download the csv file as you run the script, I just use a typical mysql query with the following header:

header( "Content-Type: application/save-as" );
header ('Content-Disposition: attachment; filename="myfile.csv"');

Otherwise a csv file is just a comma delimited file, output each record with commas between the fields and ensure each record is on it's own individual line.

Hope this helps.

[marcus76]

thanks for your post, but i'd like to create it locally on my webhost, i will then have a scheduled job to FTP the CSV file to my local (home PC).

Any ideas?

[jatar_k]

the steps involved woould be

1 connect to db
2 select desired records
3 create or open the destination file
4 step through the returned records writing them line by line into your destination file
5 close your destination file

mysql_connect
mysql_select_db
mysql_query
(you may want to check for 1 or more rows returned here)
fopen
mysql_fetch_array
- fwrite
fclose

[marcus76]

cool, mkaes sense - i can do this in vbscript no problem - anyone got some sample code i can butcher to do this write line business....

thanks

[marcus76]

meant to say - PHP syntax i'm not too good with...

[jatar_k]

pulling the line is a matter of

take each column returned in the row and concatenate it with a comma and end it with a newline

let's say there are 3 columns

$fp = fopen('/path/to/file.csv','w');
$q = 'select * from mytable';
$query = mysql_query($q);
while ($row = mysql_fetch_array($query)) {
$nextline = $row[0] . ',' . $row[1] . ',' . $row[2] . "\r\n";
fwrite($fp,$nextline);
}
fclose($fp);

something like that