Как связь таблиц в mysql через php

Как связь таблиц в mysql через php

БлогNot. PHP: связь «многие ко многим» между двумя таблицами

PHP: связь «многие ко многим» между двумя таблицами

Задача будет не слишком отличаться от аналогичной для «оффлайновой» СУБД вроде ACCESS, по крайней мере, мы по-прежнему реализуем связь «многие ко многим» как пару связей «один ко многим».

При использовании связки PHP+MySQL нам достаточно продемонстрировать, как извлекать категории для заданного объекта и объекты, относящиеся к заданной категории, а исключение дублирующихся связей можно сделать просто с помощью дополнительного запроса. Структура БД может быть в простейшем случае такой (выполните этот запрос в PHPMyAdmin после создания пустой базы с именем my ):

Заодно добавлено несколько записей и связей, чтоб было что выводить.

Напишем скрипт, показывающий получение всех объектов категории, всех категорий, имеющихся у объекта, а также добавление/удаление связей.

Примечания к листингу (*)

1. Не забудьте скорректировать данные доступа к БД, заданные константами define

2. Во избежание дублирования кода здесь лучше сделать функцию-обработчик допустимых значений параметров $cat и $obj

3. В нагруженных скриптах с потенциально большими объёмами данных не стоит делать запросов к двум, а тем более, к трём таблицам. Старайтесь всегда обойтись запросами типа «выбрать все объекты данной категории» или «выбрать все категории для данного объекта»

P.S. Ниже показана более современная версия скрипта, рассчитанная на работу с PHP7 и MySQLi вместо MySQL, проверял в XAMPP. Структура самой БД не изменилась, только теперь предполагается, что она создавалась в кодировке Юникода utf-8 (тип сопоставления utf8_general_ci):

• до версии PHP 5.3 включительно СУБД по умолчанию был MySQL;
• с версии 5.4 и в PHP 7.X СУБД называется MySQLi, формат БД не менялся, имена функций для работы с БД – да (везде передаётся идентификатор соединения).

utf-8 с игнорированием регистра символов при сравнении

Скриншот приложения в работе

Связывание через таблицу связи в PHP

Пусть теперь юзер был в разных городах. В этом случае таблица с юзерами могла бы иметь следующий вид:

Понятно, что так хранить данные неправильно — города нужно вынести в отдельную таблицу. Вот она:

Однако, нам нужно сделать так, чтобы каждый юзер мог ссылаться на несколько городов. С помощью двух таблиц это сделать невозможно.

Нам понадобится ввести так называемую , которая будет связывать юзера с его городами.

В каждой записи этой таблицы будет хранится связь между юзером и одним городом. При этом для одного юзера в этой таблице будет столько записей, в скольки городах он был.

Вот наша таблица связи:

Таблица с юзерами будет хранить только имена юзеров, без связей:

Запросы

Давайте сделаем запрос, с помощью которого вытащим юзеров вместе с их городами. Для этого нам понадобится сделать два джоина: первый джоин присоединит к юзерам таблицу связи, а второй джоин по связям присоединит города:

Результат запроса

Результат нашего запроса в PHP будет содержать имя каждого юзера столько раз, со скольки городами он связан:

Удобнее было бы переконвертировать такой массив и превратить его в следующий:

Напишем код, выполняющий такую конвертацию:

Практические задачи

Пусть товар может принадлежать нескольким категориям. Распишите структуру хранения.

Напишите запрос, который достанет товары вместе с их категориями.

Выведите полученные данные в виде списка ul так, чтобы в каждой li вначале стояло имя продукта, а после двоеточия через запятую перечислялись категории этого продукта. Примерно так:

Создание связей между таблицами с помощью phpmyadmin

В этой заметке мы научимся создавать связи между таблицами в базе данных MySQL с помощью phpmyadmin. Если по какой-то причине вы не желаете использовать phpmyadmin, смотрите приведенные ниже SQL-запросы.

Почему же связи удобно держать в самой базе данных? Ведь эту задачу обычно решает так и само приложение? Все дело в ограничениях и действиях при изменении, которые можно наложить на связи.

Например, можно запретить удалять категорию, если с ней связана хотя б одна заметка. Или удалить все заметки, если удалена категория. Или установить NULL в связующее поле. В любом случае, с помощью связей повышается отказоустойчивость и надежность приложения.

Для начала, движок таблиц должен быть InnoDB . Только он поддерживает внешние ключи ( foreign key ). Если у вас таблицы MyISAM , почитайте как их конвертировать в InnoDB .

Для того, чтобы связать таблицы по полям, необходимо сначала добавить в индекс связываемые поля:

В phpmyadmin выбираем таблицу, выбираем режим структуры, выделяем поле, для которого будем делать внешнюю связь и кликаем Индекс.

Обратите внимание на разницу между "Индекс" и "Уникальный". Уникальный индекс можно использовать, например, до поля id, то есть там, где значения не повторяются.

Это же действие можно сделать с помощью SQL-запроса:

Аналогично добавляем индекс (только в моем случае теперь уже уникальный или первичный) для таблицы, на которую ссылаемся, для поля id. Поскольку поле id у меня идентификатор, для него делаем первичный ключ. Уникальный ключ мог бы понадобится для других уникальных полей.

С помощью SQL-запроса:

Теперь осталось только связать таблицы. Для этого кликаем внизу на пункт Связи:

Теперь для доступных полей (а доступны только проиндексированные поля) выбираем связь с внешними таблицами и действия при изменении записей в таблицах:

Через SQL-запрос:

Виды связей в базах данных

MySQL — это реляционная база данных. Это означает, что данные в базе могут быть распределены в нескольких таблицах, и связаны друг с другом с помощью отношений (relation). Отсюда и название — реляционные.

Связи между таблицами происходят с помощью ключей. К примеру, в созданной нами ранее таблице пользователей есть первичный ключ — поле id. Если мы захотим сделать таблицу со статьями и хранить в ней авторов этих статей, то мы можем добавить новый столбец author_id и хранить в нём id пользователей из таблицы users.

Это был лишь один из примеров. Всего же типов подобных связей может быть 3:

• один-к-одному;
• один-ко-многим;
• многие-ко-многим.

Давайте же рассмотрим пример каждой из этих связей.

Один-к-одному

При связи один-к-одному каждой записи таблицы соответствует только одна запись в другой таблице.

Давайте заведем ещё одну таблицу, в которой будет храниться профиль пользователя. В нём можно будет указать информацию о себе и ссылку на профиль в VKontakte.

Добавим для каждого пользователя профиль:

• PhP разработчик для написание модулей под Личный Кабинет 20000₽ — 80000₽
• Backend разработчик (PHP Symfony), Symphony + Vue, БД postgres, знания docker 80000₽ — 150000₽
• Full stack PHP разработчик До 120000₽
• Backend-разработчик (Symfony) 120000₽ — 150000₽
• PHP-разработчик (Symfony) До 250000₽

Посмотрим на получившиеся профили:

Теперь каждой записи из таблицы users соответствует только одна запись из таблицы users_profiles и наоборот.

INNER JOIN

Прежде чем идти дальше и рассматривать другие типы связей, стоит изучить ещё один оператор SQL — INNER JOIN. Он используется для объединения строк из двух и более таблиц, основываясь на отношениях между ними. Для запроса используется следующий синтаксис:

Чтобы получить всех пользователей вместе с их профилями нам нужно выполнить следующий запрос:

Каждая строка из левой таблицы, сопоставляется с каждой строкой из правой таблицы, после этого проверяется условие.

Если мы хотим выбрать только некоторые столбцы, то после оператора SELECT нужно перед именем поля явно указать название таблицы, из которой оно берется:

Алиасы

Согласитесь, в прошлом примере пришлось довольно много букв написать. Чтобы этого избежать, в запросах можно использовать алиасы для имён таблиц. Для этого после имени таблицы можно написать AS alias. Давайте для таблицы users зададим алиас — u, а для таблицы profiles — p. Эти алиасы теперь можно использовать в любой части запроса:

Заметьте, запрос сократился. Писать запрос с использованием алиаса быстрее.

Как уже говорилось выше, алиас можно использовать в любой части запроса, в том числе и в условии WHERE:

Один-ко-многим

При такой связи одной записи в одной таблице соответствует несколько записей в другой. В начале этого урока мы рассмотрели как раз такой пример, когда говорили о добавлении в таблицу с новостями поля author_id. Таким образом, у каждой статьи есть один автор. В то же время у одного автора может быть несколько статей.
Давайте создадим таблицу для статей. Пусть в ней будет идентификатор статьи, её название, текст, и идентификатор автора.

Добавим несколько статей:

Запросим теперь эти записи, чтобы убедиться, что всё ок

Давайте теперь выведем имена статей вместе с авторами. Для этого снова воспользуемся оператором INNER JOIN.

Как видим, у Ивана две статьи, и ещё одна у Ольги.

Если бы мы захотели на странице со статьей выводить рядом с автором краткую информацию о нем, нам нужно было бы сделать ещё один JOIN на табличку profiles.

LEFT JOIN

Помимо INNER JOIN, есть ещё несколько операторов класса JOIN. Один из самых частоиспользуемых — LEFT JOIN. Он позволяет сделать запрос к двум таблицам, между которыми есть связь, и при этом для одной из таблиц вернуть записи, даже если они не соответствуют записям в другой таблице.
Как например, если бы мы хотели вывести не только пользователей, у которых есть статьи, но и тех, кто "халтурит" 🙂

Давайте для начала сделаем запрос с использованием INNER JOIN, который выведет пользователей и написанные ими статьи:

Теперь заменим INNER JOIN на LEFT JOIN:

Видите, вывелись записи из левой таблицы (users), которым не соответствует при этом ни одна запись из правой таблицы (articles).

Многие-ко-многим

Такая связь возникает, когда множество строк одной таблицы соответствуют множеству строк другой таблицы. Чтобы связать их между собой, нужно создать третью таблицу, создав с каждой из первых двух связь один-ко-многим.

В качестве примера такой связи можно привести рубрики статей. Каждая статья может иметь несколько рубрик. И одновременно с этим, каждая рубрика может содержать в себе несколько статей. Давайте добавим таблицу для рубрик.

И сразу добавим в неё несколько рубрик.

Проверим, что они добавились.

Теперь нам нужно добавить ещё одну таблицу, в которой будут храниться связи между article.id и category.id. Создаём:

Обратите внимание на составной первичный ключ. Здесь нам требуется, чтобы именно пара (id_статьи — id_рубрики) была уникальной. А сами по себе значения в отдельных колонок могут повторяться.