Exporting related records from mysql into csv file

Posted on 2005-05-02
Last Modified: 2012-08-14
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";

Question by:pmmarketing
    LVL 7

    Accepted Solution

    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'
    LVL 7

    Expert Comment

    woops change 'add_id' to 'aff_id'

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Suggested Solutions

    The Client Need Led Us to RSS I recently had an investment company ask me how they might notify their constituents about their newsworthy publications.  Probably you would think "Facebook" or "Twitter" but this is an interesting client.  Their cons…
    Part of the Global Positioning System A geocode ( is the major subset of a GPS coordinate (, the other parts being the altitude and t…
    The viewer will learn how to dynamically set the form action using jQuery.
    The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now