PHP CSV export function
I am trying to export data from my MySQL database to multiple CSV files and then GZIP them.
I have a couple of problems which make exporting it to a CSV file complicated ...
- Several fields (10) need to be in 1 CSV file
- Other fields (multiple!) are serialized and need to be exported into a different CSV file with two other non serialized fields included in that CSV file.
What I want to do is have all those CSV files created in a gzip file ... Here's what I have so far! What happens it the user checks different check boxes with which file they want to export. Then its passed to this file where each file has a different SELECT query and field names. I haven't even started to deal with the serialized data yet ... I have no idea how I am going to do that. It then returns the CSV file and I place that file into an array which is waiting to be exported into a gzip ... don't even know if I can do that.
Any suggestions or help would be greatly appreciated!
$files = array();
if(in_array("general", $_POST['exportid'])){
$files[] = CSVoutput("SELECT timestamp, id, monday, tuesday, wednesday, thursday, friday, saturday, sunday, records, 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 23, 24 FROM hourly", array(timestamp, id, monday, tuesday, wednesday, thursday, friday, saturday, sunday, records, 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 23, 24));
}
function CSVoutput($query, $fields){
$rsSearchResults = mysql_query($query) or die(mysql_error());
$out = '';
$columns = count($fields);
// Put the name of all fields
for ($i = 0; $i < $columns; $i++) {
$l= $fields[$i];
$out .= '"'.$l.'",';
}
$out .="\n";
// Add all values in the table
while ($l = mysql_fetch_array开发者_StackOverflow中文版($rsSearchResults)) {
for ($i = 0; $i < $columns; $i++) {
$out .='"'.$l["$i"].'",';
}
$out .="\n";
}
return $out;
}
exit;
Check out the fputcsv() function. It will handle quotes/escaping much better than what you have.
res = query_result
row = fetch array from res
handle/remove serialized columns in row
fputcsv(row array keys)
fputcsv(row values)
while(row = fetch from res)
handle/remove serialized columns in row
fputcsv(row values)
You might have a reason for doing it this way, but I just wanted to mention that SELECT ... INTO OUTFILE
exports the result of the select into a csv file.
You can also specify how the fields are delimited and so on. So you might want to give it a try.
Also see this page(scroll down to SELECT INTO OUTFILE) http://dev.mysql.com/doc/refman/5.1/en/select.html
If you need to do this manually then please ignore my comment.
精彩评论