Filtrovanie dát v tabuľke cez SQL
V tomto semestri si postupne ukazujeme praktické využitie jazyka SQL a všeobecne využitie databáz v informačných systémoch a inak tomu nebude ani na tejto prednáške. Po tom, ako sme si ukázali, ako dáta z databázy načítať a použiť v kóde jazyka PHP a takisto aj ako dáta zachytené, resp. nachádzajúce sa v kóde uložiť do databázy, si ukážeme, ako dáta na stránke filtrovať a vyhľadávať.
S nasledujúcou funkcionalitou (vymoženosťou), ktorú si ideme na našej stránke zaviesť, sa takisto stretávate denne počas surfovania. Či už hľadáte niečo na nete alebo konkrétne na nejakej stránke, vo väčšine zadávate nejaké kľúčové slová (keywords), ktoré popisujú to, čo sa snažíte nájsť. Samotný proces vyhľadávania, teda hľadania vami zadaného kľúčového slova na stránke, neznamená nič iné, ako použitie klauzuly WHERE pri príkaze SELECT v jazyku SQL. Okrem vyhľadávania využíva túto kombináciu (select - where) aj proces filtrovania dát v nejakých zoznamoch alebo výpisoch.
Naša tabuľka používateľov postupne naberá na množstve a kľudne by mohla obsahovať stovky alebo tisíce záznamov. Čo ale v prípade, že chceme nájsť nejakého konkrétneho používateľa? Napríklad podľa mena? V takomto prípade zvyknú takéto tabuľky alebo zoznamy niečoho obsahovať filtre, čo sú v podstate formuláre, do ktorých vpisujeme nami hľadaný výraz a po kliknutí na tlačítko filtruj sa nám zobrazia iba záznamy, ktoré obsahujú nami zadaný výraz.
Takýto filter si teraz spravíme aj my. Teoreticky by ste to mohli zvládnuť aj sami, viete pracovať s formulármi a parametrami a aj príkaz v SQL by ste zvládli. Čo teda ideme robiť? Nad tabuľkou si spravíme formulár, ktorý bude obsahovať napríklad jeden textový input, do ktorého bude používateľ zadávať hľadané meno alebo priezvisko. Vedľa neho bude tlačítko filtruj a po kliknutí naň sa stránka refrešne a v tabuľke bude vypísaný iba daný záznam alebo hláška, že sa žiadny záznam nenašiel.
Poďme teda na to. Začneme ako vždy iba s funkcionalitou, s dizajnom sa teraz nebudeme zbytočne zaoberať. Nad tabuľku si pridáme formulár s dvomi prvkami a v kóde spracujeme odoslanie formuláru. Keďže máme na stránke dva formuláre, práve teraz nám príde vhod testovanie, ktorý formulár sa odoslal (podľa name parametra na submit tlačítku). V spracovaní sa spýtame, či nám prišiel nejaké parameter a ak áno, tak ho pošleme v klauzule WHERE do príkazu na vytiahnutie dát z tabuľky. Mohlo by to vyzerať takto:
index.php (pridaný kód za formulár pre pridanie nového záznamu):
<?php
//SPRACOVANIE FILTROVANIA
$search_keyword = "";
if($_SERVER['REQUEST_METHOD'] == 'GET' && $_GET['search_form']){
$search_keyword = $_GET['search_keyword'];
}
$sql_query = "SELECT * FROM users";
if($search_keyword){
$sql_query .= " WHERE user_name ='".$search_keyword."' OR user_surname = '".$search_keyword."'";
}
$result = mysqli_query($connection, $sql_query);
echo '<h2>Zoznam používateľov</h2>';
?>
<!-- Formular na filtrovanie usera -->
<form class="contact_form" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>" method="get">
<label for="search_keyword">Meno</label>
<input type="text" name="search_keyword" id="search_keyword" value="<?php echo $search_keyword;?>">
<input type="submit" name="search_form" value="Filtruj">
</form>
<br>
Ako vidíte v kóde, najprv sme si urobili spracovanie formulára pre vyhľadávanie, do premennej search_keyword (hľadané kľúčové slovo) si vkladáme hľadaný parameter. Všimnite si dve veci, prvá je, že sme metódu tohto formulára zmenili na GET. Spravili sme to preto, pretože vyhľadávanie sa vo väčšine dáva ako GET, aby hľadaný parameter bol viditeľný v URL adrese a teda sa v nej zachoval. V prípade preposielania URL aj s konkrétnym výsledkom vyhľadávania sa tým pádom zachová. Ak by ste niečo hľadali a chceli ten istý výsledok poslať niekomu, dostal by po zavolaní danej url rovnaký výsledok (filtrovanie).
Druhá vec je, že sme podmienku, či boli nájdené nejaké dáta (if (mysqli_num_rows($result) > 0) {) posunuli nižšie, aby sa nám v prípade žiadneho nájdeného výsledku zobrazil nadpis Zoznam používateľov a pod ním formulár na vyhľadávanie. Ako vidíte, podľa toho, či nám prišiel nejaký parameter na vyhľadávanie (if($search_keyword){) pridávame do query na SELECT dát klauzulu WHERE. Všimnite si, že sme použili podmienku OR, takže nami zadané meno sa vyhľadáva aj ako v menách, tak aj v priezviskách. Všimnite si, že sme museli zakomponovať aj úvodzovky, keďže vyhľadávame string.
Vyhľadávanie naozaj funguje, skúste si zadať nejaké existujúce mená a priezviská (Harper, Miro...), skutočne sa vypíšu iba tie, ktoré sa zhodujú s hľadanou frázou. Šikovné nie? Ak chceme zobraziť všetky záznamy, jednoducho vymažeme hľadanú frázu a dáme hľadať (vráti nám všetko bez WHERE klauzuly) alebo klikneme v menu na domov, čím sa stránka načíta bez odoslania formulára. Refreš nám v tomto prípade nebude fungovať, pretože ako vieme, pri odoslaní formulára a pokuse o refreš alebo o vrátenie sa späť v prehliadači nás browser upozorní, že prepošle znova formulár.
Aby bolo naše hľadanie ešte dokonalejšie, naučíme sa novú vec z jazyka SQL. Ako ste si všimli, toto naše vyhľadávanie funguje iba v prípade, že zadáme presnú hodnotu, ktorá sa nachádza v tabuľke (meno alebo priezvisko). Z praxe ste ale zvyknutí, že vo väčšine nemusíte zadávať presne nejakú hodnotu, resp. kľúčové slovo, ale stačí zadať iba nejakú jeho menšiu časť. Ak by sme napríklad zadali len slovo ''en'', tak by sme chceli, aby nám filtrovanie vrátilo všetky záznamy, ktoré obsahujú tento reťazec (substring – menší string - časť stringu nachádzajúca sa v stringu/hodnote). V našom prípade by to bolo aj Zdeno a aj Lenka.
Presne na takéto filtrovanie pomocou klauzuly WHERE, kde v podmienke nie je vzťah, že sa zadaná hodnota musí zhodovať s nejakým stĺpcom (=), slúži v jazyku SQL príkaz LIKE (ako). Tento príkaz hovorí, nájdi všetky záznamy, ktorých hodnota stĺpcov je ako zadaná hodnota, v našom prípade napríklad to ''en''. V hodnote, ktorú chceme vyhľadávať, môžeme zadať znak percenta (%), ktorý má podobnú funkciu ako bodka v regulárnych výrazoch, značí , že sa na danom mieste môže nachádzať akýkoľvek znak.
Čiže výraz vo WHERE klauzule name LIKE '%en%' vyhľadá tie záznamy, ktorých hodnota obsahuje niečo na začiatku, potom dva znaky e a n a následne za tým tiež niečo môže byť. Ak by sme zadali iba en%, tak by to vyhľadalo len slová, ktoré sa začínajú na písmená en, podobne keby sme mali %en, tak by to boli len hodnoty, ktoré končia na en.
Tento príkaz sa využíva veľmi často najmä pri vyhľadávaniach a filtrovaniach. Na druhej strane treba s ním narábať opatrne, pretože toto vyhľadávanie dosť spomaluje načítanie dát z databázy. Pri veľkom počte dát a zadávaní tejto podmienky na príliš veľa stĺpcov zapríčiňuje príliš veľa krokov pre databázový server na porovnávanie (či niečo obsahuje to a to). V našom prípade to ale zatiaľ nebude vadiť. Naša podmienka v príkaze SELECT by teda mohla vyzerať takto:
index.php (iba upravená WHERE klauzula):
…
$sql_query .= " WHERE user_name LIKE '%".$search_keyword."%' OR user_surname LIKE '%".$search_keyword."%'";
...
Keď si teraz zadáte hľadanie napríklad výrazov ''en'' alebo ''ar'', hodí vám záznamy, ktorých hodnota meno alebo priezvisko obsahuje práve tento reťazec znakov. Milé nie? Týmto je už naše filtrovanie dát v tabuľke dokonané, pomocou nášho filtra vieme nájsť všetko, čo poterbujeme:) Tento systém by sa dal kľudne využiť aj tak, že ak by sme mali napríklad stránku, ktorá obsahuje články a každé z nich by malo viacero kľúčových slov, ktoré článok popisujú, mohla by byť táto podmienka aplikovaná na všetky slová a nadpis článku. Takto by sme mohli do webstránky zakomponovať vyhľadávanie vo všetkých stránkach. Prípadne by sme ešte hľadanú frázu mohli skloňovať (upravovať ako string), ale to je už náročnejší mechanizmus, kľudne by postačilo aj takéto jednoduché hľadanie.