Getting a nested array when multiple rows are linked to a single entry

1. Getting a nested array from a single query with join
2. Comments

There is a situation when we have to select some data from the table and also get multiple rows from another table linked to an entry from the main table. A frequent example is an article and comments. Or a category and products from that category.

Getting a nested array from a single query with join

The most direct solution for this task is to get all the data using a single SQL query with JOIN, such as,

SELECT c.id, c.name, c.url, p.id, p.name, p.price
FROM category c
JOIN product p ON p.category_id=c.id

There is a drawback of course, as it will return a lot of duplicated data. If the data from the main table is large, like a news article, it would be a waste to use this approach. But in case the data from the main table is relatively small, it is not a problem, so we could use a query like this:

In a rare case when we only need a single field from the main table, PDO already has a solution. But in case you need more than one field from each table, follow this article further on.

To get the data in a nested array we will need to create an empty array first and then fetch the returned rows one by one and check if we already have an array element for the main entry

• if not, we should create the element, along with a nested array for the linked rows
• if yes - just add another entry in the linked rows section

In order to make it work we will need to index the resulting array with some unique value. Let it be the category id.

So here is the code that does exactly this:

$sql = "SELECT c.id as c_id, c.name as c_name, c.url, 
               p.id as p_id, p.name as p_name, p.price
        FROM category c
        JOIN product p ON p.category_id=c.id
        ORDER BY c.name, p.name
        LIMIT ?,?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$start, $limit]);

$data = [];
while ($row = $stmt->fetch()) {
    if (!isset($data[$row['c_id']])) {
        $data[$row['c_id']] = [
            'name' => $row['c_name'],
            'url' => $row['url'],
            'products' => [],
        ];
    }
    $data[$row['c_id']]['products'][] = [
        'id' => $row['p_id'],
        'name' => $row['p_name'],
        'price' => $row['price'],
    ];
}