Lehký úvod do tvorby databázových aplikací v prostředí MySQL 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 MySQL. 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á. Potřebné znalosti lze získat např. v kurzu IT_360 -- Databáze. Zde se mimo jiné i důkladně seznámíte s jazykem SQL.

Ukázková aplikace

Práci s MySQL 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.

MySQL

MySQL 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.

MySQL 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:

mysqlshow

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:

mysqlshow jméno_databáze

K samotné práci s databází slouží příkaz mysql. 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 quit.

Vytvoření nové tabulky

Nyní si ukážeme vytvoření naší ukázkové tabulky. Nejprve spustíme prostředí MySQL příkazem:

mysql xaaabnn

xaaabnn je přitom naše uživatelské jméno a tedy i 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
Typ Popis
int celé číslo
float číslo s pohyblivou řádovou čárkou
varchar(n) textový řetězec o maximální délce n
date datum ve tvaru RRRR-MM-DD
time čas ve tvaru HH:MM:SS

Vybrané datové typy MySQL
Typ	Popis
`int`	celé číslo
`float`	číslo s pohyblivou řádovou čárkou
`varchar(n)`	textový řetězec o maximální délce `n`
`date`	datum 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í. Pro usnadnění dalších operací přidáme do tabulky i pátou položku, kterou bude jedinečné identifikační číslo záznamu (primární klíč). Toto číslo deklarujeme tak, že se bude u každého nového záznamu automaticky zvyšovat -- MySQL pohlídá, aby měl každý záznam toto číslo unikátní.

create table adresar (id int auto_increment primary key,
       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 MySQL k provedení.

O tom, že se tabulka skutečně vytvořila, se můžeme přesvědčit pomocí příkazu mysqldump xaaabnn (příkaz musí být zadán na příkazovou řádku shellu, ne v prostředí programu mysql).

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

Přímo v programu mysql 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 (0, 'Jan', 'Novák', 
                            'Jan.Novak@mail.cz', '1965-08-25');

Nula na místě id způsobí automatické generování jedinečného id. 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

Praktická ukázka smazání záznamu, jehož identifikační číslo je tři:

delete from adresar where id=3;

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 na jedinečnou hodnotu:

update adresar set email="novak@ini.cz" where id=37;

Příkaz změní mailovou adresu uživatele, který je v adresáři veden pod identifikačním číslem 37.

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ě.

PHP3

V PHP3 se pro komunikaci s databází MySQL používají funkce začínající na mysql. 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 MySQL v PHP3 tedy vypadá takto:

<? mysql_Connect("localhost")?>

    práce s databází

<? mysql_Close()?>

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 mysql(jméno_databáze, 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 = mysql("xaaabnn", "select * from adresar");
?>

Počet záznamů, které jsou výsledkem posledního dotazu, můžeme zjistit pomocí funkce mysql_NumRows($result). Poněkud ucelenější ukázka:

<?
    mysql_Connect("localhost");
    $result = mysql("xaaabnn", "select * from adresar");
    $pocet = mysql_NumRows($result);
    echo "V tabulce adresar je $pocet záznamů.";
    mysql_Close();
?>

Jednotlivé položky záznamů, které jsou výsledkem dotazu, jsou přístupné pomocí funkce mysql_Result(). Ta má tři parametry:

mysql_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:

<?
    mysql_Connect("localhost");
    $result = mysql("xaaabnn", "select * from adresar");
    $pocet = mysql_NumRows($result);
    echo "V tabulce adresar je $pocet záznamů.";
    echo "<P>";
    $i = 0;
    while ($i<$pocet):
        echo mysql_Result($result, $i, "jmeno") + " " +
             mysql_Result($result, $i, "prijmeni") + ", " +
             mysql_Result($result, $i, "email") + ", " +
             mysql_Result($result, $i, "narozen") + "<BR>";
        $i++;
    endwhile;
    mysql_Close();
?>

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 mysql_FreeResult($result).

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

Počet záznamů, kterých se dotklo provedení operace update, delete nebo insert, vrací funkce mysql_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:

<?
    mysql_Connect("localhost");
    $result = mysql("xaaabnn", 
              "insert into adresar values (0, '$jmeno', '$prijmeni', " +
              "'$email', '$narozen')");
    if ($result==0):
        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;
    mysql_Close();
?>

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é $id. Vymazání pak v PHP provedeme takto:

<?
    mysql_Connect("localhost");
    $result = mysql("xaaabnn", "delete from adresar where id=$id");
    if ($result==0):
        echo "Záznam se podařilo úspěšně vymazat.";
    else:
        echo "Záznam nelze vymazat, do povrchu pevného disku je vyryt.";
    endif;
    mysql_Close();
?>

Změna záznámu v tabulce

Předpokládejme, že u člověka, jehož $id známe, chceme změnit e-mailovou adresu na hodnotu proměnné $email:

<?
    mysql_Connect("localhost");
    $result = mysql("xaaabnn", 
                    "update adresar set email='$email' where id=$id");
    if ($result==0):
        echo "Záznam se podařilo úspěšně aktualizovat.";
    else:
        echo "Záznam nelze aktualizovat, do povrchu pevného disku je vyryt.";
    endif;
    mysql_Close();
?>

Aplikace "adresar"

Nyní toho již víme dost, abychom mohli pomocí několika .phtml stránek implementovat aplikaci pro práci s adresářem.

index.phtml

Hlavní stránka celého adresáře umožňuje do formuláře zadat filtr, který určuje záznamy, které se mají zobrazit. O zobrazování záznamů se stará stejná stránka a proto formulář obsahuje odkaz znovu na stránku index.phtml.

Ve filtru můžeme používat hvězdičku pro nahrazení libovolné sekvence znaků. V PHP pak všechny hvězdičky převedeme na znak '%', který se používá v SQL pro zastoupení libovolné skupiny znaků.

Když generujeme výpis adresáře, přidají se ke každému záznamu dva odkazy, které vedou na stránky zajišťující změnu a smazání záznamu. Odkazy mají v sobě rovnou zakomponováno id záznamu, kterého se operace týká.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
    <TITLE>Adresar - Uvodni stranka</TITLE>
</HEAD>
<BODY>
<H1>Adresar - vyhledavani</H1>

<H2>Zadejte filtr pro hledani v databazi:</H2>

<?
    /* Pocatecni prihlaseni ke strance ? */
    if ($jmeno=="" && $prijmeni=="" &&
        $email=="" && $narozen=="") { $firsttime="true"; };
    
    /* Inicializace filtru pro prazdna pole */
    if ($jmeno=="") { $jmeno="*"; };
    if ($prijmeni=="") { $prijmeni="*"; };
    if ($email=="") { $email="*"; };
    if ($narozen=="") { $narozen="*"; };
?>

<FORM ACTION=index.phtml METHOD=GET>
<TABLE>
<TR><TD>Jmeno:	<TD><INPUT TYPE=TEXT NAME=jmeno VALUE="<?echo $jmeno?>" SIZE=40>
<TR><TD>Prijmeni:<TD><INPUT TYPE=TEXT NAME=prijmeni VALUE="<?echo $prijmeni?>" SIZE=40>
<TR><TD>E-mail:	<TD><INPUT TYPE=TEXT NAME=email VALUE="<?echo $email?>" SIZE=40>
<TR><TD>Narozen:<TD><INPUT TYPE=TEXT NAME=narozen VALUE="<?echo $narozen?>" SIZE=10>
</TABLE>
<BR><INPUT TYPE=SUBMIT VALUE="Prohledej adresar">
</FORM>

<FORM ACTION=new.phtml METHOD=GET>
<INPUT TYPE=SUBMIT VALUE="Pridej novou adresu">
</FORM>

<?
    if ($firsttime!="true"):
?>

<HR>
<H2>Vysledky prohledavani</H2>

<TABLE>
<TR><TH>Jmeno<TH>Email<TH>Datum narozeni<TH>Uprava<TH>Smazani
<?
	mysql_Connect("localhost");
	/* prevod * na % pro operator like */
         $_jmeno = ereg_replace("\*", "%", $jmeno);
         $_prijmeni = ereg_replace("\*", "%", $prijmeni);
         $_email = ereg_replace("\*", "%", $email);
         $_narozen = ereg_replace("\*", "%", $narozen);
	$result = mysql("jkj", "select * from adresar where " +
		"jmeno like '$_jmeno' and prijmeni like '$_prijmeni' and " +
		"email like '$_email' and narozen like '$_narozen'");
	$num = mysql_NumRows($result);
	if ($num==0):
	    echo "<TR><TH COLSPAN=5>Dotazu nevyhovuje zadna adresa!\n";
	else:
	    $i = 0;
	    while ($i<$num):
		echo "<TR><TD>" + mysql_Result($result, $i, "jmeno") + " " +
		     mysql_Result($result, $i, "prijmeni");
	 	echo "<TD>" + mysql_Result($result, $i, "email");
	 	echo "<TD>" + mysql_Result($result, $i, "narozen");
	 	echo "<TD><A HREF='edit.phtml?id=" + 
		     mysql_Result($result, $i, "id") + "'>Uprav</A>";
	 	echo "<TD><A HREF='delete.phtml?id=" + 
		     mysql_Result($result, $i, "id") + "'>Smaz</A>";
		$i++;
	    endwhile;
	endif;
	mysql_Close();
?>
</TABLE>

<?
    endif;
?>

</BODY>
</HTML>

new.phtml

Tato stránka obsahuje formulář, který se po vyplnění pošle skriptu add.html -- ten se postará o samotné přidání záznamu do tabulky.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
    <TITLE>Adresar - Pridani nove adresy</TITLE>
</HEAD>
<BODY>
<H1>Adresar - pridani nove adresy</H1>

<H2>Zadejte novou adresu:</H2>

<FORM ACTION=add.phtml METHOD=GET>
<TABLE>
<TR><TD>Jmeno:	<TD><INPUT TYPE=TEXT NAME=jmeno SIZE=40>
<TR><TD>Prijmeni:<TD><INPUT TYPE=TEXT NAME=prijmeni SIZE=40>
<TR><TD>E-mail:	<TD><INPUT TYPE=TEXT NAME=email SIZE=40>
<TR><TD>Narozen:<TD><INPUT TYPE=TEXT NAME=narozen SIZE=10>
</TABLE>
<BR><INPUT TYPE=SUBMIT VALUE="Pridej adresu">
</FORM>

<FORM ACTION=index.phtml METHOD=GET>
<INPUT TYPE=SUBMIT VALUE="Zpet">
</FORM>

</BODY>
</HTML>

add.phtml

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
    <TITLE>Adresar - Vysledek pridani adresy</TITLE>
</HEAD>
<BODY>
<?
	mysql_Connect("localhost");
	$result = mysql("jkj", "insert into adresar values (0, '$jmeno'," +
		  "'$prijmeni', '$email', '$narozen')");
	if ($result==0):
	    echo "<H1>Nova adresa byla uspesne pridana</H1>";
	else:
	    echo "<H1>Novou adresu se nepodarilo pridat</H1>";
	endif;
	mysql_Close();
?>
<A HREF=index.phtml>Adresar</A>

</BODY>
</HTML>

delete.phtml

Smazání záznamu zadaného pomocí parametru id:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
    <TITLE>Adresar - Vysledek smazani adresy</TITLE>
</HEAD>
<BODY>
<?
	mysql_Connect("localhost");
	$result = mysql("jkj", "delete from adresar where id=$id");
	if ($result==0):
	    echo "<H1>Adresa byla uspesne smazana.</H1>";
	else:
	    echo "<H1>Adresu se nepodarilo smazat.</H1>";
	endif;
	mysql_Close();
?>
<A HREF=index.phtml>Adresar</A>

</BODY>
</HTML>

edit.phtml

Tato stránka obsahuje formulář se stávajícím obsahem záznamu s určitým id. Po úpravách se obsah formuláře odešle skriptu update.phtml, který se postará o změnu záznamu v tabulce.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
    <TITLE>Adresar - Zmena adresy</TITLE>
</HEAD>
<BODY>
<H1>Adresar - Zmena adresy</H1>

<H2>Opravte udaje v adrese:</H2>
<?
	mysql_Connect("localhost");
	$result = mysql("jkj", "select * from adresar where id='$id'");
	$jmeno = mysql_Result($result, 0, "jmeno");
	$prijmeni = mysql_Result($result, 0, "prijmeni");
	$email = mysql_Result($result, 0, "email");
	$narozen = mysql_Result($result, 0, "narozen");
	mysql_Close();
?>

<FORM ACTION=update.phtml METHOD=GET>
<TABLE>
<TR><TD>Jmeno:	<TD><INPUT TYPE=TEXT NAME=jmeno VALUE="<?echo $jmeno>" SIZE=40>
<TR><TD>Prijmeni:<TD><INPUT TYPE=TEXT NAME=prijmeni VALUE="<?echo $prijmeni>" SIZE=40>
<TR><TD>E-mail:	<TD><INPUT TYPE=TEXT NAME=email VALUE="<?echo $email>" SIZE=40>
<TR><TD>Narozen:<TD><INPUT TYPE=TEXT NAME=narozen VALUE="<?echo $narozen>" SIZE=10>
</TABLE>
<BR><INPUT TYPE=HIDDEN NAME=id VALUE=<?echo $id>>
<INPUT TYPE=SUBMIT VALUE="Proved zmenu udaju">
</FORM>

<FORM ACTION=index.phtml METHOD=GET>
<INPUT TYPE=SUBMIT VALUE="Zpet">
</FORM>

</BODY>
</HTML>

update.phtml

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
    <TITLE>Adresar - Vysledek zmeny adresy</TITLE>
</HEAD>
<BODY>
<?
	mysql_Connect("localhost");
	$result = mysql("jkj", "update adresar set jmeno='$jmeno', " +
		  "prijmeni='$prijmeni', email='$email', narozen='$narozen' " +
		  "where id=$id");		 
	if ($result==0):
	    echo "<H1>Adresa byla uspesne zmenena.</H1>";
	else:
	    echo "<H1>Adresu se nepodarilo zmenit.</H1>";
	endif;
	mysql_Close();
?>
<A HREF=index.phtml>Adresar</A>

</BODY>
</HTML>