Exporting related records from mysql into csv file

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";

Who is Participating?
jdpipeConnect With a Mentor Commented:
Change your query SQL to the following. I think your problem was that you weren't linking the order_id field properly.

Hope that helps--- JP

  , line_items
  orders.order_id = line_items,order_id
  AND orders.add_id=$affid
  AND orders.status='closed'
woops change 'add_id' to 'aff_id'
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.