Page 1 of 1

export to csv

Posted: Sat Jul 18, 2009 4:35 am
by martinc
I have managed to export my db and column names to a csv file using the following code

Code: Select all

<?
// Connect database
$database="dbname";
$table="orders";
mysql_connect ("xxx","xxx","xxx");
mysql_select_db ("xx");
 
$result=mysql_query("SELECT * FROM orders where date >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)");
 
$out = '';
 
$fields = mysql_list_fields(xx,$table);
 
// Count the table fields and put the value into $columns.
$columns = mysql_num_fields($fields);
 
 
// Put the name of all fields to $out.
for ($i = 0; $i < $columns; $i++) {
$l=mysql_field_name($fields, $i);
$out .= '"'.$l.'",';
}
$out .="\n";
 
// Add all values in the table to $out.
while ($l = mysql_fetch_array($result)) {
for ($i = 0; $i < $columns; $i++) {
$out .='"'.$l["$i"].'",';
}
$out .="\n";
}
 
// Open file export.csv.
$f = fopen ('export.csv','w');
 
// Put all values from $out to export.csv.
fputs($f, $out);
fclose($f);
 
header('Content-type: application/csv');
header('Content-Disposition: attachment; filename="export.csv"');
readfile('export.csv');
?>
One of the fields (eg cart) contains the following data which is (product : qty : total : subtotal) each set seperated by a ~

Product1 : 1 : 2.49 : 2.4900 : ~Product2 : 1 : 9.99 : 9.9900 : ~Product3 : 1 : 12.99 : 12.9900 :

I need to find someway to output new columns for "product : qty : total : subtotal" and to create a new line for each additional product (up to 20) where all other fields on the new line contain the same data.

Would be really grateful for any suggestions or pointers in the write direction.

Re: export to csv

Posted: Sat Jul 18, 2009 5:28 am
by DaiLaughing
Normalise it. productsOrdered should be in a separate table to Orders. One order to many products.

Incidentally this is a database design issue not a coding issue as such.