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?

nemonoman

This can't be done with a SQL query, which will return each row's information discretely, as you have found.

What you are trying to do requires modifying your while loop and your SQL

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

then you'll need to check when name changes:

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

or something similar. Example code only; not debugged or tested.