List records grouped by category name

Question

I am having some problems, trying to get data from my database, and show it correctly. I have a layout, where I have two "lists", where I can add rows to each lists. I can add multiple rows to each list.

I want to display the output, so the first list, along with all it's row is showed first and then the second list, along with it's rows.

Currently, it is showed like this:

    LIST #1
 -- row inside list #1 -- 
 -- row inside list #1 --

    LIST #2
 -- row inside list #2 --

Currently, it is displaying like this:

    LIST #1
 -- row inside list #1 -- 
    LIST #2
 -- row inside list #2 --
    LIST #1
 -- row inside list #1 --

Below is a visual:

This is my selection in PHP:

    $driving=$dbh->prepare("SELECT 
    driving_lists.id,
    driving_lists.list_id,
    driving_lists.date,
    driving_lists.list_name,
    driving_list_shipments.*
FROM
    driving_lists
        LEFT JOIN driving_list_shipments ON driving_lists.list_id = driving_list_shipments.list_id

    ");
    $driving->execute();

And the output script:

foreach( $driving as $row){
                echo "<pre>";
                echo $row["list_name"];
                echo "<br />";
                echo $row["reference"];
                echo "</pre>";
        }

What am I doing wrong?

Answer 1

Luckily you are using PDO which already has this functionality, that can do exactly what you want - group the data based on some column.

PDO can group results into the nested arrays, based on the first column in the field list. So you need to put your list id as the first column in the field list, and then get all rows using fetchAll() using aforementioned fetch mode:

$sql = "SELECT dl.list_id, dl.id, dl.date, dl.list_name, dls.*
  FROM driving_lists dl LEFT JOIN
     driving_list_shipments dls
 ON dl.list_id = dls.list_id
 ORDER BY dl.list_id";

$driving = $dbh->query($sql)->fetchAll(PDO::FETCH_GROUP);

and now you get a neat nested array where rows are grouped by list id!

To output neatly you have to use two nested foreach operators

foreach ($driving as $list_id => $list_data)
{
     echo $list_data[0]['list_name']."<br />\n"; 
     foreach ($list_data as $row)
     {
         echo "whatever";
     }
}