Output SQLquery as CSV

darkpsyd

I am new to PHP and I've been beating my head again the wall trying to get this to work with no success; I have an SQL query that grabs data from a handful of tables (product name, category, price and product code) then cleans the code up and outputs an RSS feed that works fine.

What I need to do is output the same data as a CSV file; I have searched everywhere and have found some code examples I was able to get to output the entire contents of a single table but none that I could get to work with a query.

Below is a slimmed down version of what I have however the output file has no content and I just don't know what to do from here so if anyone can help I would appreciate it

<?php
include("../inc/db.php");

$product_table = mysql_query("SELECT * FROM ds_products, ds_language_products, ds_product_categories, ds_product_group_pricing
WHERE ds_products.pID = ds_language_products.pID
AND ds_products.pID = ds_product_categories.pID
AND ds_products.pAvail = '$available'
AND ds_product_categories.catID = 114
AND ds_product_group_pricing.pID = ds_language_products.pID
AND ds_product_group_pricing.pgID = 6") or die(mysql_error());

while ($rowr = mysql_fetch_row($product_table)) {
for ($j=0;$j<$i;$j++) {
$csv_output .= $rowr[$j]."; ";
}
$csv_output .= "\n";
}

$file = 'export';
$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/csv");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");
print $csv_output;
exit;
?>

Crowly

Use implode() instead of the for loop 🙂
Also updated the select to use joins and aliases

<?php
include("../inc/db.php");

$sql="SELECT * FROM ds_products p
INNER JOIN ds_language_products lp ON p.pID = lp.pID
INNER JOIN ds_product_categories pc ON p.pID = pc.pID
INNER JOIN ds_product_group_pricing pgp ON pgp.pID = lp.pID
WHERE p.pAvail = '".$available."'
AND pc.catID = 114
AND pgp.pgID = 6";

$product_table = mysql_query($sql) or die(mysql_error());

$csv_output='';
while ($rowr = mysql_fetch_row($product_table)) {
  $csv_output .= implode(';',$rowr)."\n";
}

$file = 'export';
$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/csv");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");
print $csv_output;
?>

I've tested it with this modified script, and that worked

$data[0]=array('ServicePic'=>'foto1','Date'=>'01-01-1935','Keyword'=>'something','Comment'=>'Something more');
$data[1]=array('ServicePic'=>'foto2','Date'=>'01-01-1935','Keyword'=>'something 2','Comment'=>'Something more');
$data[2]=array('ServicePic'=>'foto3','Date'=>'01-01-1935','Keyword'=>'something 2','Comment'=>'Something more');
$data[3]=array('ServicePic'=>'foto4','Date'=>'01-01-1935','Keyword'=>'something 2','Comment'=>'Something more');

$csv_output='';
foreach ($data as $row) {
$csv_output.=implode(';',$row)."\n";
}

$file = 'export';
$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/csv");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header("Content-disposition: filename=".$filename.".csv");
print $csv_output;

bradgrafelman

And what happens when the data contains the delimiter you're using (e.g. a semicolon)?

I'd recommend using [man]fputcsv/man instead.

darkpsyd

Thank you for your work however I am still unable to produce an output file.
I used your version of the query in my original RSS feed which worked fine as did your test script but for some reason the code didn't output anything to the csv.
I forced a dummy string to the csv just so there would be something in the output and this wouldn't work if I placed it before the while loop it had to be done after - not sure if this means anything.

Is there anything you can suggest I try from here?

Crowly

I dont know anything about RSS, ive opened the csv in Excel and the information was placed in the cells (rows and columns) as expected. If i commented out the header information and just printed $csv_output to the screen, it was also as expected. So i may be that it isn't formated correctly for RSS use.