Bazy danych – Alfabet: wybieranie rekordów na zadaną literę

W przypadku dużej ilości rekordów w bazie danych interfejs witryny możemy wzbogacić o alfabet. Wybierając jedną z liter alfabetu, użytkownik ujrzy wyłącznie rekordy rozpoczynające się na daną literę. W artykule opiszę metodę implementacji takiego alfabetu.

Działanie alfabetu

Jak działa alfabet? Na stronie prezentującej wybraną kategorię rekordów umieszczamy litery alfabetu. Na przykład na witrynie publikującej artykuły alfabet umieszczamy na podstronie prezentującej nazwiska wszystkich autorów.

Rys. 1 przedstawia podstronę, która zawiera nazwiska wszystkich autorów zawartych w bazie danych. Tabela jest skrócona do pierwszych 18 pozycji (pełna lista autorów liczy 136 nazwisk). Powyżej tabeli został umieszczony alfabet.

Litery alfabetu widoczne na rysunku są hiperłączami. Alfabet nie jest pełny. Zawiera wyłącznie te litery, na które faktycznie rozpoczyna się co najmniej jedno z nazwisk. Pominięta jest na przykład litera E, gdyż baza danych nie zawiera żadnego autora o nazwisku rozpoczynającym się na E.

Po wybraniu jednej z liter na ekranie pojawia się strona prezentująca tylko część rekordów z bazy danych. Na rys. 2 jest widoczna witryna, którą ujrzy użytkownik po wybraniu z alfabetu litery G. Witryna ta zawiera wyłącznie nazwiska rozpoczynające się na G. Jest ich 10.

Zapytania SQL

Pierwszym etapem pracy nad alfabetem jest ustalenie zapytań SQL odpowiedzialnych za pobranie odpowiednich danych z bazy. Potrzebne będą trzy zapytania:

• ustalenie listy wszystkich liter, na jakie rozpoczynają się rekordy,
• sprawdzenie, czy na zadaną literę rozpoczyna się choć jedno nazwisko (walidacja),
• wyznaczenie listy rekordów rozpoczynających się na zadaną literę.

Lista wszystkich liter

Pierwsze zapytanie jest następujące:

SELECT DISTINCT
    ORD(LEFT(nazwisko, 1)) as kod,
    LEFT(nazwisko, 1) as litera
FROM
    tosoba
ORDER BY
    nazwisko, imie

W wyniku otrzymamy listę par kod-litera:

65 A
66 B
67 C
…

Lista ta będzie zawierała wszystkie litery, na jakie rozpoczynają się nazwiska zapisane w tabeli tosoba. W zapytaniu wykorzystane są funkcje LEFT (pobranie pierwszej litery z napisu) oraz ORD (ustalenie kodu pojedynczego znaku).

Walidacja podanej litery

Sprawdzenie poprawności litery wykonamy zapytaniem:

SELECT
    COUNT(tosoba_id)
FROM
    tosoba
WHERE
    ORD(LEFT(nazwisko, 1)) = 65

Wynikiem zwracanym przez zapytanie jest liczba rekordów rozpoczynających się na literę o kodzie 65 (czyli na literę A).

Lista rekordów rozpoczynających się na wybraną literę

Oto zapytanie ustalające listę osób, których nazwiska rozpoczynają się na literę A (kod: 65):

SELECT
    tosoba_id,
    imie,
    nazwisko,
    liczbaartykulow,
    liczbastron
FROM
    tosoba
WHERE
    ORD(LEFT(nazwisko, 1)) = 65
ORDER BY
    nazwisko, imie

Funkcje dostępu do danych

Po ustaleniu zapytań SQL przechodzimy do implementacji funkcji pobierających dane z bazy. Funkcje takie implementujemy jako metody klasy DBA. Klasa ta wykorzystuje do komunikacji z bazą danych klasę {stala}PEAR::DB{/stala}, co znacznie upraszcza implementację.
Funkcja {stala}DBA_alfabet_osob(){/stala}, przedstawiona na listingu 1,zwraca w wyniku dwuwymiarową tablicę zawierającą zestaw wszystkich par: kod-litera.

public function DBA_alfabet_osob()
{
    $q = \'
        SELECT DISTINCT
            ORD(LEFT(nazwisko, 1)) as kod,
            LEFT(nazwisko, 1) as litera
        FROM
            tosoba
        ORDER BY
            nazwisko, imie
    \';

    $w = $this->Fdb->getAll($q);

    if (DB::isError($w)) {
        die(__LINE__ . \' \' . $w->getMessage());
    } else {
        return $w;
    }
}

Funkcja {stala}DBA_poprawny_kod_litery_alfabetu_osob(){/stala} zawiera jeden parametr: $AKod. Parametr jest kodem sprawdzanej litry. Na przykład badając ile nazwisk w bazie danych rozpoczyna się na A, podajemy jako parametr liczbę 65. Ponieważ wykorzystujemy klasę PEAR::DB, w zapytaniu SQL pojawia się – w miejscu danej – znak zapytania. Znak zapytania jest zamieniany na podany parametr $AKod wywołaniami:

$t = array($AKod);
$w = $this->Fdb->getOne($q, $t);

Wynikiem działania funkcji jest liczba nazwisk zawarta w tabeli tosoba, które rozpoczynają się na literę o zadanym kodzie. Jeśli wynik ten jest większy od 1, to podany kod jest poprawny – listing 2

function DBA_poprawny_kod_litery_alfabetu_osob($AKod)
{
    $q = \'
        SELECT
            COUNT(tosoba_id)
        FROM
            tosoba
        WHERE
            ORD(LEFT(nazwisko, 1)) = ?
    \';

    $t = array($AKod);
    $w = $this->Fdb->getOne($q, $t);

    if (DB::isError($w)) {
        die(__LINE__ . \' \' . $w->getMessage());
    } else {

        /*
         * $w jest zawsze napisem!
         *
         */
        if (str_ievpi($w)) {
            return ($w > 0);
        } else {
            return false;
        }
    }
}

Funkcja {stala}DBA_podaj_osoby_na_litere(){/stala} ustala dane osób, których nazwiska rozpoczynają się na wybraną literę. Literę zadajemy – identycznie jak poprzednio – podając w parametrze $AKod jej kod ASCII. Zapytanie SQL zawiera znak zapytania zastępowany na konkretną daną poprzez {stala}funkcję getAll(){/stala} – listing 3.

public function DBA_podaj_osoby_na_litere($AKod)
{
    $q = \'
        SELECT
            tosoba_id,
            imie,
            nazwisko,
            liczbaartykulow,
            liczbastron
        FROM
            tosoba
        WHERE
            ORD(LEFT(nazwisko, 1)) = ?
        ORDER BY
            nazwisko, imie
    \';

    $t = array($AKod);
    $w = $this->Fdb->getAll($q, $t);

    if (DB::isError($w)) {
        die(__LINE__ . \' \' . $w->getMessage());
    } else {
        return $w;
    }
}

Przekazanie danych do szablonu

Kolejnym etapem dodawania alfabetu do aplikacji jest wydanie zapytań SQL i przekazanie wyników do szablonu. Przyjrzyjmy się realizacji podstrony wszystkich autorów.
Na podstronie tej musimy wyświetlić listę zawierającą wszystkie nazwiska. Najpierw do zmiennej $osoby pobieramy z bazy danych pełną listę osób:

$osoby = $db->DBA_podaj_wszystkie_osoby($sortowanie);

Następnie zmienną $osoby przekazujemy do szablonu:

$s->assign(\'osoby\', $osoby);

W celu wyświetlenia na stronie alfabetu pobieramy z bazy danych listę wszystkich liter rozpoczynających nazwiska:

$alfabet = $db->DBA_alfabet_osob();

Dane zawarte w zmiennej $alfabet przekazujemy do szablonu:

$s->assign(\'alfabet\', $alfabet);

Pełny kod podstrony wszystkich autorów jest widoczny na listingu 4.

//Podstrona: Wszystkie osoby
case 3:
    $osoby = $db->DBA_podaj_wszystkie_osoby($sortowanie);
    $s->assign(\'osoby\', $osoby);
    $alfabet = $db->DBA_alfabet_osob();
    $s->assign(\'alfabet\', $alfabet);
    break;

Adresy URL

Podstrony wyświetlające rekordy rozpoczynające się na wybraną literę zawierają dodatkowy parametr w adresie URL. Lista autorów, których nazwiska rozpoczynają się na wybraną literę jest widoczna pod adresem:

index.php?id=15&id2=XX

gdzie XX oznacza kod litery. Zatem pod adresem:

index.php?id=15&id2=65

dostępna jest lista nazwisk rozpoczynających się na A, zaś odwiedzając adres:

index.php?id=15&id2=90

poznamy zestawienie autorów o nazwiskach na literę Z (kodem litery Z jest 90).
Pełne zestawienie osób jest dostępne na stronie:

index.php?id=3

Ponieważ na stronie tej wyświetlamy wszystkie nazwiska, żadna dodatkowa zmienna URL nie jest potrzebna.

Walidacja zmiennej zawierającej kod alfabetu

Przeprowadzając pełną walidację zmiennych zawartych w zapytaniu HTTP, nie możemy zapomnieć o zmiennej id2 zawierającej kod wybranej litery alfabetu.
Sprawdzając zmienną id2 należy zbadać:

• czy jest ona podana,
• czy jest poprawną liczbą całkowitą,
• czy na podaną literę rozpoczyna się w bazie danych co najmniej jedno nazwisko.

Pełny kod walidacji zmiennej id2 jest przedstawiony na listingu 5 .

if (
    isset($_GET[\'id2\']) &&
    str_ievpi($_GET[\'id2\']) &&
    $db->DBA_poprawny_kod_litery_alfabetu_osob($_GET[\'id2\'])
) {
    $akcja = 15;
}

Wykorzystanie danych w szablonie

Ostatnim krokiem dodawania do strony alfabetu jest modyfikacja szablonu. W szablonie należy odebrać tablicę $alfabet i na jej podstawie wydrukować alfabet w postaci hiperłączy.
Tablica {stala}$alfabet{/stala} nie jest wykorzystywana w szablonie głównym index.tpl. Jest ona przekazana do szablonu alfabet-autorzy.tpl dołączonego funkcją include, co widać na listingu 6.

...
{elseif $akcja == 3}
    {include file=\"alfabet-autorzy.tpl\"}
    
WSZYSCY AUTORZY
    {include file=\"autorzy-wszyscy.tpl\"}
{elseif $akcja == 4}
...

W szablonie {stala}alfabet-autorzy.tpl{/stala}, przedstawionym na listingu 7, występuje jedna pętla section, która generuje cały alfabet.

{section name=alf loop=$alfabet}
{$alfabet[alf].litera}
{/section}

Podstrona z rekordami na wybraną literę

Lista osób na wybraną literę jest dostępna pod adresami:

index.php?id=15&id2=XX

W skrypcie index.php należy zatem w przypadku 15 ustalić:

• dane osób na wybraną literę (zmienna $osoby),
• alfabet (zmienna $alfabet),
• kod wybranej litery (zmienna $kodlitery),
• wybraną literę (zmienna $litera).

Cztery zmienne $osoby, $alfabet, $kodlitery, $litera następnie przekazujemy do szablonu, co widać na listingu 8.

Rozwiązanie podane w przykładach jest dodatkowo wzbogacone o sortowanie nazwisk względem dowolnych kolumn. Porządek sortowania ustalany jest w zmiennej $sortowanie widocznej m.in. na listingu 8.

//Podstrona: osoby na wybraną literę
case 15:
    $osoby = $db->DBA_podaj_osoby_na_litere($_GET[\'id2\'], $sortowanie);
    $s->assign(\'osoby\', $osoby);
    $alfabet = $db->DBA_alfabet_osob();
    $s->assign(\'alfabet\', $alfabet);
    $s->assign(\'kodlitery\', $_GET[\'id2\']);
    $s->assign(\'litera\', pl_utf8_chr($_GET[\'id2\']));
    break;

Kodowanie utf-8

Aplikacja omówiona w artykule stosuje kodowanie utf-8. Zmianę kodowania z iso-8859-2 (lub z windows-1250) można wykonać w czterech krokach.
Skrypt SQL tworzący bazę danych rozpoczniemy od zapytania:

SET NAMES utf8 COLLATE utf8_polish_ci;

zaś samą bazę danych tworzymy zapytaniem:

CREATE DATABASE artykuly_mi DEFAULT CHARACTER SET utf8 COLLATE utf8_polish_ci;

Następnie konstruktor klasy DBA nawiązujący połączenie z bazą danych należy wzbogacić o wywołanie:

$this->Fdb->query(\'SET NAMES utf8 COLLATE utf8_polish_ci\');

Po trzecie dane przekazywane do zapytań (czyli do metod klasy DBA) konwertujemy z kodu iso-8859-2 (lub z windows-1250) do utf-8:

$tytul = pl_iso2utf8($tytul);

lub

$tytul = pl_win2utf8($tytul);

Funkcje konwertujące kodowanie polskich znaków, rozpoczynające się od przyrostka pl_, znajdziemy w pakiecie pl.inc.php.

Po czwarte zmieniamy kodowanie dokumentów HTML: