Exporting related records from mysql into csv file
Posted on 2005-05-02
I am trying to export certain records from my line items table into a csv file. I have been able to successfully export the entire table however, now I need specific records. Here is how my mysql database is layed out:
The ORDERS table is the order data for the shopping cart and the line items table is for each individual line item. I have one to many relationship between orders and line_items, order_id links the 2 tables together.
I basically need to query the orders table for the affiliate id and that the STATUS field is 'closed'. This means the order has been processed successfully. I simply need to export the entire line item data into a csv file where the status in the ORDERS table = 'closed' and the affilate id = 'affiliate id'.
Here is my code so far which exports an empty file: (the $db and mysql select db lines have been removed on purpose)
$query = "SELECT * from orders, line_items WHERE orders.aff_id = '$id' AND orders.aff_id = line_items.aff_id AND orders.status = 'closed'";
$result = mysql_query($query, $db) or die(mysql_error() . " " . mysql_errno());
//No query failure, so we can output the right headers and the file data:
header("Content-Disposition: attachment; filename=$exportName");
//you can change report.csv to any filename you like
while($row = mysql_fetch_assoc($result))
echo '"'.implode('","', $row).'"'. "\n";