CODEWITHSUNDEEP
phpmysql
Adam Evers
Adam Evers

Reputation: 107

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 ...

  1. Several fields (10) need to be in 1 CSV file
  2. 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($rsSearchResults)) {
 for ($i = 0; $i < $columns; $i++) {
 $out .='"'.$l["$i"].'",';
 }
 $out .="\n";
 }
 return $out;
 }
exit;

Upvotes: 1

Views: 1943

Answers (2)

Cullen Walsh
Cullen Walsh

Reputation: 4388

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)

Upvotes: 2

Andr&#233; Hoffmann
Andr&#233; Hoffmann

Reputation: 3553

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.

Upvotes: -1

Related Questions