Vinculación a través de tabla de relación en PHP

Supongamos ahora que el usuario ha estado en diferentes ciudades. En este caso, la tabla de usuarios podría tener la siguiente apariencia:

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

Está claro que almacenar datos así es incorrecto - las ciudades deben llevarse a una tabla separada. Aquí está:

cities
id	name
1	city1
2	city2
3	city3

Sin embargo, necesitamos hacer que cada usuario pueda hacer referencia a varias ciudades. Con dos tablas esto es imposible de hacer.

Necesitaremos introducir una llamada tabla de relación que vinculará al usuario con sus ciudades.

En cada registro de esta tabla se almacenará un vínculo entre un usuario y una ciudad. Al mismo tiempo, para un usuario habrá tantos registros en esta tabla como ciudades en las que haya estado.

Aquí está nuestra tabla de relación:

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

La tabla de usuarios almacenará solo los nombres de usuario, sin vínculos:

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

Consultas

Hagamos una consulta con la cual obtengamos los usuarios junto con sus ciudades. Para esto necesitaremos hacer dos joins: el primer join adjuntará la tabla de relación a los usuarios, y el segundo join adjuntará las ciudades a través de los vínculos:

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

Resultado de la consulta

El resultado de nuestra consulta en PHP contendrá el nombre de cada usuario tantas veces como ciudades tenga asociadas:

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

Sería más conveniente convertir tal array y transformarlo en el siguiente:

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

Escribamos el código que realiza dicha conversión:

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

Tareas prácticas

Supongamos que un producto puede pertenecer a varias categorías. Describa la estructura de almacenamiento.

Escriba una consulta que obtenga los productos junto con sus categorías.

Muestre los datos obtenidos en forma de lista ul de modo que en cada li al principio esté el nombre del producto, y después de dos puntos, se enumeren las categorías de este producto separadas por comas. Algo así:

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