MySQL связь многие ко многим

[ritor]

есть массив цифр==id из таблицы в mysql. (массив получаю из другой таблицы. там он в одном столбце, через ";" перечислен)
надо получить нечто вроде вот такого:
{a href="ID"}Name(из 2й таблицы,соотвествие ID&Name){/a}, {a href="ID"}Name(из 2й таблицы,соотвествие ID&Name){/a}, ...

завис пока на этом..

$sql_taxonomy = 'SELECT * FROM `taxonomy` ORDER BY `t_id` DESC';
$rez_taxonomy = mysql_query($sql_taxonomy,$db);


while($row_taxonomy = mysql_fetch_array($rez_taxonomy))
{
$t_id = $row_taxonomy['t_id'];
$t_name = $row_taxonomy['t_name'];
$t_date = TimestampToDatetime($row_taxonomy['t_date']);
$t_chleni = $row_taxonomy['t_chleni'];
$t_uchastniki = $row_taxonomy['t_uchastniki'];
//$t_text = $row_taxonomy['t_text'];

$sql_ppl = 'SELECT users.user_id, users.user_nick, taxonomy.t_chleni FROM `taxonomy`
INNER JOIN users ON (users.user_id = taxonomy.t_chleni)';
$rez_ppl = mysql_query($sql_ppl,$db);
while($row_ppl = mysql_fetch_array($rez_ppl))
{
$t_chleni = $row_ppl['t_chleni'];
$user_id = $row_ppl['user_id'];
$user_nick = $row_ppl['user_nick'];

$field_map = array('1' => '');/..думал через массив пропустить, но завис
}

echo'...не суть важно';
}


размышлял-хотел один запросом сделать, но вставл вопрос-как сопоставить массив id готовый($t_chleni) со значениями ID в виде $t_id

@темы: Алгоритмы, PHP, Вопросы

Comments

[lugavchik]

Как-то так или задание я не понял.
// $array - Массив где данные через ;
$table=explode(';',$array);
$SQL='... WHERE id in ("'.implode('", "',$table).'") ...';
...
while ($row=mysql_fetch_array($result)){
echo '<a href="http://.$row[id].">'.$row['name'].'</a>';
}

лучше приведи 2-3 строчки для каждой таблицы, чтобы было понятно, как это можно быстро связать и одним запросом вытащить

[ritor]

тестовые для примеру себе создавал.

CREATE TABLE `taxonomy` (
`t_id` tinyint(4) NOT NULL auto_increment,
`t_name` varchar(50) NOT NULL default '',
`t_date` datetime NOT NULL default '0000-00-00 00:00:00',
`t_chleni` text NOT NULL,
`t_uchastniki` text NOT NULL,
`t_text` text NOT NULL,
UNIQUE KEY `t_id` (`t_id`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 COMMENT='Восхождения' AUTO_INCREMENT=3 ;

--
-- Дамп данных таблицы `taxonomy`
--

INSERT IGNORE INTO `taxonomy` VALUES (1, 'Западный Кавказ, Архыз', '2006-00-00 00:00:00', '1;2;3;4;', 'Участник1, Участник2, Участник3.', 'Подходы.\r\n\r\nОт бревна 50 метров в право');
INSERT IGNORE INTO `taxonomy` VALUES (2, 'Архыз2', '2005-00-00 00:00:00', '1;4;', 'Участник1, Участник2, Участник3.', 'Подходы2');

CREATE TABLE `users` (
`user_id` tinyint(4) NOT NULL auto_increment,
`user_nick` varchar(25) NOT NULL default '',
`user_sess` text NOT NULL,
`user_visits` datetime NOT NULL default '0000-00-00 00:00:00',
`user_f` varchar(30) NOT NULL default '',
`user_i` varchar(30) NOT NULL default '',
`user_o` varchar(30) NOT NULL default '',
`user_kvalificatsiya` text NOT NULL,
`user_sex` tinyint(1) NOT NULL default '0',
`user_from` varchar(30) NOT NULL default '',
`user_datereg` datetime NOT NULL default '0000-00-00 00:00:00',
`user_about` text NOT NULL,
`user_diary_title` varchar(50) NOT NULL default '',
UNIQUE KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 COMMENT='пользователи' AUTO_INCREMENT=3 ;

--
-- Дамп данных таблицы `users`
--

INSERT IGNORE INTO `users` VALUES (1, 'nick1', '', '2007-12-05 00:00:00', 'Фамилия1', 'Имя1', 'Отчество1', 'лузер неопытный (квалификация)', 1, 'из ниоткуда', '2007-12-02 00:00:00', 'о себе1', '');
INSERT IGNORE INTO `users` VALUES (2, 'ник2', '', '0000-00-00 00:00:00', 'Фамилиё2', 'Имечко2', 'Отчество2', 'значёк типа ^_^', 2, 'с гор спустился', '0000-00-00 00:00:00', 'о себе2', '');

[[php]]

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


taxonomy / таблица связи / таблица юзеров (users)

id___________ id________________id
name_________id_user___________fio
date__________id_taxonomy_______..........
....

Это для таблицы taxonomy. Тут получится , что для кажого taxonomy есть несколько записей из таблицы юзеров , а для каждого юзера - несколько записей из taxonomy ...
http://sql.ru/forum/actualthread.aspx?bid=36&tid=215946&pg=1 - первое попавшееся , про нереальность хранить массивы в полях

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

Если же хранить массивы по какой-то причине легче - то в MySQL нету рекурсий и с произвоьными длинами массивов в 1 запрос не выцепишь все данные (хотя могу и ошибаться насчет рекурсий, но это уже копать в сторону ф-ций работы со строками)

[ritor]

[php]
я не знаю, как давать заданию программисту.

многие ко многим - похоже то самое, что необходимо, но вышеуказаную запись я не понял taxonomy / таблица связи / таблица юзеров (users)

массив в полях:
на сайте выбираются юзерс, (например галочками-чеками), которых надо потом как нить компактно сохранить куда либо. я выбрал вариант сохранения ихнего id в виде строки в таблицу taxonomy.

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

или где то неправильная логика?

[[php]]

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

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

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

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

Для этого вводим дополнительную таблицу `link` - таблицу связей. Она будеt состоять из 3х полей - уникальный id , id главной таблицы и id user'a .

Чтобы вытащить юзеров для записи из главной таблицы : SELECT * FROM users JOIN link ON user.id = link.user_id JOIN main on main.id = link.main_id.

Примерно такая логика.)

[ritor]

юзеры вроде и так хранятся в отдельном виде (уникально-неповторяющиеся).

вопрос в том, как хранить отношение (кто именно из юзеров), к main таблице?

3х полей - уникальный id , id главной таблицы и id user'a .

кроме первого, вторые как создавать? -реферы, или?



попытаюсь ещё раз:
main:
m_id | m_title | m_.....(неважно)|

users:
u_id | u_nick | u_.....(неважно)|

нужна связь многих ко многим.
что куда добавить(столбцы\тип данных)?

(каждый пользователь имеет несколько ссылок на main, а каждая строка main имеет нескольких пользователей).

[[php]]

добавлять нужно -
в главную поле integer id юзера (вместо поля с массивами)

+ добавить таблицу из трех полей.

main
1 Запись1
2 Запись2
3 Запись3

link ( поля id , id main , id user'a)
1 1 2
2 1 3
3 1 4

users
1 user1
2 user2
3 user3
4 user4

т.е. по таблице связи видим, что для первой записи из main (id=1) есть 4 записи из таблицы юзеров с id 2 , 3 и 4.
офиса нет на компе, бы нарисовал.....

[[php]]

link ( поля id , id main , id user'a)
1 1 2
2 1 3
3 1 4
4 2 1
5 2 2
6 2 3
7 2 4

а так - для первой записи 3 юзера, для второй - 4 юзера, причем те же самые по второму разу.
наоборот - можно узнать, в каких записях есть именно 2ой юзер.

[ritor]

с доп таблицей ясно.

в главную поле integer id юзера (вместо поля с массивами)

массив удалить-понял.
а интегер_ид_юзер - ссылку на таблицу users?

или банально - при создании записи в main делать инсёты(по количеству юзеров) в link?

и никаких REFERENCES тут и близко не нужно?

[[php]]

для юзеров достаточно их id , кстати и для главной тоже - лишнее поле id_user . Можно сразу по idшникам записей заносить. ) мой косяк

[ritor]

для юзеров достаточно их id

u_id (например) и есть -первичный, просто что бы потом не путаться в джоинах. да и читабельнее.

[ritor]

угу, создал по приведеному ранее примеру запрос, по этим таблицам.
запихивать причётся инсётами и никаких ссылок в таблицах)

SELECT * FROM

users JOIN

link ON
users.user_id = link.user_id JOIN

taxonomy ON
main.m_id = link.m_id WHERE

main.m_id=1 //условие сортировки, собсно.

[[php]]

уфф)

[ritor]

[php]
спасибо)

[Гость]

Всем привет, есть проблема, мне нужно связать фильмы и жанры отношением многие ко многим.
Делал все сам, результат получился такой же примерно к чему тут и пришли. Только одно НО.

Фильм может содержать несколько жанров, вот мне нужно построить список фильмов, где выводится название фильма и список его жанров, как это сделать одним запросом?

Пример: добавлено 3 фильма, каждый из них имеет 2 жанра, тот SQL запрос, который описан выше вернет 6 записей, а мне нужно что бы он вернул 3 записи, так как в запросе участвует еще LIMIT, для разброса списка фильмов по страницам. Проблема в том, что если я поставлю LIMIT 2 (выбор 2-х из 3-х фильмов), то вернется 2 записи, которые на самом деле относятся только к одному фильму, который имеет 2 жанра )

Вот такая ситуация, думаю я более понятно объяснил. Нужна помощь. Заранее спасибо.

[ritor]

'SELECT * (фильма) WHERE `janr`='.$1th.' OR `janr`='.$2th.' LIMIT 5'; /*к примеру-по 5 на страницу, есть код бьющий\создающий запросы - на страницу, самописное извращение, но работает, могу поискать*/

а так? т.е. селектом-выбирать именно то что нужно, фильмы т.е., а условия-фильтры, через ИЛИ поставить. т.е. если фильм несколько раз в фильтры попадает, то всё равно один раз он вылезет - условие то (ну там объёдинения баз допишешь, я с ходу не могу их-непривык ещё).

pS: я как раз таки по фильмам(точнее аниме) дела..делаю, уже который год, лень просто, этот этап вроде прошел-надо будет проверить, так сейчас там собсно запрос уже выглядит как куча $переменных, где из "живых" - только само слово Селект, да названия баз откуда данные вытаскиваются.
а остальное -$WHERE, $LIMIT, $DESK... вроде всё.. может чего то ещё, непомню) (сейчас там надо юзабилити доделывать, -тот же аякс изучать.. эх.. -_-, ленивое мя))

[Гость]

'SELECT * (фильма) WHERE `janr`='.$1th.' OR `janr`='.$2th.' LIMIT 5'; /*к примеру-по 5 на страницу, есть код бьющий\создающий запросы - на страницу, самописное извращение, но работает, могу поискать*/

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

Вот попробую пояснить немного больше. Коряво думаю, предпросмотра нет )

Структура:
-------------------------------------------------------------
films filmgenres genres
+-----------+ +-------------+ +---------------+
|film_id |-+ |fg_id | +-|genre_id |
|film_name| +-|fg_filmid | | |genre_name |
+-----------+ |fg_genreid |-+ +---------------+
+-------------+
Данные:
-------------------------------------------------------------
1 Film1 1 1 1 1 Комедия
2 Film2 2 1 3 2 Фантасика
3 Film3 3 2 2 3 Боевик
... 4 2 3 4 Драма
5 3 4 5 Фэнтези
6 3 6 6 Триллер
... ...

[Гость]

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

[ritor]

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

запрос конечно длинный получается, зато один.

[ritor]

ps: если выдаль sql дамп-с созданием таблиц, посмотрю "вжвую")

[[php]]

SELECT * FROM film WHERE janre IN ( список значений через запятую)
вроде есть такая конструкция, сейчас покопаю ещё....

[[php]]

SELECT *
FROM Блюда
WHERE Основа IN (Яйца Крупа Овощи);

Результат:БЛ Блюдо В Основа Выход Труд
1 Салат летний З Овощи 200. 3
3 Салат витаминный З Овощи 200. 4
16 Драчена Г Яйца 180. 4
17 Морковь с рисом Г Овощи 260. 3
19 Омлет с луком Г Яйца 200. 5
20 Каша рисовая Г Крупа 210. 4
21 Пудинг рисовый Г Крупа 160. 6
23 Помидоры с луком Г Овощи 260. 4


Рассмотренная форма IN является в действительности просто краткой записью последовательности отдельных сравнений, соединенных операторами OR. Предыдущее предложение эквивалентно такому:
SELECT *
FROM Блюда
WHERE Основа=Яйца OR Основа=Крупа OR Основа=Овощи;

Можно задать и NOT IN (не принадлежит), а также возможность использования IN (NOT IN) с подзапросом

[[php]]

я к тому, что сгенерировать строчку легче в IN ('значение1' , 'значение2') чем писать кучку OR с проверкой, является ли этот OR последним тд)

[[php]]

expr IN (value,...)

Возвращает 1, если выражение expr равно любой величине из списка IN, иначе - 0. Если все величины - константы, то они оцениваются в соответствии с типом выражения expr и сортируются. Поиск элемента в этом случае производится методом логического поиска. Это означает, что функция IN является очень быстрой, если список значений IN состоит полностью из констант. Если expr является зависимым от регистра строковым выражением, то сравнение строк производится с учетом регистра:

mysql> SELECT 2 IN (0,3,5,'wefwf');
-> 0
mysql> SELECT 'wefwf' IN (0,3,5,'wefwf');
-> 1


Начиная с 4.1 (в соответствии со стандартом SQL-99), IN возвращает NULL не только если выражение в левой части является NULL, но также если не найдено соответствия в списке и одно из выражений в списке является величиной NULL.

[Гость]

Уговорили, держите дамп )

CREATE TABLE `films` (
`film_id` mediumint(8) unsigned NOT NULL auto_increment,
`film_name` varchar(30) NOT NULL,
PRIMARY KEY (`film_id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=cp1251;

INSERT IGNORE INTO `films` (`film_id`, `film_name`) VALUES (1, 'Film1'), (2, 'Film2'), (3, 'Film3');

-- --------------------------------------------------------

CREATE TABLE `filmgenres` (
`fg_id` mediumint(8) unsigned NOT NULL auto_increment,
`fg_filmid` mediumint(8) unsigned NOT NULL,
`fg_genreid` mediumint(8) unsigned NOT NULL,
PRIMARY KEY (`fg_id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=cp1251;

INSERT IGNORE INTO `filmgenres` (`fg_id`, `fg_filmid`, `fg_genreid`) VALUES (1, 1, 1), (2, 1, 3), (3, 2, 2), (4, 2, 3), (5, 3, 4), (6, 3, 6);

-- --------------------------------------------------------

CREATE TABLE `genres` (
`genre_id` smallint(4) NOT NULL auto_increment,
`genre_name` varchar(30) NOT NULL,
PRIMARY KEY (`genre_id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=cp1251;

INSERT IGNORE INTO `genres` (`genre_id`, `genre_name`) VALUES (1, 'Комедия'), (2, 'Фантасика'), (3, 'Боевик'), (4, 'Драма'), (5, 'Фэнтези'), (6, 'Триллер');

[Гость]

Все что выше было написано, все это я знаю, но либо немогу правильно применить, либо просто так нельзя сделать, а именно:

Запросы:
----------------------------------------------
1. SELECT film_name, genre_name FROM films JOIN filmgenres ON films.film_id=filmgenres.fg_filmid JOIN genres ON filmgenres.fg_genreid=genres.genre_id WHERE film_id = 1
Запрос выбирает поля из связных таблиц для фильма с id = 1, но при этом результат прийдет массивом из двух элементов, которые будут отличаться только жанром фильма (так как их два для фильма с id = 1)
Результат:

Array(
[0] => Array([film_name] => Film1, [genre_name] => Комедия),
[1] => Array([film_name] => Film1, [genre_name] => Боевик)
)

2. SELECT film_name, genre_name FROM films JOIN filmgenres ON films.film_id=filmgenres.fg_filmid JOIN genres ON filmgenres.fg_genreid=genres.genre_id
Запрос выбирает поля из связных таблиц для всех фильмов, но при этом результат будет массивом из 6-ти элементов (так как каждый из 3-х фильмов имеет по два жанра)
Результат:

Array(
[0] => Array([film_name] => Film1, [genre_name] => Комедия),
[1] => Array([film_name] => Film1, [genre_name] => Боевик),
[2] => Array([film_name] => Film2, [genre_name] => Фантасика),
[3] => Array([film_name] => Film2, [genre_name] => Боевик),
[4] => Array([film_name] => Film3, [genre_name] => Драма),
[5] => Array([film_name] => Film3, [genre_name] => Триллер)
)

[Гость]

3. SELECT film_name, genre_name FROM films JOIN filmgenres ON films.film_id=filmgenres.fg_filmid JOIN genres ON filmgenres.fg_genreid=genres.genre_id LIMIT 2
Запрос выбирает все поля из связных таблиц для одного фильма (хотя необходимо для 2-х), и при этом результат будет массивом из 2-х элементов (так как ограничили через LIMIT)

Array(
[0] => Array([film_name] => Film1, [genre_name] => Комедия),
[1] => Array([film_name] => Film1, [genre_name] => Боевик)
)

Вопрос: как построить запрос (и можно ли?) что бы им забрать поля по 2-м фильмам и их жанры?

Необходимо получить что-то типа того, что ниже, но LIMIT ограничивает в 2-е возвращаемые записи:

array(
[0] => array('film_name' => 'Film1', 'genres' => array('Комедия', 'Боевик')),
[1] => array('film_name' => 'Film2', 'genres' => array('Фантасика', 'Боевик'))
);

Или

array(
[0] => array('film_name' => 'Film1', 'genre' => 'Комедия'),
[1] => array('film_name' => 'Film1', 'genre' => 'Боевик'),
[2] => array('film_name' => 'Film2', 'genre' => 'Фантасика'),
[3] => array('film_name' => 'Film2', 'genre' => 'Боевик')
);

[[php]]

Вообще невозможно в 1 строке ответа селекта получить информацию по 2м записям, можно в одном Запросе получить инфу по неск. записям и уже в PHP обработать. Полезн фишка DISTINCT

[[php]]

для тебя лучший вариант - в php делать запрос на фильм, потом к нему запрос на жанры, чтобы не мучиться)

[Гость]

Не, так не покатит, так как и так сильно много запросов в системе, а тут еще 30 фильмов отбирать и потом к каждому искать жанры, сильно много запросов будет.

Вот я думаю был бы идеальный запрос, но он не работает, так как работает ограничение на подзапрос в одно значение одного поля.
SELECT film_name, film_id AS fid, CONCAT_WS(',',(SELECT genre_name FROM filmgenres JOIN genres ON filmgenres.fg_genreid=genres.genre_id WHERE fg_filmid=fid LIMIT 1)) AS genres FROM films LIMIT 30

Но то что ты сказал мне кажется лучше сделать через IN.

Находим все необходимые айдишки фильмов
SELECT film_id FROM films LIMIT 2

А затем в другой запрос вписываем в IN результат предыдущего запроса обработанного через implode, который успешно нам заменит LIMIT
SELECT film_id, film_name, genre_name FROM films JOIN filmgenres ON films.film_id=filmgenres.fg_filmid JOIN genres ON filmgenres.fg_genreid=genres.genre_id WHERE film_id IN (implode(',', 'array(результат с предыдущего запроса)'))

Вообщем не знаю как лучше сделать, как оптимальней. Может есть еще какие идеи? )