在php中显示每种类型的组的mysql数据

Question

I have my table structure like as below:

tbl1

id  prodid prodname height cost category
-----------------------------------------
 1    1     Test      5     54    ABC
 2    5     Test1     6     85    DEF
 3    8     Test2     8     20    DEF
 4    2     Test3     4     10    GHI
 5    3     test4     8     58    ABC
 6    4     Test5     84    878   ABC

tbl2

 id(FK of pid)   color intensity vibrance
-----------------------------------------
  1                 red   5        NA
  5                 pink  0.5      8 ..and so on

Now i want the output like as below,

Want Output

ABC
----
 Test ... & other parameters
 test4 ... & other parameters
 Test5 ... & other parameters
DEF
---
 Test1 ... & other parameters
 Test2 ... & other parameters
GHI
----
 Test3 ... & other parameters

Query I have tried is:

"SELECT tbl1.*,tbl2.* from tbl1 LEFT JOIN tbl2 on tbl1.prodid=tbl2.id;

PHP

i tried to show cat as below:

$category="";
foreach($all as $row){
  if ($row['category'] != $category && !empty($row['category'])) {
        echo $row['category']; $category=$row['category'];
  }
  echo $row['othercolumns'];
}

But it is not grouping... It is repeating every time.

Answer 1

You can use PDO, which has a nice feature for you with PDO::FETCH_GROUP option in PDOStatement::fetchAll() function. Firstly in your query, make category the first column, like this way :

SELECT tbl1.category, tbl1.* from tbl1 LEFT JOIN tbl2 on tbl1.pid=tbl2.id;

Then run the query in PDO and fetchAll with PDO::FETCH_GROUP

$sth = $dbh->prepare($query);
$sth->execute();
$result = $sth->fetchAll(PDO::FETCH_ASSOC|PDO::FETCH_GROUP);
print_r($result); 

//Result will be something like this:

Array (
    [ABC] => Array
        [0] => Array
            (
               [id] => 1,
               [prodid] => 1,
               [prodname] => "test",
               [height] => 5,
               [cost] => 54,
               [category] => "ABC",
            ),
        [1] => Array
            (
             ...
            ),
        ....
        ),
    [DEF] => Array
        (
         ...
        ),
    ....
)