PHP/Mysql issue...

[ktsirig]

Hi all!

I have a problem concerning data retrieval and ,in particular, how they are shown to the user.
To be more specific, assume we have the following tables[Mysql database]:

id name vehicle
1 nick LOTUS
2 nick BMW
3 peter MASERATI
4 mary FERRARI
5 lisa PEUGEOT
6 peter HARLEY
7 peter SEAT
8 lisa MERCEDES
9 steven LADA
10 george JEEP

If you run a query like:

$query = "SELECT name, vehicle from TABLE";

you will get the 10 rows as expected.
When it comes to printing, you have:

$result = mysql_query($query);
while ($line = mysql_fetch_row($result) )
{
echo $line[0]."\t".$line[1];
}

where, $line[0] is NAME and $line[1] is VEHICLE
and you get :

nick LOTUS
nick BMW
peter MASERATI
mary FERRARI
peter HARLEY
peter SEAT
... ...

What I want to do is print the results in the following way:

nick: LOTUS|BMW
peter: MASERATI|HARLEY|SEAT
mary: FERRARI
lisa: PEUGEOT|MERCEDES
steven LADA
george JEEP

that is, have one row per person. Is this done by , somehow, checking in the 'while loop' for something
,or is there a special function in Mysql that I am not aware of?

[Pheonix]

In your sql query sort by name, then...

PHP: Select all

$last = '';
$result = mysql_query($query);
while ($line = mysql_fetch_row($result) )
{
if($last == $line[0]) echo " | ".$line[1];
elseif(empty($last)) echo $line[0]."\t".$line[1];
else  echo "\n".$line[0]."\t".$line[1];
$last = $line[0]
} 

Of course you will need to change things to meet your needs, but that's the gist.

[guelphdad]

Even simpler than that since you are using mysql use group_concat in your query.

Code: Select all

select user, group_concat(vehicle)
from yourtablename
group by user