Use multiple SQL queries to output into CSV format using PHP

Question

having some issues outputting some SQL queries into CSV format. I can successfully do one query and write it to CSV, but I need to do a query within a query and I get that awesome infinite loop. Here's what I have so far

header( 'Content-Type: text/csv' );
header( 'Content-Disposition: attachment;filename='.$filename);
$fp = fopen('php://output', 'w');

$result = mysqli_query($conn, "SELECT * FROM Customers")
    or die("Error: ".mysqli_error($conn));

$row = mysqli_fetch_assoc($result);
    if($row) {
        fputcsv($fp, array_keys($row));
        // reset pointer back to beginning
        mysqli_data_seek($result, 0);
    }

while($row = mysqli_fetch_assoc($result)) {
    $orderResult = mysqli_query($conn,"SELECT * FROM Orders WHERE order_No = '".$row['customer_ID']."'")
        or die("Error: ".mysqli_error($conn));
    $rowOrder = mysqli_fetch_assoc($orderResult);
    fputcsv($fp, array_keys($rowOrder));
    mysqli_data_seek($result, 0);
    $rowOrder = mysqli_fetch_assoc($orderResult);
    fputcsv($fp, $row);
    fputcsv($fp, $rowOrder);
}

fclose($fp);

The output i'm trying to achieve is:

Display customer header info in this row a.k.a first name, surname, email

next row displays info about customers e.g Tony, Mcguire, [email protected]

Display Tony's order header info e.g order number, order date

display Tony's order info e.g 5122, 6/3/2013

If I can get it all to display into 2 rows, that'd be even better.

m79lkm · Accepted Answer · 2014-03-06 05:32:43Z

1

This can be cleaned up a lot. If I'm understanding you right, you want to generate a report where row 1 is the header and each row after is customer/order data. This is an example I came up with:

$sql = "SELECT c.*, o.*  FROM Customers AS c
    JOIN Orders AS o ON c.CustomerID = o.CustomerID";

$result = mysqli_query($conn, $sql);

$i = 1;
while($row = mysqli_fetch_assoc($result)) {
    if ($i == 1) {
        // this is the header
        fputcsv($fp, array_keys($row));
        $i++;
    }

    // this is the customer/order data
    fputcsv($fp, $row);
}

You can specify which database fields you want to include by replacing the c.* with the customer fields (c.name, c.address, etc) and the o.* with your order fields (o.orderid, o.date, etc)

answered Mar 6, 2014 at 5:32

m79lkm

3,0801 gold badge24 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Akira Dawson Over a year ago

Mate, you deserve to get as drunk as skunk. At my expense. You bloody ripper! You have helped me save a lot of time and effort here! I'm definitely intruiged on how the JOIN statement works too. Thank you so so much!

Collectives™ on Stack Overflow

Use multiple SQL queries to output into CSV format using PHP

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related