Создание условия для простого поискового SQL запроса, основанного на LIKE и REGEXP - Базы данных - Интересные решения для программистов - Примеры кода

Базы данных → Создание условия для простого поискового SQL запроса, основанного на LIKE и REGEXP → PHP

Создает условия для простого поискового SQL запроса, основанного на LIKE и REGEXP.
Поисковая фраза и данные в таблице БД д.б. в кодировке UTF-8!

LIKE используется для оптимизации скорости (и стоит в условии первым!),
REGEXP -- для точного поиска целых вхождений слов и чисел.

Синтаксис и примеры поискового запроса:
"квантов* механик*", "сервер 3.4GHz 1024Mb", "смысл* жизни" -- ищутся вхождения всех слов (логическое "И")
"квантов* | механик*" -- ищутся вхождения любого найденного (логическое "ИЛИ")
Необязательный символ "*" заменяет ноль или более букв и может стоять только в конце слова!
В начале или в середине слова звёздочка стоять не может!
Регистр слов при поиске не учитывается!

/**
 *
 * @param    string               $s        поисковая фраза
 * @param    array                &$words   массив поисковых слов из запроса (слова и цифры)
 *                                          может понадобится, например, при "подсвечивании" найденных слов в тексте
 * @param    mixed(string/array)  $field    поле или массив полей, по которым будет производиться поиск
 * @return   mixed(string/false)  SQL код для условия WHERE, имя поля обозначается через {field}
 *                                возвращает false в случае ошибки в синтаксисе поискового запроса
 *
 * @license  http://creativecommons.org/licenses/by-nc-sa/3.0/
 * @author   Nasibullin Rinat <n a s i b u l l i n  at starlink ru>
 * @charset  ANSI
 * @version  2.0.5
 */
function utf8_simple_search_sql($s, array &$words, $field = '{field}')
{
    #хак для MySQL для поиска целых слов в кодировке UTF-8 через REGEXP
    #английский   турецкие буквы (татарский латиница)
    #русский   татарские буквы (кириллица)

#предыдущий/последующий символ -- не буква (lowercase):
    #/*static*/ $re_utf8_no_letter_begin_lc = "([^a-z\x80-\x8f\x91\x97\x99\x9f\xa3\xa4\xa7\xa9\xaf\xb0-\xbf]|\xc3[^\xa4\xa7\xb1\xb6\xbc]|\xc4[^\x9f\xb1]|\xc5[^\x9f]|\xd0[^\xb0-\xbf]|\xd1[^\x80-\x8f\x91]|\xd2[^\x97\xa3\xaf\xbb]|\xd3[^\x99\xa9]|^)";
    #/*static*/ $re_utf8_no_letter_end_lc   = "([^a-z\xc3-\xc5\xd0-\xd3]|\xc3[^\xa4\xa7\xb1\xb6\xbc]|\xc4[^\x9f\xb1]|\xc5[^\x9f]|\xd0[^\xb0-\xbf]|\xd1[^\x80-\x8f\x91]|\xd2[^\x97\xa3\xaf\xbb]|\xd3[^\x99\xa9]|$)";

#предыдущий/последующий символ -- не буква (all):
    /*static*/ $re_utf8_no_letter_begin = "([^a-zA-Z\x80-\xbf]|\xc3[^\xa4\xa7\xb1\xb6\xbc\x84\x87\x91\x96\x9c]|\xc4[^\x9f\xb1\x9e\xb0]|\xc5[^\x9f\x9e]|\xd0[^\x90-\xbf\x81]|\xd1[^\x80-\x8f\x91]|\xd2[^\x96\x97\xa2\xa3\xae\xaf\xba\xbb]|\xd3[^\x98\x99\xa8\xa9]|^)";
    /*static*/ $re_utf8_no_letter_end   = "([^a-zA-Z\xc3-\xc5\xd0-\xd3]|\xc3[^\xa4\xa7\xb1\xb6\xbc\x84\x87\x91\x96\x9c]|\xc4[^\x9f\xb1\x9e\xb0]|\xc5[^\x9f\x9e]|\xd0[^\x90-\xbf\x81]|\xd1[^\x80-\x8f\x91]|\xd2[^\x96\x97\xa2\xa3\xae\xaf\xba\xbb]|\xd3[^\x98\x99\xa8\xa9]|$)";

#русский   татарские буквы (кириллица)
        'tt'    => '\xd0[\x90-\xbf\x81]|\xd1[\x80-\x8f\x91]                          #А-я (all)
                   |\xd2[\x96\x97\xa2\xa3\xae\xaf\xba\xbb]|\xd3[\x98\x99\xa8\xa9]',  #татарские буквы (all)
        'tt_uc' => '\xd0[\x90-\xaf\x81]                      #А-Я (uppercase)
                   |\xd2[\x96\xa2\xae\xba]|\xd3[\x98\xa8]',  #татарские буквы (uppercase)
        'tt_lc' => '\xd0[\xb0-\xbf]|\xd1[\x80-\x8f\x91]      #а-я (lowercase)
                   |\xd2[\x97\xa3\xaf\xbb]|\xd3[\x99\xa9]',  #татарские буквы (lowercase)
    );

#заменяем недопустимые символы из таблицы ASCII на пробел (за исключением " ", "|", "*")
    $s = preg_replace('/(?![\x20\x7c\x2a])[\x00-\x2f\x3a-\x40\x5b-\x60\x7b-\x7f]/s', ' ', $s);
    $trans = array(
        "\xc2\xad" => '',    #вырезаем "мягкие" переносы строк ()
        "\xcc\x81" => '',    #знак ударения  (U 0301 «combining acute accent»)
        "\xc2\xa0" => ' ',   #неразрывный пробел
    );
    $s = strtr($s, $trans);
    #$s = str_replace(array_keys($trans), array_values($trans), trim($s, ' |'));
    if (! function_exists('utf8_convert_case')) include_once 'utf8_convert_case.php'; #оптимизация скорости include_once
    $s = utf8_convert_case($s, CASE_LOWER);

#проверка синтаксиса поискового запроса:
    if (! preg_match('/^(?:(?:(?>' . $re_letters['tr_lc'] . ')   #слова английские или турецкие
                            | (?>' . $re_letters['tt_lc'] . ')   #слова русские или татарские
                            | \d 
                            )(?>\*?\x20)?
                            \x20*(?>\|\x20*)?
                         ) 
                       $/sx', $s . ' ')) return false; #неверный поисковый запрос
    #d($s);
    if (! function_exists('utf8_ucfirst')) include_once 'utf8_ucfirst.php'; #оптимизация скорости include_once
    preg_match_all('/  ((?>' . $re_letters['tr_lc'] . ') \*?)  #1 слова английские или турецкие
                     | ((?>' . $re_letters['tt_lc'] . ') \*?)  #2 слова русские или татарские
                     | (\d \*?)  #3 цифры
                    /sx', $s, $m);
    $words = array_unique($m[0]);

$cond = (strpos($s, '|') === false) ? 'AND' : 'OR';
    $q = array();
    foreach ($words as $i => $word)
    {
        if ($is_mask = (substr($word, -1) === '*')) $word = rtrim($word, '*');

if (strlen($m[3][$i]) > 0)  #для цифр
        {
            $re_word = '([^0-9]|^)' . $word . ($is_mask ? '' : '([^0-9]|$)');
            $like    = '{field} LIKE   BINARY "%' . $word    . '%"';
            $regexp  = '{field} REGEXP BINARY  "' . $re_word . '"';
        }
        else  #для букв
        {
            $word_cases = array(
                'lowercase' => $word,                                 #word
                'ucfirst'   => utf8_ucfirst($word),                   #Word
                'uppercase' => utf8_convert_case($word, CASE_UPPER),  #WORD
            );
            $like = '( {field} LIKE BINARY 0x' . bin2hex('%' . $word_cases['lowercase'] . '%') . ' OR
                       {field} LIKE BINARY 0x' . bin2hex('%' . $word_cases['ucfirst']   . '%') . ' OR
                       {field} LIKE BINARY 0x' . bin2hex('%' . $word_cases['uppercase'] . '%') . ' )';
            $re_word = $re_utf8_no_letter_begin . '(' . implode('|', $word_cases) . ')' . ($is_mask ? '' : $re_utf8_no_letter_end);
            $regexp = '{field} REGEXP BINARY 0x' . bin2hex($re_word);
        }
        #:DEBUG:
        #d(array('like' => $like, 'regexp' => $regexp));

if ($cond == 'AND')
        {
            $q['like'][]   = $like;
            $q['regexp'][] = $regexp;
        }
        else $q[] = '(' . $like . ' AND ' . $regexp . ')';
    }#foreach

$r = '(' . implode("\r\n" . $cond . ' ', ($cond == 'AND') ? array_merge($q['like'], $q['regexp']) : $q) . ')';

$a = array();
    $fields = is_array($field) ? $field : array($field);
    foreach ($fields as $k => $v) $a[] = str_replace('{field}', $v, $r);
    return '(' . implode(' OR ', $a) . ')';
}

database, like, mysql, php, regexp, search, simple, sql

KOS_MOS

Nasibullin Rinat

07.06.2008

Меню

Подписка на RSS:

Подпишись на RSS-ленту

Поиск

Облако тегов

	Базы данных → Создание условия для простого поискового SQL запроса, основанного на LIKE и REGEXP → PHP Создает условия для простого поискового SQL запроса, основанного на LIKE и REGEXP. Поисковая фраза и данные в таблице БД д.б. в кодировке UTF-8! LIKE используется для оптимизации скорости (и стоит в условии первым!), REGEXP -- для точного поиска целых вхождений слов и чисел. Синтаксис и примеры поискового запроса: "квантов* механик", "сервер 3.4GHz 1024Mb", "смысл жизни" -- ищутся вхождения всех слов (логическое "И") "квантов* \| механик" -- ищутся вхождения любого найденного (логическое "ИЛИ") Необязательный символ "" заменяет ноль или более букв и может стоять только в конце слова! В начале или в середине слова звёздочка стоять не может! Регистр слов при поиске не учитывается! /** * * @param string $s поисковая фраза * @param array &$words массив поисковых слов из запроса (слова и цифры) * может понадобится, например, при "подсвечивании" найденных слов в тексте * @param mixed(string/array) $field поле или массив полей, по которым будет производиться поиск * @return mixed(string/false) SQL код для условия WHERE, имя поля обозначается через {field} * возвращает false в случае ошибки в синтаксисе поискового запроса * * @license http://creativecommons.org/licenses/by-nc-sa/3.0/ * @author Nasibullin Rinat <n a s i b u l l i n at starlink ru> * @charset ANSI * @version 2.0.5 / function utf8_simple_search_sql($s, array &$words, $field = '{field}') { #хак для MySQL для поиска целых слов в кодировке UTF-8 через REGEXP #английский турецкие буквы (татарский латиница) #русский татарские буквы (кириллица) #предыдущий/последующий символ -- не буква (lowercase): #/static/ $re_utf8_no_letter_begin_lc = "([^a-z\x80-\x8f\x91\x97\x99\x9f\xa3\xa4\xa7\xa9\xaf\xb0-\xbf]\|\xc3[^\xa4\xa7\xb1\xb6\xbc]\|\xc4[^\x9f\xb1]\|\xc5[^\x9f]\|\xd0[^\xb0-\xbf]\|\xd1[^\x80-\x8f\x91]\|\xd2[^\x97\xa3\xaf\xbb]\|\xd3[^\x99\xa9]\|^)"; #/static/ $re_utf8_no_letter_end_lc = "([^a-z\xc3-\xc5\xd0-\xd3]\|\xc3[^\xa4\xa7\xb1\xb6\xbc]\|\xc4[^\x9f\xb1]\|\xc5[^\x9f]\|\xd0[^\xb0-\xbf]\|\xd1[^\x80-\x8f\x91]\|\xd2[^\x97\xa3\xaf\xbb]\|\xd3[^\x99\xa9]\|$)"; #предыдущий/последующий символ -- не буква (all): /static/ $re_utf8_no_letter_begin = "([^a-zA-Z\x80-\xbf]\|\xc3[^\xa4\xa7\xb1\xb6\xbc\x84\x87\x91\x96\x9c]\|\xc4[^\x9f\xb1\x9e\xb0]\|\xc5[^\x9f\x9e]\|\xd0[^\x90-\xbf\x81]\|\xd1[^\x80-\x8f\x91]\|\xd2[^\x96\x97\xa2\xa3\xae\xaf\xba\xbb]\|\xd3[^\x98\x99\xa8\xa9]\|^)"; /static/ $re_utf8_no_letter_end = "([^a-zA-Z\xc3-\xc5\xd0-\xd3]\|\xc3[^\xa4\xa7\xb1\xb6\xbc\x84\x87\x91\x96\x9c]\|\xc4[^\x9f\xb1\x9e\xb0]\|\xc5[^\x9f\x9e]\|\xd0[^\x90-\xbf\x81]\|\xd1[^\x80-\x8f\x91]\|\xd2[^\x96\x97\xa2\xa3\xae\xaf\xba\xbb]\|\xd3[^\x98\x99\xa8\xa9]\|$)"; #буквы алфавитов static $re_letters = array( #английский турецкие буквы (татарский латиница) 'tr' => '[a-zA-Z]\|\xc3[\xa4\xa7\xb1\xb6\xbc\x84\x87\x91\x96\x9c]\|\xc4[\x9f\xb1\x9e\xb0]\|\xc5[\x9f\x9e]', #(all) 'tr_uc' => '[A-Z]\|\xc3[\x84\x87\x91\x96\x9c]\|\xc4[\x9e\xb0]\|\xc5\x9e', #(uppercase) 'tr_lc' => '[a-z]\|\xc3[\xa4\xa7\xb1\xb6\xbc]\|\xc4[\x9f\xb1]\|\xc5\x9f', #(lowercase) #русский татарские буквы (кириллица) 'tt' => '\xd0[\x90-\xbf\x81]\|\xd1[\x80-\x8f\x91] #А-я (all) \|\xd2[\x96\x97\xa2\xa3\xae\xaf\xba\xbb]\|\xd3[\x98\x99\xa8\xa9]', #татарские буквы (all) 'tt_uc' => '\xd0[\x90-\xaf\x81] #А-Я (uppercase) \|\xd2[\x96\xa2\xae\xba]\|\xd3[\x98\xa8]', #татарские буквы (uppercase) 'tt_lc' => '\xd0[\xb0-\xbf]\|\xd1[\x80-\x8f\x91] #а-я (lowercase) \|\xd2[\x97\xa3\xaf\xbb]\|\xd3[\x99\xa9]', #татарские буквы (lowercase) ); #заменяем недопустимые символы из таблицы ASCII на пробел (за исключением " ", "\|", "") $s = preg_replace('/(?![\x20\x7c\x2a])[\x00-\x2f\x3a-\x40\x5b-\x60\x7b-\x7f]/s', ' ', $s); $trans = array( "\xc2\xad" => '', #вырезаем "мягкие" переносы строк () "\xcc\x81" => '', #знак ударения (U 0301 «combining acute accent») "\xc2\xa0" => ' ', #неразрывный пробел ); $s = strtr($s, $trans); #$s = str_replace(array_keys($trans), array_values($trans), trim($s, ' \|')); if (! function_exists('utf8_convert_case')) include_once 'utf8_convert_case.php'; #оптимизация скорости include_once $s = utf8_convert_case($s, CASE_LOWER); #проверка синтаксиса поискового запроса: if (! preg_match('/^(?:(?:(?>' . $re_letters['tr_lc'] . ') #слова английские или турецкие \| (?>' . $re_letters['tt_lc'] . ') #слова русские или татарские \| \d )(?>\?\x20)? \x20(?>\\|\x20)? ) $/sx', $s . ' ')) return false; #неверный поисковый запрос #d($s); if (! function_exists('utf8_ucfirst')) include_once 'utf8_ucfirst.php'; #оптимизация скорости include_once preg_match_all('/ ((?>' . $re_letters['tr_lc'] . ') \?) #1 слова английские или турецкие \| ((?>' . $re_letters['tt_lc'] . ') \?) #2 слова русские или татарские \| (\d \?) #3 цифры /sx', $s, $m); $words = array_unique($m[0]); $cond = (strpos($s, '\|') === false) ? 'AND' : 'OR'; $q = array(); foreach ($words as $i => $word) { if ($is_mask = (substr($word, -1) === '')) $word = rtrim($word, ''); if (strlen($m[3][$i]) > 0) #для цифр { $re_word = '([^0-9]\|^)' . $word . ($is_mask ? '' : '([^0-9]\|$)'); $like = '{field} LIKE BINARY "%' . $word . '%"'; $regexp = '{field} REGEXP BINARY "' . $re_word . '"'; } else #для букв { $word_cases = array( 'lowercase' => $word, #word 'ucfirst' => utf8_ucfirst($word), #Word 'uppercase' => utf8_convert_case($word, CASE_UPPER), #WORD ); $like = '( {field} LIKE BINARY 0x' . bin2hex('%' . $word_cases['lowercase'] . '%') . ' OR {field} LIKE BINARY 0x' . bin2hex('%' . $word_cases['ucfirst'] . '%') . ' OR {field} LIKE BINARY 0x' . bin2hex('%' . $word_cases['uppercase'] . '%') . ' )'; $re_word = $re_utf8_no_letter_begin . '(' . implode('\|', $word_cases) . ')' . ($is_mask ? '' : $re_utf8_no_letter_end); $regexp = '{field} REGEXP BINARY 0x' . bin2hex($re_word); } #:DEBUG: #d(array('like' => $like, 'regexp' => $regexp)); if ($cond == 'AND') { $q['like'][] = $like; $q['regexp'][] = $regexp; } else $q[] = '(' . $like . ' AND ' . $regexp . ')'; }#foreach $r = '(' . implode("\r\n" . $cond . ' ', ($cond == 'AND') ? array_merge($q['like'], $q['regexp']) : $q) . ')'; $a = array(); $fields = is_array($field) ? $field : array($field); foreach ($fields as $k => $v) $a[] = str_replace('{field}', $v, $r); return '(' . implode(' OR ', $a) . ')'; } database, like, mysql, php, regexp, search, simple, sql KOS_MOS Nasibullin Rinat 07.06.2008 Ваше имя Ваш E-mail (если хотите отслеживать комментарии) Дoкажи, что ты не робот Текст комментария	Меню CodeIgniter Алгоритмы Базы данных Дата и Время Интернет Интерфейс Мультимедиа ОС и Железо Работа с текстом Разное Технологии Файлы Подписка на RSS: Подпишись на RSS-ленту Поиск Облако тегов .net array asp bash browser charset check class clean convert count create css curl database date delete directory download element email equivalent extension file find form format function get height html http ie image javascript jquery load mysql number page perl php random scriplet send shell size sql string text time url utf8 variable vb visual basic width window xml xslt