PHP MySQL: Vyhledávání

Pro vyhledávání v MySQL databází existuje několik možností. Některé z nich jsou přímo implementovány v databází, jiné lze provádět pomocí správných operátorů v dotazech.

Pro všechny ukázkové dotazy budeme kvůli jednoduchosti vycházet ze struktury tabulky podobné tého:

Mysql: Like

Prvním, nejjednodušším a nejméně efektivním způsobem je vyhledávání pomocí příkazu LIKE, který dokáže porovnat dva řetězce a určit jejich shodnost.

Operátor Like se používá jako podmínka za klíčovým slovem Where a neumožňuje žádné složité konstrukce dotazu. Z toho plyne že je u složitějšího vyhledávání nepoužitelné a výsledky nejsou mnohdy moc dobré. Navíc nedokáže řadit dle relevance vyhledávání.

Základní dotaz pomocí Like může vypadat následovně:

Takový dotaz vrací pouze takové řádky, ve kterých je přimo obsažen text „hledavné slovo“ a to ve sloupcích “nazev”, nebo text.

Takového vyhledávání je téměř nepoužitelné, ale naštěstí i v LIKE můžeme dotaz z určité části vylepšit použitím znaků % a _.

Znak „%“ nahrazuje ve hledaném slově libovolný počet znaků, které mohou být na jeho místě. Libovolným počtem se samozřejmě rozumí i 0.

Pokud chceme například najít všechny sloupce v tabulce, ve kterých se vyskytuje slovo „slovo“, ale i toto slovo se může vyskytovat i mezi jiným textem, může dotaz vypadat následovně:

Znak „_“ na rozdíl od procenta nahradí pouze jeden znak v hledaném řetězci. Využití není při vyhledávání takové jako u znaku procenta, ale i tak může být občas použitelné.

Využití by mohlo nastat například v případě, kdy chceme vyhledávat ve sloupcích s danou strukturou, jako je telefonní číslo, nebo podobně:

Takový dotaz vrací všechny výsledky, které odpovídají našemu „modelu“ v dotazu v tabulce s telefonními čísly.

Operátor Like umí pracovat s velikostí písmen a tudíž nemusíme písmena převádět na velká čí malá.

Mysql: Regularní výrazy

Dalším a mnohem lepším způsobem vyhledávání je použití regularních výrazů na straně databázového serveru.

Mysql nabízí funkce REGEXP která dokáže porovnat hledaný řetězec s regularním výrazem, který je jako parametr.

Bohužel toto vyhledávání ma í své neduhy a to hlavně v podobně rychlosti vyhledávání. Regularní výrazy jsou mnohem složitější než použití operátoru Like a proto taky pomalejší.

Ovšem regularní výrazy jsou mocnou zbraní při vyhledávání a za jejich pomoci můžeme vytvářet mnohem přesnější vyhledávání.

Musíme se tedy rozhodnout, zda potřebujeme používat regularní výrazy, nebo nám bude stačit operátor Like.

Například náš dotaz:

můžeme nahradit regularním výrazem:

V takovémto případě by bylo mnohem jednodušší a rychlejší použití operátoru LIKE, které by vrátilo stejný výsledek vyhledávání.

Pomocí regularních výrazů můžeme vyhledávat v textu pomocí pravidel vytvořených pomocí zástupných znaků. Správnou formulací regularních výrazů můžeme dostat pravidlo téměř pro jakýkoli text.

Operátor „$“ slouží pro rozlišení konce řetězce. Pokud bychom chtěli najít všechny řádky, které končí na určitý řetězec, můžeme tento operátor použít:

Takový dotaz by vyhledal všechny řádky, jejihš sloupce končí na písmeno „o“.

Operátor „^“ slouží pro rozlišení začátku řetězce. Použití je obdobné jako u předchozího dotazu.

Konstrukce „[[:<:]], [[:>:]]“

asi nejčastěji používanou konstrukcí při vyhledávání pomocí regularních výrazů je právě tato struktura.

Příklad použití si ukážeme na příkladu:

Takovýto dotaz najde všechny řádky právě takové, které obsahují sloupce, obsahující slovo „slovo“. Na rozdíl od použití LIKE ‘%slovo%’ nebude hledat výskyt řetězce, ale pouze celá slova.

Dejme tomu, že v proměnné $_GET[‘search’] budeme mít řetězec, který chceme vyhledat v databázi. Tento řetězec bude obsahovat více slov, a my budeme chtít aby hledal jak celý řetězec tak jednotlivá slova:

Tento kód nám vytvoří SQL dotaz ve tvaru:

Čili vyhledá právě ty sloupce, které obsahují buď celý řetězec „testovaci retezec“ nebo jenom některé z těchto dvou slov: Array ( [0] => testovaci [1] => retezec )

Mysql: Fulltetové vyhledávání

Fulltext je metoda pro vyhledávání textu. Při vyhledávání je tento způsob asi nejpřesnější ovšem opět jsou zde určitá omezení a to hlavně v českém internetu. Diakritika je problém fulltextu, protože s ní neumí korektně pracovat a tudíž při vyhledávání slova „tičinka“ s diakritikou nevyhledá slova „ticinka“ bez diakritiky. Další nevýhoda je skloňování slov, které se v českém jazyce hojně vyskytuje.

Ale i tak je fulltextové vyhledávání použitelné a mnohdy hodně efektivní.

Tabulka pro fulltext

Pokud chceme vyhledávat pomocí fulltextu v MySQL, musí tabulka pro vyhledávání obsahovat údaje v podobě fulltextových indexů. Tyto klíče lze vytvářet pouze na sloupcích typu CHAR, VARCHAR, TEXT, … Prostě všech textových sloupcích ale ne na jiných. Fulltextové indexy lze vytvářet pouze nad tabulkami typu MyISAM.

Fulltextové indexy můžeme vytvořit i na více než jednom sloupci tabulky. Klíče vytvoříme následujícím příkazem:

Těmito dvěma příkazy jsme vytvořili fulltextové indexy nad sloupci „nazev“ a „text“ a v těchto dvou sloupcích budeme nyní moci pomocí fulltextu vyhledávat.

Pro samotné vyhledávání slouží v MySQL operátory Match – Against. Nejjednodužší dotaz může potom vypadat následovně:

Tento dotaz bude hledat ve sloupci „nazev“ slovo „slovo“ za využití fulltextového vyhledávání. Tento základní dotaz je ještě něčím zvláštní a to tím, že bude vracet výsledky seřazené dle určitě posloupnosti – dle relevance.

Relevance je určité měřítko, dle kterého lze určit jak silně daná věc(v tomto případě určitý výsledek) ovlivňuje celkový dotaz v ohledu na ostatní výsledky.

Výsledky jsou tedy řazeny dle toho, jak moc bylo vyhledávání úspěšné. Relevance je obyčejné desetinné číslo, takže ji můžeme bezproblémů zahrnout mezi výsledky, které databáze vrací:

Jak jsem již napsal, řazení v takovém dotazu probíhá defaultně dle relevance. Vytvořme si další příklad dotazu, kdy budeme chtít pomocí fulltextu vyhledávat ve dvou sloupcích:

Relevance je desetinné číslo, a pomocí tohoto čísla je prováděno řazení. V případě, že vyhledáváme ve dvou sloupcích, je celková relevance pro řazení sčítána a opět jsou výsledky dle této výsledné relevance řazeny.

Pokud bychom chtěli výsledky seřadit tak, aby byl přikládán větší důraz na sloupec „nazev“ než na sloupec „text“, můžeme jednoduše vytvořit vlastní řazení a toto číslo relevance násobit:

V tomto případě bude brán větší důraz na relevanci prvního sloupce než na relevanci sloupce druhého.

Výhoda fulltextu spočívá v tom, že umí vyhledávat jak jednoduché fráze, tak i složitější spojení. Proto není problém volat dotaz ve tvaru:

V takovém případě nehledá databáze pouze celé fráze, ale i části jednotlivých slov a jejich kombinaci.

Boolean mode

MySQL a jeho fulltextové vyhledávání umožňuje kromě klasického vyhledávání vyhledávání v boolean mode. Při tomto vyhledávání opět přibívají určité možnosti, jak můžete zoganizovat dotaz na databázi a tak vracet přesnější výsledky při vyhledávání.

Pro podrobnější popis těchto možností se podívejte přímo do manuálu na adrese manuálu MySQL

Kombinace vyhledávání

Fulltextové vyhledávání je sice zajímavé ale ne vždy použitelné a to právě díky jeho největšímu omezení v podobě nastavení hodnoty ft_min_word_len, která určuje, jak musí být hledané slovo minimálně dlouhé, aby bylo možné jej pomocí fulltextu vyhledat.

Na straně PHP můžeme tuto hodnotu zjistit zavoláním SQL dotazu na databázi:

Celkový počet výsledků vyhledávání

Další, co musíme při vyhledávání řešit je stránkování. Při stránkování musíme znát dvě věci. Celkový počet řádků, které nám databáze při volání našeho dotazu vrátí a počet řádků které chceme vypsat.

Pro omezení počtu řádků používáme operátor LIMIT, který bude na konci dotazu. Ovšem takový zápis dotazu nám nevrátí celkový počet řádek, ovlivněných dotazem. A právě pro tuto potřebu můžeme použít SQL_CALC_FOUND_ROWS, který dokáže tuto hodnotu zjistit.

Použití je velice jednoduché.

V dotazu, který provádíme na databázi a který je omezený LIMITEM použijeme tento operátor před názvem některého sloupce:

Tento dotaz vrací pouze 10 prvních řádků z vyhledávání omezeny právě limitem na konci dotazu.
Databázi jsme ale řekli aby navíc spočítala celkový počet řádků, které by dotaz bez limitu vráti pro sloupec „nazev“ a dalším jednoduchým dotazem můžeme tento počet zjistit:

V kombinaci s PHP by kód vypdal například takto:

Překlepy ve vyhledávání: soundex

Překlepy jsou problémem vyhledávání. Při překlepu v hledané frázi může nastat, že vyhledávání bude vracet nekorektní výsledky.

Překlepům sice nemůžeme předcházet, ale můžeme uživateli oznámit, že překlep zřejmě udělal a že při použití hledání jiné fráze může dostat lepší výsledky, tak jak to dělá například google.

Pro ošetření překlepů můžeme použít funkci soundex, která se vyskytuje buď v PHP nebo i přímo v MySQL.

Ukažme si její využití na příkladu. Vytvoříme si pole podobných slov a vypíšeme si jejich hodnoty soundexu:

Jako výsledek dostaneme:

Vydíte, že podobná slova, nebo slovní spojení, mají podbný soundex. Na tomto základu můžeme snadno omezit překlepy.

Museli bychom si udělat tabulku, ve které by se ukládali hledané fráze, soundexy a počet výsledků vyhledávání.

Po vyhledání bychom ověřili, kolik výsledků vyhledávání databáze vrátila a v případě že bychom uznali tuto hodnotu jako malou bychom mohli v naší tabulce vyhledat frází, která má stejný soundex a vrátíla větší počet výsledků.

Závěrem:

Problematika vyhledávání je velmi obsáhlá a udělat kvalitní vyhledávání na webu je mnohdy velmi složité a musíme volat obsáhlé dotazy na databázi. Při vyhledávání se musíme snažit uživateli vracet co možná nejlepší výsledky.

Pro vyhledávání je asi nejlepší použít fulltext, který nabízí přímo databáze, ale i tak není toto řešení využitelné vždy. V takových případech nastupují na řadu například regularní výrazy a složitě poskládané dotazy.