Создание SQL запроса для поиска для одной колонки?

Question

[Денис]

Здравствуйте.

Подскажите пожалуйста как решить проблему, а то все не как не пойму как сделать.

Есть таблица с игровыми серверами

CREATE TABLE  `servers` (
  `server_id` mediumint(5) unsigned NOT NULL,
  `server_game` varchar(100) DEFAULT NULL,
  `game_mod` text  (сюда пишутся ид модов, моды в другой таблице)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

В ней есть столбцы интересующие нас: server_game и game_mod

На сайте есть поиск по игровым серверам.

Человек может выбрать игру любую, за это отвечает колонка server_game(тут находится тип игры)

В колонку server_game_mod помещаются моды сервера, но модов может быть и 50 и 100 (в таблице пишется ид мода)

Понятно что можно сделать WHERE server_game='ТИП ИГРЫ' AND game_mod='Моды игры'

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

Наглядно:
Человек ищет сервер minecraft, и выбрал моды "с PvP". (Все моды хранятся в другой таблице и в game_mod пишутся только id мода.
Запрос получается следующий

SELECT * FROM `servers` WHERE `server_game`='minecraft' AND `game_mod` LIKE '%6%'

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

Как мне лучше реализовать поиск?

Answer 1

[Niomin]

Такую связь нужно делать через внешнюю таблицу. То есть ещё одна таблица, в которой будут server_id, mod_id.

Comments

[Денис]

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

[Niomin]

CREATE TABLE `server_mod` (
`server_id` mediumint(5) unsigned NOT NULL,
`mod_id` integer unsigned not null
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

SELECT * FROM `servers` s
INNER JOIN server_mod m ON m.server_id = s.server_id
WHERE `server_game`='minecraft' AND m.mod_id in (6, 7);

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

[Денис]

Niomin: Спасибо за запрос, но дело в том что, пользователь может выбрать по 10 модов сразу и надо выбрать все сервера из бд у которых есть все 10 данных модов

[Niomin]

Денис: Вы можете динамически сгенерировать 10 джойнов:
SELECT * FROM `servers` s
INNER JOIN server_mod m1 ON m1.server_id = s.server_id
INNER JOIN server_mod m2 ON m2.server_id = s.server_id
WHERE `server_game`='minecraft' AND m1.mod_id = 6 AND m2.mod_id = 7;

Это не так плохо, как использовать like по текстовому полю, но всё равно плохо :) Если придумаю что-то адекватное, напишу.

Answer 2

[Draconian]

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

CREATE TABLE  'servers' (
  'server_id' mediumint(5) unsigned NOT NULL,
  'server_game'  varchar(100) DEFAULT NULL,
  'game_mod' mediumint(10)
);

CREATE TABLE  'game_mods' (
  'mod_id' mediumint(5) unsigned NOT NULL,
  'mod_name' varchar(100) DEFAULT NULL,
  'is_pvp' integer(5) NOT NULL,
  ...
);

CREATE TABLE  'servers_game_mods' (
    'server_id' mediumint(5) unsigned NOT NULL,
    'game_mod_id' mediumint(5) unsigned NOT NULL
);

select * from servers s
    join servers_game_mods sgm on sgm.server_id = s.server_id
    join game_mods gm on gm.mod_id = sgm.game_mod_id
where s.server_game = 'minecraft' and gm.is_pvp = true;

Как-то так вроде бы. Остаётся только заполнить таблицу связи корректно (какие моды на каком сервере установлены).

Comments

[Денис]

Предлагаете в таблице 'game_mods' создать 100 столбцов? Если у меня порядка 100-200 модов

[Draconian]

Нет, не предлагаю.
Многоточие там в таблице я поставил просто потому, что не знаю, какие поля в той таблице.

[Draconian]

Денис: Весь смысл здесь именно в таблице связи. Погуглите про это, если я не понятно объяснил.
У вас параметры поиска довольно четкие - нет текстового поиска, поиска каких-то похожих статей и т.п - поэтому вполне реально по ним построить архитектуру БД.

[Денис]

Draconian:
Можете тогда разяснить, в таблице `game_mods` у вас колонка 'is_pvp' integer(5) NOT NULL,
Зачем она ? так же в запросе используется. Если вы говорите не надо создавать кучу колонок

[Draconian]

Денис: Вы привели пример: "Человек ищет сервер minecraft, и выбрал моды "с PvP"". Я это понял так, что у мода есть аттрибут - пвп он или нет. Как вы собирались определять, какие моды для пвп, какие - нет?