How to split the export of a sql query with fputcsv

Question

I have a simple fputcsv command for my sql query. But sometimes my export has more lines and different primary keys. My wish is to split every result with a different primary key to a new csv export.

Example:

| *row 1   | row 2   | row 3   |
|.......27 |......45 |......aa |
|.......27 |......35 |......ab |
|.......28 |......85 |......bb |
|.......28 |......65 |......bc |

Actual situation: The row 1 has the primary key (order_id). I run a cronjob every day with all orders. So the file has more than one order (as the example above = order 27, order 28 etc.). My supplier needs for each order a csv file. So i need a code, who split the query in something like "if order_id is different so create a new file...and so on...". Is this possible?

<?php
$servername = "Jarvis";
$username = "TonyStark";
$password = "iLoveIronMan";
$dbname = "TheAnvengers";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT o.order_id, o.customer_id, op.quantity, op.model FROM oc_order o INNER JOIN oc_order_product op ON o.order_id = op.order_id INNER JOIN oc_product p ON op.product_id = p.product_id WHERE o.order_status_id = 2 AND p.location = 1 ORDER BY o.order_id, op.model";

$file = fopen('../files/in/filename.csv', 'w');
if ($rows = mysqli_query($conn, $sql)) {
    while ($row = mysqli_fetch_assoc($rows)) {
        fputcsv($file, $row, ';');
    }
    mysqli_free_result($rows);
}
mysqli_close($conn);
fclose($file);
?>

Answer 1

<?php
// connect to the database
$servername = "Jarvis";
$username = "TonyStark";
$password = "iLoveIronMan";
$dbname = "TheAnvengers";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

// Get the data
$sql = "SELECT o.order_id, o.customer_id, op.quantity, op.model FROM oc_order o INNER JOIN oc_order_product op ON o.order_id = op.order_id INNER JOIN oc_product p ON op.product_id = p.product_id WHERE o.order_status_id = 2 AND p.location = 1 ORDER BY o.order_id, op.model";

$orders = array();    // For storing the data by order_id

// Loop through the result set and populate $orders
if ($rows = mysqli_query($conn, $sql)) {
    while ($row = mysqli_fetch_assoc($rows)) {

        // This is where the split on order_id occurs 
        $orders[$row['order_id']][] = $row;

        /*
            $orders will now look like this after the first iteration
            $orders = array(
                 27 => array(
                     0 => array(
                         'order_id' => 27,
                         'customer_id' => 45,
                         'quantity' => aa,
                         'model' => someModel
                     )
                 )
            );

            ==============================

            $orders will look like this after the last iteration
            $orders = array(
                 27 => array(
                     0 => array(
                         'order_id' => 27,
                         'customer_id' => 45,
                         'quantity' => aa,
                         'model' => someModel
                     ),
                     1 => array(
                         'order_id' => 27,
                         'customer_id' => 35,
                         'quantity' => ab,
                         'model' => someModel
                 ),
                 28 => array(
                     0 => array(
                         'order_id' => 28,
                         'customer_id' => 85,
                         'quantity' => bb,
                         'model' => someModel
                     ),
                     1 => array(
                         'order_id' => 28,
                         'customer_id' => 65,
                         'quantity' => bc,
                         'model' => someModel
                 )

            );


        */

    }
    mysqli_free_result($rows);
}
mysqli_close($conn);

// Loop through $orders (by order_id)
foreach($orders as $id => $order)
{
    // Open the file for writing, blanking existing file or creating if non-existent - name it after the order_id
    $f = fopen('../files/in/' . $id . '.csv', 'w');

    // Loop through each $row for that particular order_id and put it into the csv
    foreach($order as $entry)
    {
        fputcsv($f, $entry, ';');
    }

    fclose($f);
}

?>