mysql group_concat inside while

Question

I have this passenger table date:

Which means that in Reservation 000004, there are two itineraries which are Itinerary 0000010 (AAC Hangar - Anvaya Cove) and 0000011 (Anvaya Cove - AAC Hangar), and in every itinerary, there are two passengers (0000007 (taiga) and 0000011 riyuuji are passengers of 0000010 AAC Hangar - Anvaya Cove while0000008 minoriand0000012 amiare passengers of0000011 Anvaya Cove - AAC Hangar).

I'm trying to concat them using group_concat but I can do only like this:

    $a=mysql_query("select group_concat(pass_name separator ', ') as 'pass' from passengers where reservno = '0000004' order by id asc") or die(mysql_error());
    $b=mysql_fetch_array($a);
    $c=$b['pass']; 

with result: taiga, minori, riyuuji, ami.

But I want it to look like this: taiga, riyuuji / minori, ami

Is this possible using group_concat? I know this will involve while statements but as of now I still can't get the desired output. Please help.

Answer 1

You need to use GROUP BY clause

select group_concat(pass_name separator ', ') as 'pass' 
from passengers 
where reservno = '0000004' 
GROUP BY reservno, id
order by id asc

UPDATE 1

SELECT  group_concat(`pass` separator ' / ') `pass` 
FROM    ( 
            select group_concat(pass_name separator ', ') as 'pass' 
            from passengers 
            where reservno = '0000004' 
            GROUP BY reservno, id order by id asc 
        ) s