echo SQL PDO object in php foreach loop

Question

I've got table of categories. One item may have more than one category in table3. Three tables as below:

table1:

ID  Item_id
1   1
2   2
3   3
4   4
5   5

table2:

   ID Item_id Category
   1   1      cat1
   2   2      cat1
   3   2      cat2
   4   3      cat2
   5   3      cat3
   6   3      cat4
   7   4      cat2
   8   5      cat1
   etc.

third table has only information of the name of category:

table3:

Category_id  category_name
cat1        apples
cat2        oranges
cat3        bananas
cat4        pineapples

I made a query to get PDO object of all items and be able to echo all of the categories from each one in php.

First query:

public function getCategories(){
$this->db->query('SELECT table1.*, table2.*, table3.*
       FROM table1
       INNER JOIN table2
       ON table1.item_id = table2.item_id
       INNER JOIN table3
       ON table2.category=table3.category_id');

$results = $this->db->resultset();
return $results;}

$items = $offer_class -> getCategories();

How can I take all category name of each item in foreach loop, like below:

echo: Item1: apples

echo: Item2: apples, oranges

echo: Item3: oranges, bananas, pineapples

I've tried this foreach loop, but it gives me only the first category name for each item:

<?php foreach($items as $item){
echo $item->category_name; 
}
?>

Jakub Dąbek Jakub Dąbek · Accepted Answer · 2017-10-21T19:26:35

JOIN returns single records sequentally, so the result would look more or less like this:

1, cat1, apples
2, cat1, apples
2, cat2, oranges
3, cat2, oranges
3, cat3, bananas
3, cat4, pineapples

You'd have to group the records by Item_id and print all category names at once (this is gonna be in pseudo-pseudo-code as I don't know php and I'm copy-pasting from several other answers):

$dict = [];
foreach($items as $item) {
    $id = $item->Item_id
    $dict[$id][] = $item;
}

// here print all the values from the arrays
foreach($result as $arr) { 
    echo ??? // print the category_name for each element in $arr in the same line
}

echo SQL PDO object in php foreach loop

1 Answers