PHP/Mysql issue...

ktsirig posted this at 17:13 — 28th January 2006.

Joined: Oct 2005

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?

Greg K posted this at 18:44 — 28th January 2006.

He has: 2,145 posts

Joined: Nov 2003

The below should work for you.

First note that the query was changed to sort by the "NAME" as all rows with the same "name" have to come together. For this to work, you are basically checking to see if the "name" of the current row is the same as the last row (saved in the variable $lastname).

It is good programing practice to initialize any variable before comparing it, so we set it to be empty before starting the loop.

Also note, to make it easier to read the code, I used the FETCH_ASSOC instead of FETCH_ROW, so that the array has the data's keys matching the field names. Again this is good practice, as as you get to longer code, and more fields returned, this is easier to deal with $row['password'] instead of $row[8] especially when you are so many lines down that the original query is not viewable in your editor.

Now on to the loop.

The first thing we do is compare this rows name with the name from the last row (or nothing for the first row).

If it is the same person, we only need to output the | character. If it is not the same, we output a line break followed by the new name and the tab. Note, I included the extra if statement there so the line break is not done before the first name.

After all that, it outputs the vehilce name. Note, we do not put the line break here, as if the next record is the same person, we need it to be on the same line.

Lastly, we set the variable $lastname to be this records name to compare with on the next row.

Oh yeah, I gues really the last thing is after the while loop, we need to send out one final line break.

Hope this helps.

-Greg

<?php
$query = \"SELECT name, vehicle FROM table ORDER BY name \";
$result = mysql_query($query);

$lastname = \"\";

while ($line = mysql_fetch_assoc($result))
{
    if ($line['name']==$lastname)
        echo \"|\";
    else 
    {
        if ($lastname!=\"\") echo \"\n\";
        echo $line['name'] . \"\t\";
    }
        
    echo $line['vehicle'];
    $lastname = $line['name'];
}
echo \"\n\";
?>

nuk3 posted this at 02:33 — 29th January 2006.

They have: 238 posts

Joined: May 2002

And you can take Greg's example one step further and get some more control over the order of your results:

<?php
$query = \"SELECT name, vehicle FROM cars GROUP BY vehicle ORDER BY name, vehicle ASC\";
$result = mysql_query($query);

$lastname = \"\";

while ($line = mysql_fetch_assoc($result))
{
    if ($line['name'] == \"$lastname\")
    {
    echo \"|\";
    }
    else
    {
        if ($lastname != \"\") echo \"<br />\n\";
        echo $line['name'] . \"\t\";
    }
        
    echo $line['vehicle'];
    $lastname = $line['name'];
}

echo \"<br />\n\";
?>

I've used mySQL's "GROUP BY" and "ORDER BY" to achieve this.

Edit: fixed a small error in the code.