SQL PHP Display values from database in Groups with two Columns

Question

I have table called myTable and it looks like:

restid  |    menu  |    prod   |   price 
__________________________________________
 10     |     m1   |    prod1  |     15   
 10     |     m1   |    prod2  |     21   
 10     |     m2   |    prod3  |     36   
 10     |     m2   |    prod4  |     45  
 10     |     m3   |    prod5  |     12   
 10     |     m3   |    prod6  |     5    

and I want to group and display the results as below:

               m1
_________________________________________
      prod1        |        15
      prod2        |        21
_________________________________________
                   m2
_________________________________________
      prod3        |        36
      prod4        |        45
_________________________________________
                   m3
_________________________________________
      prod5        |        12
      prod6        |        5

I already grouped them using group_concat

SELECT DISTINCT `menu`, GROUP_CONCAT( DISTINCT `prod`
ORDER BY `prod` ) AS prod_list
FROM `myTable` WHERE restid = '10'
GROUP BY `menu`
ORDER BY `menu`

and then used php to display it properly...

but I want to include price in my results. I've been searching for an answer, but any ideas been found.

I'd be great if someone could give me some solution what can be used to achieve that result.

Answer 1

You can just add the price column in selection list. Somthing like this:-

SELECT DISTINCT `menu`, GROUP_CONCAT( DISTINCT `prod`
ORDER BY `prod` ) AS prod_list, `price`
FROM `myTable` WHERE restid = '10'
GROUP BY `menu`,`price`
ORDER BY `menu`;