Linking Through a Junction Table in PHP

Suppose now a user has been to different cities. In this case, the users table could look like this:

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

It is clear that storing data this way is incorrect - cities should be moved to a separate table. Here it is:

cities
id	name
1	city1
2	city2
3	city3

However, we need to make it so that each user can be linked to several cities. Using two tables, this is impossible to achieve.

We need to introduce a so-called junction table, which will link the user to their cities.

Each record in this table will store a link between a user and one city. At the same time, for one user, there will be as many records in this table as the number of cities they have been to.

Here is our junction table:

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

The users table will store only user names, without links:

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

Queries

Let's make a query to fetch users along with their cities. For this, we will need to perform two joins: the first join will attach the junction table to the users, and the second join will attach the cities via the links:

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 Result

The result of our query in PHP will contain each user's name as many times as the number of cities they are linked to:

<?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'],
	];
?>

It would be more convenient to convert such an array and turn it into the following:

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

Let's write the code that performs such a conversion:

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

Practical Tasks

Suppose a product can belong to several categories. Describe the storage structure.

Write a query that retrieves products along with their categories.

Display the retrieved data as a list ul so that in each li, the product name comes first, followed by a colon and then the categories of this product listed separated by commas. Something like this:

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