Lehký úvod do tvorby databázových aplikací v prostředí PostgreSQL a PHP3

Tento dokument obsahuje základní informace potřebné pro tvorbu databázových aplikací s WWW rozhraním. Jako databázový systém se používá jednoduchý SQL server PostgreSQL. Pro dynamické generování stránek z databáze se používá systém PHP3.

Na čtenáře nejsou kladeny žádné zvláštní nároky. Stačí, když ovládá jazyk HTML a základy práce se systémem PHP3. Znalost jazyka SQL není vůbec na škodu, ale není nezbytně nutná.

Předtím než začneme programovat

I když budeme vytvářet aplikaci s WWW-rozhraním, což dnes ještě nené zdaleka běžné, jedná se v jádru o klasickou databázovou aplikaci. Proto by při tvorně reálného systému neměla chybět fáze analýzy, kdy podrobně prostudujeme reálný systém, jehož obraz se bude v našem informačním systému (databázové aplikaci) odrážet.

Tato fáze vždy sestává z vytvoření datového modelu, který popisuje vztahy mezi jednotlivými entitami. Z tohoto modelu také vyčteme jaké tabulky budou v databázi, jaké budou primární klíče v jednotlivých tabulkách, jaké jsou vztahy mezi tabulkami, jaká platí integritní omezení atd.

Na rozebírání datového modelování zde není prostor. My si vše ukážeme na jednoduché úloze, kde si bez něj vystačíme. Pro složitější systémy je však důkladná analýza nezbytná.

Ukázková aplikace

Práci s PostgreSQL a PHP3 si ukážeme na jednoduchém příkladě. Vytvoříme jednoduchý adresář, který bude sloužit k uchovávání jmen, e-mailových adres a dat narození kamarádů. Aplikace nám umožní adresář prohledávat a prohlížet, přidávat do něj nové údaje, opravovat údaje stávající a neaktuální údaje mazat.

PostgreSQL

PostgreSQL je jednoduchý databázový SQL server. Poskytuje však dostatečné prostředky pro tvorbu naší aplikace. Navíc je přimo podporován systémem PHP3, což se nám bude za chvíli hodit.

PostgreSQL umožňuje na jednom počítači pracovat s více databázemi. Každá databáze pak může obsahovat několik tabulek, které mohou, ale nemusí, být navzájem provázány. Výpis všech databází uložených v systému získáme pomocí příkazu:

psql -l
Každý máte k dispozici databázi, jejíž jméno odpovídá vašemu uživatelskému jménu. Pokud se chceme podívat, jaké tabulky databáze obsahuje, použijeme příkaz:
psql jméno_databáze -c \\d
K samotné práci s databází slouží příkaz psql. Po jeho spuštění můžeme pomocí příkazové řádky zadávat SQL příkazy, které se mají provést. Jako parametr programu se zadává jméno databáze, se kterou chceme pracovat. Příkazovou řádku programu ukončíme pomocí příkazu \q.

Vytvoření nové tabulky

Nyní si ukážeme vytvoření naší ukázkové tabulky. Nejprve spustíme prostředí PostgreSQL příkazem:
psql jméno_databáze
K vytvoření nové tabulky v databázi slouží SQL příkaz create table. Jeho syntaxe je následující:
create table jméno_tabulky (jméno_položky typ_položky, ... )
Přehled nejběžnějších datových typů přináší tabulka.

Vybrané datové typy MySQL
TypPopis
intcelé číslo
floatčíslo s pohyblivou řádovou čárkou
varchar(n)textový řetězec o maximální délce n
datedatum ve tvaru RRRR-MM-DD
timečas ve tvaru HH:MM:SS

My vytvoříme tabulku adresar se čtyřmi položkami jméno, příjmení, e-mail a datum narození.

create table adresar (jmeno varchar(15), prijmeni varchar(15), 
                      email varchar(40), narozen date);
Důležitý je středník na konci, kterým se odesílají příkazy PostgreSQL k provedení.

O tom, že se tabulka skutečně vytvořila, se můžeme přesvědčit pomocí příkazu \d zadaného v prostředí programu psql.

Přidání nového záznamu do tabulky

Přímo v programu psql můžeme do databáze přidávat i nové záznamy. K přidání nového záznamu do tabulky s N položkami slouží v SQL příkaz insert into:
insert into jméno_tabulky values (hodnota1, ..., hodnotaN)
My do tabulky přidáme informace o panu Novákovi:
insert into adresar values ('Jan', 'Novák', 
                            'Jan.Novak@mail.cz', '1965-08-25');
Obdobným způsbem můžeme přidat i další záznamy. Vidíme, že tento způsob není zrovna dvakrát uživatelsky příjemný -- dobrá motivace pro vytvoření snadno ovladatelného WWW rozhraní k tabulce.

Výběr a prohlížení záznamů v tabulce

K výběru a vypsání záznamů z tabulky slouží příkaz select. Pokud chceme vypsat obsah celé tabulky, zadáme příkaz:
select * from jméno_tabulky
Obsah naší tabulky si tedy můžeme ověřit pomocí:
select * from adresar;
Informace lze vybírat i selektivně. Podmínka, která musí pro vybrané záznamy platit, se uvádí za klíčové slovo where. Příklady selektivních dotazů:
select * from adresar where prijmeni like 'Novák';
select * from adresar where email = 'jkj@sorry.vse.cz';
select * from adresar where prijmeni = 'Procházka' and jmeno = 'Karel';
select * from adresar where prijmeni like 'Pro%';
Znak '%' má ve vyhledávacím řetězci za operátorem like speciální význam -- nahrazuje libovolnou sekvenci znaků.

Rušení záznamů v tabulce

K rušení záznamů v tabulce slouží příkaz delete from. Záznamy, které chceme smazat, musíme určit podmínkou v klauzuli where:
delete from jméno_tabulky where podmínka
Aby byl záznam, který chceme smazat, jednoznačně identifikován, je v podmínce vhodné provést test pro primární klíč. Pokud v tabulce není primární klíč definován (jako např. V té naší) můžeme použít primární klíč, který vytváří PostgreSQL automaticky. Jméno atributu s tímto číselným kódem je OID (Object Identifier). OID jednotlivých záznamů můžeme vypsat pomocí příkazu:
select oid, * from adresar;
Praktická ukázka smazání záznamu, jehož identifikační číslo je 24003:
delete from adresar where oid=24003;

Změna záznamu v tabulce

Ke změně záznamu slouží příkaz update:
update jméno_tabulky set položka=hodnota, ... where podmínka
Ten modifikuje všechny záznamy, které vyhovují podmínce. Pokud chceme měnit pouze jeden záznam -- to je ostatně nejtypičtější příklad -- je vhodné jako podmínku uvést test primárního klíče (nebo OID) na jedinečnou hodnotu:
update adresar set email="novak@ini.cz" where oid=18937;
Příkaz změní mailovou adresu uživatele, který je v adresáři veden pod identifikačním číslem 18937.

Smazání celé tabulky

Pokud se nám nějaká tabulka znelíbí, můžeme ji i s jejím obsahem smazat pomocí příkazu:
drop table jméno_tabulky
Dejte si na tuto operaci pozor. Tabulka je smazána nenávratně.

Přístupová práva k tabulce

PostgreSQL umožňuje nastavování přístupových práv pomocí standardního SQL-příkazu grant. Aby nám správně fungovaly všechny následující ukázky spolupráce PHP s databází, povolíme všechny operace v tabulce všem uživatelům. V praxi samozřejmě musíme pro zabezpečení neoprávněného přístupu k datům udělat maximum.
grant all on jméno_tabulky to public;

PHP3

V PHP3 se pro komunikaci s databází MySQL používají funkce začínající na pg. Před každou komunikací se serverem je potřeba se k němu připojit a po skončení práce se zase odpojit. Typické schéma práce s PostgreSQL v PHP3 tedy vypadá takto:
<? $conn = pg_Connect("localhost", "", "", "", "jméno_databáze")?>

    práce s databází

<? pg_Close($conn)?>
$conn je proměnná, která identifikuje spojení vytvořené se serverem.

Výpis záznamů z tabulky

Abychom mohli vypsat záznamy z tabulky, musíme je nejprve vybrat pomocí SQL příkazu select. V PHP máme k dispozici funkci pg_exec(SQL_příkaz), která jako výsledek vrací výsledek SQL příkazu.

V případě příkazu select je výsledkem seznam záznamů. Funkce v tomto případě vrací ukazatel na seznam záznamů. Tyto záznamy jsou pak dále přístupné pomocí dalších funkcí. Příklad dotazu v PHP:

<?
    $result = pg_exec("select * from adresar");
?>
Počet záznamů, které jsou výsledkem posledního dotazu, můžeme zjistit pomocí funkce pg_NumRows($result). Poněkud ucelenější ukázka:
<?
    $conn = pg_Connect("localhost", "", "", "", "test");
    $result = pg_exec("select * from adresar");
    $pocet = pg_NumRows($result);
    echo "V tabulce adresar je $pocet záznamů.";
    pg_Close($conn);
?>
Jednotlivé položky záznamů, které jsou výsledkem dotazu, jsou přístupné pomocí funkce pg_Result(). Ta má tři parametry:
pg_Result($result, číslo_záznamu, položka)
Poznamenejme, že záznamy jsou číslovány od 0. Pokud bychom tedy chtěli vypsat obsah celé tabulky adresar, můžeme na stránku zařadit následující kód:
<?
    $conn = pg_Connect("localhost", "", "", "", "test");
    $result = pg_exec("select * from adresar");
    $pocet = pg_NumRows($result);
    echo "V tabulce adresar je $pocet záznamů.";
    echo "<P>";
    $i = 0;
    while ($i<$pocet):
        echo pg_Result($result, $i, "jmeno") . " " .
             pg_Result($result, $i, "prijmeni") . ", " .
             pg_Result($result, $i, "email") . ", " .
             pg_Result($result, $i, "narozen") . "<BR>";
        $i++;
    endwhile;
    pg_Close($conn);
?>
Ve smyčce bychom mohli kolem dat z databáze umístit například HTML tagy pro vytvoření tabulky a výsledky tak prezentovat v přehledné tabulce.

Pokud již nepotřebujeme s výsledkem dotazu dále pracovat, je vhodné uvolnit paměť, ve které je výsledek uložen. To provedeme voláním funkce pg_FreeResult($result).

Pokud výsledkem volání funkce pg_exec() není seznam záznamů (např. po SQL příkazech update, delete nebo insert), vrací funkce true. Pokud při provádění SQL příkazu došlo k chybě, vrátí funkce hodnotu false.

Počet záznamů, kterých se dotklo provedení operace update, delete nebo insert, vrací funkce pg_Affected_Rows().

Přidání nového záznamu do tabulky

Předpokládejme, že v proměnných $jmeno, $prijmeni, $email a $narozen máme uloženy informace o člověku, kterého chceme do adresáře přidat. Do proměnných se údaje mohly dostat například z formuláře, který vyplnil uživatel. Stránka, která tento formulář obsluhuje, pak bude obsahovat tento kód pro přidání nového záznamu do tabulky:
<?
    $conn = pg_Connect("localhost", "", "", "", "test");
    $result = pg_exec("insert into adresar values ('$jmeno', '$prijmeni', " +
	              "'$email', '$narozen')");
    if ($result):
        echo "Do adresáře byl úspěšně přidán nový záznam.";
    else:
        echo "A sakra! Nový záznam se do adresáře nepodařilo přidat.";
    endif;
    pg_Close($conn);
?>    

Zrušení záznámu v tabulce

Předpokládejme, že identifikační číslo záznamu, který chceme smazat, máme uloženo v proměnné $oid. Vymazání pak v PHP provedeme takto:
<?
    $conn = pg_Connect("localhost", "", "", "", "test");
    $result = pg_exec("delete from adresar where oid=$oid");
    if ($result):
        echo "Záznam se podařilo úspěšně vymazat.";
    else:
        echo "Záznam nelze vymazat, do povrchu pevného disku je vyryt.";
    endif;
    pg_Close($conn);
?>    

Změna záznámu v tabulce

Předpokládejme, že u člověka, jehož $oid známe, chceme změnit e-mailovou adresu na hodnotu proměnné $email:
<?
    $conn = pg_Connect("localhost", "", "", "", "test");
    $result = pg_exec("update adresar set email='$email' where oid=$oid");
    if ($result):
        echo "Záznam se podařilo úspěšně aktualizovat.";
    else:
        echo "Záznam nelze aktualizovat, do povrchu pevného disku je vyryt.";
    endif;
    pg_Close($conn);
?>    

© Jirka Kosek, 1998