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?

BlazingWolf

$query = "SELECT name, vehicle from TABLE ORDER BY name"; Not sure if that is correct SQL but it's something like that.

[PHP]while ($line = mysql_fetch_row($result) )
{
if(isset($prevName))
{
if($prevName == $line[0])
{
echo = " | " . $line[1];
}
else
{
echo = "<br />" $line[0] . ":" . $line[1];
}//endif
}
else
{
echo = "<br />" $line[0] . ":" . $line[1];
}//endif
$prevName = $line[0];
}//endwhile[/PHP]


I think that should do what your looking for. If it gives you errors tell me. You could also probably fix it by restructing your database if that is a posibility.

__________________
_______________________________
BlazingWolf

Ooble

When you have duplicate information in a table, it's not normalised. You should really have three tables here:
We're working in the form table_name (primary_key, column1, column2). When you've organised your data so you don't have any duplicates, you should be able to find the cars for each person:
[php]$people = mysql_query("SELECT person_id, name FROM people ORDER BY name");
while ($person = mysql_fetch_row($result))
{
echo $person[1] . ': ';
$cars = mysql_query("SELECT vehicle.manufacturer, vehicle.model FROM vehicle, drives WHERE drives.person_id = {$person[0]} AND vehicle.vehicle_id = drives.vehicle_id");
while ($car = mysql_fetch_row($cars))
{
echo $car[0] . ' ' . $car[1] . ' ';
}
echo "<br />\n";
}[/php]
If you're not too sure on how those SQL statements, I'm more than happy to explain them, but you should probably take it as a sign you need to brush up on your SQL.

Disclaimer: code wasn't tested, but I'm pretty sure it'll work with a couple of bugfixes. :p

__________________
Me :: You :: Them