Koppelen via een koppeltabel in PHP

Stel dat een gebruiker in verschillende steden is geweest. In dit geval zou de tabel met gebruikers er als volgt uit kunnen zien:

users
id	name	city
1	user1	city1, city2, city3
2	user2	city1, city2
3	user3	city2, city3
4	user4	city1

Het is duidelijk dat het onjuist is om gegevens op deze manier op te slaan - steden moeten in een aparte tabel worden ondergebracht. Hier is deze:

cities
id	name
1	city1
2	city2
3	city3

We moeten er echter voor zorgen dat elke gebruiker naar meerdere steden kan verwijzen. Met twee tabellen is dit onmogelijk.

We moeten een zogenaamde koppeltabel introduceren, die de gebruiker met zijn steden zal verbinden.

In elke record van deze tabel wordt een verbinding tussen een gebruiker en één stad opgeslagen. Hierbij zullen er voor één gebruiker evenveel records in deze tabel zijn als het aantal steden waar hij is geweest.

Hier is onze koppeltabel:

users_cities
id	user_id	city_id
1	1	1
2	1	2
3	1	3
4	2	1
5	2	2
6	3	2
7	3	3
8	4	1

De tabel met gebruikers zal alleen gebruikersnamen opslaan, zonder koppelingen:

users
id	name
1	user1
2	user2
3	user3
4	user4
5	user5

Query's

Laten we een query maken waarmee we gebruikers samen met hun steden kunnen ophalen. Hiervoor moeten we twee joins uitvoeren: de eerste join voegt de koppeltabel toe aan de gebruikers, en de tweede join voegt via de koppelingen de steden toe:

SELECT
	users.name as user_name, cities.name as city_name
FROM
	users
LEFT JOIN users_cities ON users_cities.user_id=users.id
LEFT JOIN cities ON users_cities.city_id=cities.id

Query-resultaat

Het resultaat van onze query in PHP zal de naam van elke gebruiker even vaak bevatten als het aantal steden waarmee hij verbonden is:

<?php
	$arr = [
		['user_name' => 'user1', 'city_name' => 'city1'],
		['user_name' => 'user1', 'city_name' => 'city2'],
		['user_name' => 'user1', 'city_name' => 'city3'],
		['user_name' => 'user2', 'city_name' => 'city1'],
		['user_name' => 'user2', 'city_name' => 'city2'],
		['user_name' => 'user3', 'city_name' => 'city2'],
		['user_name' => 'user3', 'city_name' => 'city3'],
		['user_name' => 'user4', 'city_name' => 'city1'],
	];
?>

Het zou handiger zijn om zo'n array te converteren en om te zetten in het volgende:

<?php
	$res = [
		['user1' => ['city1', 'city2', 'city3']],
		['user2' => ['city1', 'city2']],
		['user3' => ['city2', 'city3']],
		['user4' => ['city1']],
	];
?>

Laten we code schrijven die een dergelijke conversie uitvoert:

<?php
	$res = [];
	
	foreach ($data as $elem) {
		$res[$elem['user_name']][] = $elem['city_name'];
	}
	
	var_dump($res);
?>

Praktische opdrachten

Stel dat een product tot meerdere categorieën kan behoren. Beschrijf de opslagstructuur.

Schrijf een query die de producten samen met hun categorieën ophaalt.

Toon de verkregen gegevens als een lijst ul zodanig dat in elke li eerst de productnaam staat, en na de dubbele punt, gescheiden door komma's, de categorieën van dit product worden opgesomd. Ongeveer zo:

<ul>
	<li>product1: category1, category2, category3</li>
	<li>product2: category1, category3</li>
	<li>product3: category1</li>
</ul>