Vinculação através de tabela de ligação em PHP

Suponha que agora um usuário tenha estado em cidades diferentes. Neste caso, a tabela de usuários poderia ter a seguinte aparência:

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

É claro que armazenar dados assim está incorreto - as cidades precisam ser movidas para uma tabela separada. Aqui está:

cities
id	name
1	city1
2	city2
3	city3

No entanto, precisamos fazer com que cada usuário possa referenciar várias cidades. Isso é impossível de fazer usando apenas duas tabelas.

Precisaremos introduzir uma chamada tabela de ligação, que irá vincular o usuário às suas cidades.

Cada registro nesta tabela armazenará um vínculo entre um usuário e uma cidade. Ao mesmo tempo, para um usuário, haverá tantos registros nesta tabela quantas cidades ele esteve.

Aqui está nossa tabela de ligação:

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

A tabela de usuários armazenará apenas os nomes dos usuários, sem vínculos:

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

Consultas

Vamos fazer uma consulta que buscará os usuários junto com suas cidades. Para isso, precisaremos fazer dois joins: o primeiro join juntará a tabela de ligação aos usuários, e o segundo join, através dos vínculos, juntará as cidades:

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 da consulta

O resultado da nossa consulta em PHP conterá o nome de cada usuário quantas vezes quantas cidades ele estiver vinculado:

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

Seria mais conveniente converter tal array e transformá-lo no seguinte:

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

Vamos escrever o código que realiza essa conversão:

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

Tarefas práticas

Suponha que um produto possa pertencer a várias categorias. Descreva a estrutura de armazenamento.

Escreva uma consulta que buscará os produtos junto com suas categorias.

Exiba os dados obtidos na forma de uma lista ul de modo que em cada li no início esteja o nome do produto, e após dois-pontos, separadas por vírgula, sejam listadas as categorias desse produto. Mais ou menos assim:

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