PHP for loop on SQL results

Question

I'm currently working on a project for school. I have 2 SQL tables and one table to connect the two. Table one contains people, table 2 contains cars. The connect table contains a primary key, and 2 values. On value is the primary key of a person, the other is the primary key of a car. This table connects the people to the cars. With this I can make a table in PHP in which I can see who has which car.

This works fine, however every person that has multiple cars is shown more than once. So, for example: person one had both car number one and number two. The table now shows the name of person on 2 times and behind his name the car. I would like it to show a table with the names of the persons just once, and then one or more cars behind that.

Below is an example of the way I have it set up now:

$query = "SELECT firstname, lastname, car FROM people, cars, connecttabel         
WHERE connecttabel.personnumber = people.personnumer
AND connecttabel.carnumber = cars.carnumber";
$result = mysqli_query($database, $query)
or die ("error");
echo "<table>";
while($record=mysqli_fetch_array($result))
{$firstname = $record['firstname'];

and so on. With the variables I fill in a table.

Does anyone have any idea how to help me out here? I think it should be done with a for loop but I've been trying for the last 3 hours and I can't get it done.

Answer 1

I assume your query returns something like:

|   first_name   |   last_name   |   car   |
+------------------------------------------+
| John           | Doe           | Mustang |
| John           | Doe           | Camaro  |
| John           | Doe           | SL55    |
| Jane           | Deo           | Prius   |
| Jane           | Deo           | Renegade|

If you want just the number of cars each one have, you might try:

SELECT firstname, lastname, COUNT(car) FROM people, cars, connecttabel         
WHERE connecttabel.personnumber = people.personnumer
AND connecttabel.carnumber = cars.carnumber GROUP BY firstname, lastname;

This will give you:

|   first_name   |   last_name   |COUNT(car)|
+-------------------------------------------+
| John           | Doe           | 3        |
| Jane           | Deo           | 2        |

But if you want something like...

|   first_name   |   last_name   |       car_list        |
---------------------------------------------------------+
| John           | Doe           | Mustang, Camaro, SL55 |
| Jane           | Deo           | Renegade, Prius       |

You can use Group Concat:

SELECT firstname, lastname, GROUP_CONCAT(DISTINCT car ORDER BY car) AS car_list FROM people, cars, connecttabel         
WHERE connecttabel.personnumber = people.personnumer
AND connecttabel.carnumber = cars.carnumber GROUP BY firstname, lastname

Answer 2

$query = "SELECT firstname, lastname, car FROM people, cars, connecttabel         
WHERE connecttabel.personnumber = people.personnumer
AND connecttabel.carnumber = cars.carnumber Group by people.id";
$result = mysqli_query($database, $query)
or die ("error");
echo "<table>";
while($record=mysqli_fetch_array($result))
{$firstname = $record['firstname'];