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]:

idnamevehicle
1nickLOTUS
2nickBMW
3peterMASERATI
4maryFERRARI
5lisaPEUGEOT
6peterHARLEY
7peterSEAT
8lisaMERCEDES
9stevenLADA
10georgeJEEP

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 :

nickLOTUS
nickBMW
peterMASERATI
maryFERRARI
peterHARLEY
peterSEAT
......

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
stevenLADA
georgeJEEP

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?

[jatar_k]

I imagine you could order them using

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

then in your while loop you would have to do a little extra. You test to see if the name in the new row at 0 is the same as the last. If it is you output a comma and the car name, if it is not then you need to output a linebreak and the output the name and the car name.

[inveni0]

I've done something similar to this. I assume that you'll have to mess around with the GROUP BY statement until you get the results you want.

[coopster]

MySQL has a GROUP_CONCAT feature that would likely work here but if I was you I would stick with the recommendation from jatar_k. Here is a similar example that may give you some ideas on how to get started.

[webmasterworld.com...]

[tc3driver]

I think you need to break up your tables for more control over your content

such as :

Table: names
ID Name
1 nick
2 peter
Table: autos
ID NID Car1 Car2
1 2 Maserati
2 1 Lotus BMW

then in your while statement

you call to both tables creating recurring arrays to grab all the information you need, yes, it is a bit more work, but you have more control over your data, and also more control over the way your data is displayed.