• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

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:

Tables:
ORDERS
LINE ITEMS

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-type:text/csv');
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";


0
pmmarketing
Asked:
pmmarketing
  • 2
1 Solution
 
jdpipeCommented:
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

SELECT
 *
FROM
  orders
  , line_items
WHERE
  orders.order_id = line_items,order_id
  AND orders.add_id=$affid
  AND orders.status='closed'
0
 
jdpipeCommented:
woops change 'add_id' to 'aff_id'
JP
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now