Связанный вывод с двух таблиц?
Здравствуйте, натолкните на мысль либо дайте пожалуйста пример или ссылку на мануал, как реализовать такой функционал:
MySQL:
У нас есть две таблицы:
category(категории, имеет столбцы — id и name)
order(статьи, имеет столбцы — id, name и category_id)
Хочу реализовать вот такой вывод:
К примеру в таблице category есть три записи(3 категории)
id — 1, name — Природа
id — 2, name — Еда
id — 3, name — Спорт
А в таблице order есть тоже 3 записи (3 статьи)
id — 1, name — В мире животных, category_id — 1
id — 2, name — Как сделать вкусный салат, category_id — 2
id — 3, name — Методы похудения, category_id — 3
Нужно чтоб выводилось на одной странице вот так
1) Природа
— В мире животных
2) Еда
— Как сделать вкусный салат
3) Спорт
— Методы похудения
—————————
То есть чтоб на одной странице выводились все категории и статьи к которым они привязаны.
Заранее огромное спасибо!
https://www.w3schools.com/sql/sql_join_inner.asp
Использую таблицы caregory и orders. Связываю их по category_id
SELECT category.name, orders.name FROM category INNER JOIN orders ON category.category_id=orders.category_id ORDER BY category.name;Развернул по уроку, обойтись оказывается спокойно можно одной таблицей и не мудрить.
-- phpMyAdmin SQL Dump -- version 4.6.5.2 -- https://www.phpmyadmin.net/ -- -- Хост: 127.0.0.1:3306 -- Время создания: Фев 27 2017 г., 22:58 -- Версия сервера: 5.7.16 -- Версия PHP: 7.1.0 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; -- -- База данных: `test` -- -- -------------------------------------------------------- -- -- Структура таблицы `categories` -- CREATE TABLE `categories` ( `id` int(10) UNSIGNED NOT NULL, `title` varchar(255) NOT NULL, `parent` int(10) UNSIGNED NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -- Дамп данных таблицы `categories` -- INSERT INTO `categories` (`id`, `title`, `parent`) VALUES (1, 'Природа', 0), (2, 'Еда', 0), (3, 'Спорт', 0), (4, 'В мире животных', 1), (5, 'Как сделать вкусный салат', 2), (6, 'Свое', 2), (20, 'Методы похудения', 3); -- -- Индексы сохранённых таблиц -- -- -- Индексы таблицы `categories` -- ALTER TABLE `categories` ADD PRIMARY KEY (`id`); -- -- AUTO_INCREMENT для сохранённых таблиц -- -- -- AUTO_INCREMENT для таблицы `categories` -- ALTER TABLE `categories` MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;Связывание через таблицу связи в PHP
Пусть теперь юзер был в разных городах. В этом случае таблица с юзерами могла бы иметь следующий вид:
| id | name | city |
|---|---|---|
| 1 | user1 | city1, city2, city3 |
| 2 | user2 | city1, city2 |
| 3 | user3 | city2, city3 |
| 4 | user4 | city1 |
Понятно, что так хранить данные неправильно — города нужно вынести в отдельную таблицу. Вот она:
Однако, нам нужно сделать так, чтобы каждый юзер мог ссылаться на несколько городов. С помощью двух таблиц это сделать невозможно.
Нам понадобится ввести так называемую , которая будет связывать юзера с его городами.
В каждой записи этой таблицы будет хранится связь между юзером и одним городом. При этом для одного юзера в этой таблице будет столько записей, в скольки городах он был.
Таблица с юзерами будет хранить только имена юзеров, без связей:
Запросы
Давайте сделаем запрос, с помощью которого вытащим юзеров вместе с их городами. Для этого нам понадобится сделать два джоина: первый джоин присоединит к юзерам таблицу связи, а второй джоин по связям присоединит города:
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
Результат запроса
Результат нашего запроса в PHP будет содержать имя каждого юзера столько раз, со скольки городами он связан:
‘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’], ]; ?>
Удобнее было бы переконвертировать такой массив и превратить его в следующий:
Напишем код, выполняющий такую конвертацию:
Практические задачи
Пусть товар может принадлежать нескольким категориям. Распишите структуру хранения.
Напишите запрос, который достанет товары вместе с их категориями.
Выведите полученные данные в виде списка ul так, чтобы в каждой li вначале стояло имя продукта, а после двоеточия через запятую перечислялись категории этого продукта. Примерно так:
- product1: category1, category2, category3
- product2: category1, category3
- product3: category1
Связи между таблицами. JOIN. Агрегатные функции
Есть таблица workers и departments. Попробуем выбрать работников с их отделами.
SELECT workers.name as w_name, departments.name as d_name FROM workers, departments WHERE workers.dep_id = departments.id Теперь в PHP мы передаем таблицу с двумя полями w_name и d_name.
Проблема такого подхода, является то, что в начале из двух таблиц генерируется таблица, содержащая все возможные комбинации записей первой и второй таблиц. Если в первой таблице N записей, а во второй M, то в промежуточных вычислениях мы получаем таблицу с M*N записей. Если мы работаем с небольшими таблицами порядка 10000 строчек, то получим таблицу 100 миллионов записей. В дальнейшем с помощью WHERE мы вернем ее к 10K записей. Такой подход нерационален как по памяти, так и по времени.
SELECT workers.name as w_name, departments.name as d_name FROM workers JOIN departments WHERE workers.dep_id = departments.id JOIN объединит две таблицы, но при такой записи мы не получим выигрыша. Мы по прежнему получаем все комбинации в таблице, а потом их фильтруем
К счастью JOIN позволяет объединять таблицы сразу по условию
SELECT workers.name as w_name, departments.name as d_name FROM workers JOIN departments ON workers.dep_id = departments.id Попробуем сократить наш запрос, используя псевдонимы на этапе таблиц
SELECT w.name as w_name, d.name as d_name FROM workers as w JOIN departments as d ON w.dep_id = d.id Наш запрос имеет один недостаток – если работник не находится в каком-либо отделе, то он не попадет в результирующую выборку.
Чтобы исправить эту ситуацию, воспользуемся LEFT JOIN
SELECT w.name as w_name, d.name as d_name FROM workers as w LEFT JOIN departments as d ON w.dep_id = d.id Отличие LEFT JOIN от JOIN состоит в том, что записи первой (левой) таблицы берутся в любом случае. Если им не найдены соответствующие поля с таблицы справа, то вместо них подставляется NULL
RIGHT JOIN аналогичен только для таблицы справа
Связи между таблицами
users — profile (один к одному)
workers – departments (один ко многим)
posts – hashtags (многие ко многим)
На практике связь «многие ко многим» реализуется через промежуточную таблицу
Полезное чтиво:
- Создать таблицу departments(id,name) с названиями отделов, добавить поле dep_id в workers. Выбрать всех работников с названием отделов
- SpeedDating. Есть таблица с именами девушек, есть таблица с именами парней. Есть таблица знакомств — кто с кем познакомился. Вывести для каждой девушки список парней, которые с ней познакомились.
results matching » «
No results matching » «
Связка двух таблиц php
БлогNot. PHP: связь «многие ко многим» между двумя таблицами
PHP: связь «многие ко многим» между двумя таблицами
Задача будет не слишком отличаться от аналогичной для «оффлайновой» СУБД вроде ACCESS, по крайней мере, мы по-прежнему реализуем связь «многие ко многим» как пару связей «один ко многим».
При использовании связки PHP+MySQL нам достаточно продемонстрировать, как извлекать категории для заданного объекта и объекты, относящиеся к заданной категории, а исключение дублирующихся связей можно сделать просто с помощью дополнительного запроса. Структура БД может быть в простейшем случае такой (выполните этот запрос в PHPMyAdmin после создания пустой базы с именем my ):
create table if not exists categories ( id int primary key auto_increment, category varchar(32) ); create table if not exists objects ( id int primary key auto_increment, object varchar(32) ); create table if not exists links ( id_category int, id_object int ); insert into categories (id,category) values (1,'Notes'), (2,'Balls'), (3,'Rating'); insert into objects (id,object) values (1,'Admin'), (2,'User'); insert into links (id_category,id_object) values (1,1), (2,1), (2,2), (3,2);
Заодно добавлено несколько записей и связей, чтоб было что выводить.
Напишем скрипт, показывающий получение всех объектов категории, всех категорий, имеющихся у объекта, а также добавление/удаление связей.
Передан несуществующий id категории скорректировано на "пусто"'; > > if (!empty($obj)) < $result2 = mysql_fetch_array(mysql_query('select id from objects where '; echo '
Передан несуществующий id объекта скорректировано на "пусто"'; > > > if (!empty($cat) && !empty($obj)) < $sql = 'select * from links where id_category='.$cat.' and id_object='.$obj; $result = mysql_query ($sql); $n = mysql_num_rows ($result); switch ($action) < case 'add': if ($n==0) < $sql = 'insert into links (id_category,id_object) values ('.$cat.','.$obj.')'; $result = mysql_query ($sql); echo '
Запрос на добавление выполнен'; > else echo '
Связь уже существует, повторно не добавляем'; break; case 'del': if ($n>0) < $sql = 'delete from links where id_category='.$cat.' and id_object='.$obj; $result = mysql_query ($sql); echo '
Запрос на удаление выполнен'; > else echo '
Связь не задана, нечего удалять'; break; default: echo '
'.($n==0?'Не связано':'Связано'); break; > > else < $action='show'; if (!empty($cat) && empty($obj)) $sql = 'select * from categories where order by id asc'; else if (empty($cat) && !empty($obj)) $sql = 'select * from objects where order by id asc'; else $sql = //(*) 3 'select * from links,categories,objects where links.id_category=categories.id && links.id_object=objects.id'; $result = mysql_query ($sql); if ($result) < $n = mysql_num_rows ($result); if ($n>0) while ($row = mysql_fetch_assoc ($result)) < echo '
'; foreach ($row as $key=>$val) echo $key.'=>'.$val.' '; > else echo '
Результат запроса пуст (выбрано 0 строк)'; > else echo '
Ошибка SQL: '.mysql_error(); > echo ' '; ?> Примечания к листингу (*)
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 с игнорированием регистра символов при сравнении
Передан несуществующий id категории скорректировано на "пусто"'; > > if (!empty($obj)) < $sql = 'select id from objects where $result2 = mysqli_fetch_array(mysqli_query($mysql_id,$sql)); if (!$result2) < $obj=''; echo ''; ?>
Передан несуществующий id объекта скорректировано на "пусто"'; > > > if (!empty($cat) and !empty($obj)) < $sql = 'select * from links where id_category='.$cat.' and id_object='.$obj; $result = mysqli_query ($mysql_id,$sql); $n = mysqli_num_rows ($result); switch ($action) < case 'add': //Запрос на добавление if ($n == 0) < $sql = 'insert into links (id_category,id_object) values ('.$cat.','.$obj.')'; $result = mysqli_query ($mysql_id, $sql); echo '
Запрос на добавление выполнен'; > else echo '
Связь уже существует, повторно не добавляем'; break; case 'del': //Запрос на удаление if ($n > 0) < $sql = 'delete from links where id_category='.$cat.' and id_object='.$obj; $result = mysqli_query ($mysql_id, $sql); echo '
Запрос на удаление выполнен'; > else echo '
Связь не задана, нечего удалять'; break; default: echo '
'.($n==0 ? 'Не связано' : 'Связано' ); break; > > else < //Подбираем запрос в зависимости от того, какой id передан: $action='show'; if (!empty($cat) and empty($obj)) //Выбрать по категории $sql = 'select * from categories where order by id asc'; else if (empty($cat) and !empty($obj)) //Выбрать по объекту $sql = 'select * from objects where order by id asc'; else $sql = //Выбрать всё (*) 3 'select * from links,categories,objects where '. 'links.id_category=categories.id && links.id_object=objects.id'; $result = mysqli_query ($mysql_id,$sql); if ($result) < $n = mysqli_num_rows ($result); if ($n >0) while ($row = mysqli_fetch_assoc ($result)) < echo '
'; foreach ($row as $key=>$val) echo $key.'=>'.$val.' '; //вывод информации > else echo '
Результат запроса пуст (выбрано 0 строк)'; > else echo '
Ошибка SQL: '.mysqli_error($mysql_id); > //Форма для интерфейса с БД echo '
Скриншот приложения в работе
20.01.2015, 09:17 [14264 просмотра]