如何对结果进行分组并显示所有行？

Question

SELECT i.itemsname
     , i.itemsprice
     , i.itemsdescrip
     , c.catname
     , c.catdes
     , c.status
     , c.collapse
     , c.catid 
  FROM items i 
  LEFT
  JOIN categories c
    ON c.catid = i.catid 
 WHERE i.restid 
   AND c.restid =12 
GROUP 
    BY c.catid

that is my query at the moment but I would like to have something like this....

but this is what I'm getting:

Answer 1

Ok, I lied in the comments, so With PDO (haven't tested it)

$stmt = $PDO->prepare('SELECT
    categories.catname,
    items.itemsname,
    items.itemsprice,
    items.itemsdescrip,
    categories.catdes,
    categories.status,
    categories.collapse,
    categories.catid 
FROM items 
LEFT JOIN categories ON items.catid=categories.catid 
WHERE items.restid AND categories.restid = :restid');

$stmt->execute([':restid' => 12]);

$data = $stmt->fetchAll(\PDO::FETCH_GROUP);



foreach($data as $catname => $rows){
    //echo group html stuff
    //echo "<dl>";
    //echo "<dt>$catname</dt>".;

    foreach($rows as $row){
       //echo row data stuff

      // echo "<dd> {stuff} </dd>";

    }
   //echo "</dl>";
}

I'll leave the html up to you. But as I said you want a data structure like this

[
  'BREAKFASTS' => [
       0 => [ name => "wimpy hamburger", description => "bla bla", price => "$100,000"],
       1 => [ ... ]
  ],
  'SINGLE BURGERS' => [ ...]
]

note that the first field after "SELECT" is by default the field used by FETCH_GROUP

See in this way, the first foreach can output the title of the category, which is BREAKFASTS for example. Then the inner foreach can do the individual rows in the table.

Personally I would use a dl, dt, dd tag setup as my structure (hinted in the comments, i really am to lazy today to code all the html, <sigh>)

https://www.w3schools.com/tags/tag_dt.asp

UPDATE

You may want to check your query

    ...
 WHERE
    items.restid AND ...

Seems to be flawed, just saying. I saw this while optomizing the query for sorting.

   SELECT
    c.catname,
    i.itemsname,
    i.itemsprice,
    i.itemsdescrip,
    c.catdes,
    c.status,
    c.collapse,
    c.catid 
FROM
    (
        SELECT c0.catid FROM categories AS c0 WHERE c0.restid = :restid SORT BY c0.catname
    ) AS t
JOIN
     categories AS c ON t.catid=c.catid 
LEFT JOIN 
    items AS i ON items.catid=categories.catid 
WHERE
     items.restid = ? //< this is the error/omission/strangeness i pointed out above.

So a few things to note, first you should base the query off the categories, as an empty category should be shown, while an item without a category will blow it all to bits ( basically, ie how can you group them by the category if they have none ) You'll wind up with some hodgepoge of items with no category at the end, of course based on your example I'm assuming a Many to One relationship. For example One category can have Many items, and Many items can belong to a category. (it's probably more ideal to do a Many to Many, but that's another story for another day)

The reason the above query is more optimized is the inner query, creates only a small temp table using the catid, And sorts on just the data from the cat table and only the data that is pulled by the where.

Then as we move to the outer query, they basically inherent the sort from the join, and we can pull the rest of the data from that. It's typically about 2-10x faster this way (of course I haven't test this particular query) in theory. Of course this is a bit more complex/advanced query and is optional, but it should improve sort performance if my mind is in the right place tonight... lol

Also I abbreviated your table names (alias), as I said I am lazy like that. Sadly my answers are always so long, dont ask me how I see all these issues, it's just experience or how my dyslexic brain works?

Lastly, if you really must use mysqli, you can manually group them with something like this.

 $data = [];

 while(false !== ($row = $res->fetch_assoc())){
     $key = $row['catname'];
     if(!isset($data[$key]))  $data[$key] = [];

     $data[$key][] = $row;
}

It's all so prosaic (common place, non-poetic) at this point for me.

Good luck.