Форум MySQL

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

имеем такие названия товаров:
1 - мотошлем обычный
2 - мотокроссы ТИТАН
3 - мотоцикл BMW
...........

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

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

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

спасибо за внимание, надеюсь на вашу помощь

MySQL участвует в работе системы? Не удастся воспользоваться полнотекстовым поиском?

MySQL используется, тип поля для названия товара VARCHAR
к чему вопрос - Не удастся воспользоваться полнотекстовым поиском? если нужен, то нужно использовать

Тогда лучше на него и ориентироваться, в логическом режиме он позволяет делать все, о чем вы пишите, только вам придется в конец слов добавлять символ *, но это уже посильная задача. Собственный полнотекстовый поиск организовывать - это на порядок больше работы.

можно примеры реализаций, а я постараюсь уже под себя сделать, спасибо

Вам нужно проиндексировать поля, по котором будет осуществляться поиск при помощи индекса FULLTEXT, после чего вы можете осуществлять запросы вида

SELECT * FROM tbl WHERE MATCH (col) AGAINST ('мото*' IN BOOLEAN MODE)

спасибо, буду делать как Вы пишете, самый подходящий для меня вариант

чего-то я не пойму, пробую выполнить в пхп скрипте такой запрос:

$strok    = isset($_POST['res']) ? magic_off($_POST['res']) : ''; //вводил слово шлем $sql = "SELECT a.`em_produce_name`,    a.`em_produce_url`,    a.`em_produce_desc`,    a.`em_produce_image_sm`,    a.`em_produce_price_ua`,    a.`em_produce_price_us`,    a.`em_produce_price_eu`,    a.`em_produce_valume`,    a.`em_produce_country`,    a.`em_produce_color`,    a.`em_produce_maxspeed`,    a.`em_produce_fuelflou`,    a.`em_produce_tub`,    a.`em_produce_stat_overal` FROM `em_produce` a JOIN `em_business_group` c  ON (c.`em_business_group_id` = a.`em_business_group_id` AND c.`em_business_group_access` = 'y' AND  c.`em_business_group_deleted` = 'n') WHERE MATCH (a.`em_produce_name`) AGAINST ('".$strok."*' IN BOOLEAN MODE) AND a.`em_produce_access` = 'y' AND a.`em_produce_deleted` = 'n' ORDER BY a.`em_produce_data_upd` DESC";                  $res = select_query($sql);          echo $res['count'];

выдает 0 строк, когда пытался через mysql клиент выполнить этот же запрос, то выдало 35 строк:

SELECT a.`em_produce_name`,    a.`em_produce_url`,    a.`em_produce_desc`,    a.`em_produce_image_sm`,    a.`em_produce_price_ua`,    a.`em_produce_price_us`,    a.`em_produce_price_eu`,    a.`em_produce_valume`,    a.`em_produce_country`,    a.`em_produce_color`,    a.`em_produce_maxspeed`,    a.`em_produce_fuelflou`,    a.`em_produce_tub`,    a.`em_produce_stat_overal` FROM `em_produce` a JOIN `em_business_group` c  ON (c.`em_business_group_id` = a.`em_business_group_id` AND  c.`em_business_group_access` = 'y' AND  c.`em_business_group_deleted` = 'n') WHERE MATCH (a.`em_produce_name`) AGAINST ('шлем*' IN BOOLEAN MODE) AND a.`em_produce_access` = 'y' AND a.`em_produce_deleted` = 'n' ORDER BY a.`em_produce_data_upd` DESC

может проблема в ф-ции, приложу и ее:

function select_query($sql){     $out = array('count' => 0, 'result' => '');     $res = mysql_query($sql);     $out['count'] = mysql_num_rows($res);     if ($out['count'] != 0){         while ($row = mysql_fetch_array($res, MYSQL_ASSOC)){                 foreach ($row as $key => $val){                 if(!isset($out['result'][$key])){                     $out['result'][$key] = array();                 }                 $out['result'][$key][] = iconv("windows-1251","utf-8",$val);             }             }     }     return $out; }

П.С. а вообще хотелось чтобы искало товары в которых слово шлем попадается не только в начале слова а допустим в средине, т.е. мотошлем уже не ищет :( как исправить?

П.С.С. если заменить этим, то находит все что нужно мотошлем, подшлемник и тд, почему с тем запросом так не ищет?

WHERE a.`em_produce_name` LIKE '%шлем%'

Что за функция select_query() и как она работает?

я ее приложил, выдает результат в виде массива состоящего из кол. записей и полей с результатом, вот распечатал часть вывода с лимитом в 1 запись:

Array ( [count] => 1 [result] => Array (    [em_produce_name] => Array (      [0] => Мотоботы Suzuki GSXR - спортивные )    [em_produce_url] => Array ( [0] => )   [em_produce_desc] => Array (      [0] => Легкая спортивная мотообувь Suzuki GSXR ,  изготовлена измягкой кожи и нескользящей резиновой подошвы.  Мотоботы Suzuki GSXR– это современная модель в среднем  ценовом диапазоне. • Вид продукции: Мотоботы •  Производитель: Suzuki • Название: GSXR • Размеры мотообуви:  34-49 • Цвет: Черный )   [em_produce_image_sm] => Array ( [0] => files/companies/produces/s_d9fbc4e8548ecf6f4bbeea57e400d4ba.jpg )    [em_produce_price_ua] => Array ( [0] => 1214.00 )    [em_produce_price_us] => Array ( [0] => 0.00 )    [em_produce_price_eu] => Array ( [0] => 0.00 )    [em_produce_valume] => Array ( [0] => )    [em_produce_country] => Array ( [0] => )    [em_produce_color] => Array ( [0] => )    [em_produce_maxspeed] => Array ( [0] => )      [em_produce_fuelflou] => Array ( [0] => )    [em_produce_tub] => Array ( [0] => )    [em_produce_stat_overal] => Array ( [0] => 0 ) ) )

После строки

$res = mysql_query($sql);

поставьте обработчик с участием функции mysql_error() - никакой ошибки не возвращается?

если Вы имели ввиду

$res = select_query($sql); mysql_error();

или

$res = select_query($sql); echo mysql_error();

то ничего не вывело

А скрипт в какой кодировке и в какой кодировке значение в $strok? Если что-нибудь английское поискать - находит?

джаваскрипт в кодировке утф-8, он посылает искомое значение в пхп обработчик, пхп обработчик тоже в утф-8 формате, таблица тоже эта в утф-8

вот пошел мимо ф-ции и сделал напрямую результат один и тотже, что с ру или енгл поисковыми словами, каунт = 0

$sql = mysql_query("             SELECT a.`em_produce_name`,                    a.`em_produce_url`,                    a.`em_produce_desc`,                    a.`em_produce_image_sm`,                    a.`em_produce_price_ua`,                    a.`em_produce_price_us`,                    a.`em_produce_price_eu`,                    a.`em_produce_valume`,                    a.`em_produce_country`,                    a.`em_produce_color`,                    a.`em_produce_maxspeed`,                    a.`em_produce_fuelflou`,                    a.`em_produce_tub`,                    a.`em_produce_stat_overal`             FROM `em_produce` a             JOIN `em_business_group` c  ON (c.`em_business_group_id` = a.`em_business_group_id`  AND c.`em_business_group_access` = 'y'  AND c.`em_business_group_deleted` = 'n')             WHERE MATCH (a.`em_produce_name`) AGAINST ('".$strok."*' IN BOOLEAN MODE)             AND a.`em_produce_access` = 'y'             AND a.`em_produce_deleted` = 'n'             ORDER BY a.`em_produce_data_upd` DESC            ");   echo mysql_num_rows($sql);

А если SQL-запрос вывести в окно браузера - там никаких аномалий нет? И если его из браузера скопировать в тот же phpMyAdmin результат тот же или строки возвращаются?

вывел запрос в браузер, вставил в phpMyAdmin, отработало на ура и вывело все имеющиеся записи, попробовал вставить эту распечатку с браузера снова в запрос, строк = 0, капец

П.С. насчет ф-ции select_query я ошибался, она тут не причем, вернул я ее обратно, т.е. через нее идет запрос, то при русском поиске резалт=0, при английском поиске резалт=нужное кол. строк

почему с русским проблемы?

вот заметил прикол если из пхп скрипта выполнить запрос с русским словом шлем вылезет ошибка Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in с английскими буквами такого не происходит, данный запрос в пхпмайадмине нормально реагирует, без ошибок

$sql = mysql_query("             SELECT a.`em_produce_name`,                    a.`em_produce_url`,                    a.`em_produce_desc`,                    a.`em_produce_image_sm`,                    a.`em_produce_price_ua`,                    a.`em_produce_price_us`,                    a.`em_produce_price_eu`,                    a.`em_produce_valume`,                    a.`em_produce_country`,                    a.`em_produce_color`,                    a.`em_produce_maxspeed`,                    a.`em_produce_fuelflou`,                    a.`em_produce_tub`,                    a.`em_produce_stat_overal`             FROM `em_produce` a             JOIN `em_business_group` c ON (c.`em_business_group_id` = a.`em_business_group_id`  AND c.`em_business_group_access` = 'y'  AND c.`em_business_group_deleted` = 'n')             WHERE a.`em_produce_name` = 'шлем'             AND a.`em_produce_access`='y'             AND a.`em_produce_deleted`='n'             ORDER BY a.`em_produce_data_upd` DESC            ");      echo mysql_num_rows($sql);

>Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in
Значит mysql_query() вернула false вместо дескриптора, а так как у вас нет проверки с участием mysql_error(), то и подробная информация не доступна - нужно добавить обработчик такой ошибки. А лучше его всегда добавлять, даже если запрос очень простой - такая тактика экономит время.

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

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

MATCH (a.`em_produce_name`) AGAINST ('".iconv("utf-8","windows-1251",$strok)."*' IN BOOLEAN MODE)

хотя джаваскрипт в утф-8, пхп скрипт тоже утф-8, таблица утф-8, а вот сама база вин1251 (( видать это и влияло

теперь вопрос в следующем, почему оно ищет по запросу шлем, а не ищет мотошлем и тд??

Вообще должен искать. А если убрать символ * все-равно не ищет?

без * - 51 строка, с * - 54 строки результата

Это нормально. Имеется в виду что происходит при удалении * в ситуации с "мотошлем"?

WHERE MATCH (a.`em_produce_name`) AGAINST ('мотошлем' IN BOOLEAN MODE) //записей извлечено: 924 (250 мс) WHERE MATCH (a.`em_produce_name`) AGAINST ('мотошлем*' IN BOOLEAN MODE) //записей извлечено: 951 (250 мс)

приложу дамп таблицы, но вроде все верно

CREATE TABLE `em_produce` (   `em_produce_id` int(10) unsigned NOT NULL auto_increment,   `em_produce_parent` int(10) unsigned NOT NULL default '0', ................   `em_produce_name` varchar(255) collate utf8_unicode_ci NOT NULL default '',   `em_produce_data_upd` datetime NOT NULL default '0000-00-00 00:00:00',   `em_produce_access` enum('y','n') collate utf8_unicode_ci NOT NULL default 'y',   `em_produce_deleted` enum('y','n') collate utf8_unicode_ci NOT NULL default 'n',   PRIMARY KEY  (`em_produce_id`,`em_produce_parent`),   FULLTEXT KEY `em_produce_name` (`em_produce_name`) ) ENGINE=MyISAM AUTO_INCREMENT=33342 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

А говорите не ищет... почти 1000 записей было возвращено.

WHERE MATCH (a.`em_produce_name`) AGAINST ('шлем*' IN BOOLEAN MODE) //записей извлечено: 54 (78 мс)

видимо мы друг друга не понимаем, при вводе шлем оно мне находит все шлемы, но не мотошлемы, а должно и шлемы и мотошлемы

А понял, но, к сожалению, с этим ничего нельзя поделать... в полнотекстовом поиске мы имеем дело с индексом, т.е. поиск всегда ведется от начала слова. А "мотошлем" начинается с "мото".

а если делать все же как мне хочется то нужно использовать

WHERE a.`em_produce_name` LIKE '%шлем%'

тогда ищет шлем в мотошлеме и др.

но насколько LIKE медленней Вашего метода, или разницы в скорости нету?

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

PS В любом случае для популярных слов не плохо создать свой минииндекс, а лучше подумать над созданием своего собственного полнотекстового поиска. Суть в том, чтобы создать дополнительную таблицу с ключевыми словами и ссылками на страницы. Т.е. лопатить каждый раз не тексты, а искать среди коротких ключевых слов (которые к тому же еще можно и проиндексировать).

идея суппер, буду разговаривать с начальством по этому поводу

т.е. таблица примерно такая: ид, ключ, ид товаров (через запятую)

после ввода в поиск мотоцикл Suzuki разбиваем строку по пробельно и ищем эти 2 слова в таблице, получаем списки ид товаров и потом выбираем эти товары с другой таблицы

все верно понял?

или можно создать поле в таблице товаров типа ключевые слова и после искать по нему?

Да, все верно, в общем это и есть полнотекстовый поиск.

>или можно создать поле в таблице товаров типа ключевые слова и после искать по нему?
Нет, лучше если это будет отдельная таблица, как правило, она со временем становится очень здоровой. А чем меньше таблица - тем быстрее проходят операции с ней.

и точно, я даже не задумался, представим 2 ситуации, когда таблица товаров содержит 500 000 записей

1 - создадим в таблице доп. поле под ключевики и сделаем его fulltext
2 - создадим доп. таблицу как я представил выше и сделаем fulltext на поле key

Рассматриваем естественно полнотекстовый поиск.

При первом варианте мы перелопачиваем довольно большой объем данных в поисках введенных ключевых слов в поиск.

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

Подведу итог второй вариант быстрее будет. Так ли?

Дело в том, что когда у вас будет отдельно поле с ключевым словом (длиной не более 255 символов), вы сможете работать эффективно при помощи LIKE. В этом и фокус, что LIKE особенно с двумя символами % в начале и конце шаблона - очень не эффективен, так как ему этот шаблон нужно протаскивать по длительным описанием. А когда у вас в поле лежит готовое ключевое слово - его эффективность резко возрастает, особенно, когда у вас есть возможность убрать % из начала шаблона. На самом деле и полнотекстовый поиск так работает, он просто извлекает все уникальные слова (за исключением стоп-слов и очень часто используемых слов) и выстраивает их в индекс по алфавиту. Когда вам нужно какое-то слово, он вместо того, чтобы лопатить все статьи и описания, просто быстро находит ключевое слово и пользуется ссылками из индекса. Вы можете делать тоже самое, при этом не тратить лишнее место на полнотекстовый индекс (который зачастую жрет столько же, сколько сами данные).

На данный момент нет возможности (времени у операторов) под каждый товар, а их более 25 тищ делать целевые ключевые слова. Когда появится время я им сделаю поле и пусть развлекаются. Т.е. я пока остановился на полнотекстовом по таблице товаров по полю название товара.

Для будущего какой вариант предпринять?
1 - делать отдельную таблицу для ключевых слов со списком ид товаров для каждого ключа, после выборки использовать этот список ид для выборки из таблицы товаров
2 - убрать fulltext индекс из таблицы товаров по полю название товара, создать поле для ключей VARCHAR(255) и использовать оператор LIKE 'искомое слово%'

А и не надо их делать, во-первых можно отобрать их из названий, можно поставить ловушку и отбирать их запросов пользователей. Да операторы вам точнее ссылки расставят (если не ошибутся). Однако, можно начать с полнотекстового поиска, но постепенно наращивать базу ключевых слов, для которой регулярно осуществлять по cron медленный, но безошибочный LIKE-поиск. Тогда при следующем обращении (а ищут примерно одно и тоже), нагрузка на базу данных будет снижаться. А у операторов и без ключевых слов забот полон рот, тем более они все-равно все не подберут.

из названий как по мне это не совсем то, много хлама может попасть

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

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

так а все таки, я не пойму, мне делать отдельную таблицу под ключевики со списком ид товаров (по запросу ищем ключи, берем их списки ид и по ним выбираем с основной таблицы товары) или делать доп. поле в товарах и по нему делать LIKE 'искомое слово%' поиск

В любом случае под ключевые слова лучше делать отдельную таблицу.

спасибо

перед тем как подставить поисковую строку я обработаю ее этой ф-цией:

function magic_off($data){        if(is_array($data)){          if(get_magic_quotes_gpc()){             $data = array_map("stripslashes", $data);          }else{             $data = array_map("mysql_escape_string", $data);          }                  return  $data;       }else{          if(get_magic_quotes_gpc()){             $data = stripslashes($data);          }else{             $data = mysql_escape_string($data);          }                            return $data;        }    }

ф-ция корректна?

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

function magic_off($data){         if(is_array($data)){           if(get_magic_quotes_gpc()){              $data = array_map("stripslashes", $data);           }         $data = array_map("mysql_escape_string", $data);                     return  $data;        }else{           if(get_magic_quotes_gpc()){              $data = stripslashes($data);           }          $data = mysql_escape_string($data);                     return $data;         }     }

PS Под новые вопросы, лучше заводить новые темы.