Lis M. - PHP 5. Praktyczny kurs. Wydanie II

Spis treści Wstęp .............................................................................................. 7 Rozdział 1. Podstawy ...

37 downloads 68 Views 10MB Size


Rysunek 7.3. Formularz służący do wysyłania wiadomości

Kod PHP zajmujący się odbieraniem danych z formularza i formowaniem z nich listu elektronicznego został przedstawiony na listingu 7.8. Najpierw jest sprawdzane, czy w tablicy $_POST znajdują się klucze temat i tresc, to znaczy, czy do skryptu zostały przekazane wymagane dane. Gdyby danych nie było, jedynym efektem działania skryptu byłoby wyświetlenie informacji o ich niekompletności. Jeśli jednak dane będą obecne, są przypisywane zmiennym $temat i $tresc. Następnie wykonywane jest sprawdzenie, czy ich zawartością nie są puste ciągi znaków, co oznaczałoby, że użytkownik nie wypełnił któregoś z pól formularza. W takiej sytuacji również jest wyświetlana informacja o braku danych (warto samodzielnie dodać także instrukcje ograniczające maksymalną długość danych, np. tak jak w skrypcie z listingu 4.12; można także pomyśleć o szczegółowej weryfikacji danych, np. ograniczeniu ich wyłącznie do liter, cyfr i znaków przestankowych). Jeśli jednak zarówno temat, jak i treść listu zostały przesłane, są przekazywane funkcji mail wraz z adresem, pod który mają być wysłane (jest on zapisany w zmiennej $do). Listing 7.8. Skrypt wysyłający e-mail
352

PHP5. Praktyczny kurs $tresc = $_POST['tresc']; if($temat == "" || $tresc == ""){ echo "Niekompletne dane"; } else{ if(mail($do, $temat, $tresc)){ echo "List został wysłany."; } else{ echo "Wystąpił błąd. List nie został wysłany."; } } } else{ echo "Niekompletne dane"; } ?>

Ćwiczenia do samodzielnego wykonania Ćwiczenie 21.1. Napisz skrypt, który będzie dopuszczał połączenia jedynie z wybranych adresów IP zapisanych w pliku tekstowym. Ćwiczenie 21.2. Zmodyfikuj kod z listingu 7.3 tak, aby treść plików tekstowych zawierających adresy IP była wczytywana tylko wtedy, gdy jest to rzeczywiście konieczne. Ćwiczenie 21.3. Napisz skrypt, który będzie wczytywał różne wersje witryny w zależności od tego, jakiej przeglądarki używa odwiedzająca ją osoba. Ćwiczenie 21.4. Na podstawie kodu z listingu 7.6 napisz taki skrypt, który będzie pobierał plik z serwera FTP i w którym parametry połączenia oraz nazwy pliku zdalnego i lokalnego będą podawane w wierszu poleceń podczas wywoływania skryptu. Ćwiczenie 21.5. Zmodyfikuj skrypt wysyłający wiadomości e-mail ze strony WWW w taki sposób, aby liczba wysyłanych wiadomości była ograniczona do trzech w ciągu jednej sesji. Ćwiczenie 21.6. Zmodyfikuj skrypt wysyłający wiadomości e-mail ze strony WWW w taki sposób, aby pozwalał na wysyłanie listów na dowolne adresy e-mail wprowadzane w odpowiednim polu formularza.

Rozdział 8.

Współpraca z bazami danych Lekcja 22. Podstawy baz danych MySQL i SQLite Gdy tworzymy serwis internetowy, musimy się zastanowić, w jaki sposób przechowywać w nim niezbędne dane. W lekcjach 12.i 16. znalazły się przykłady skryptów, takich jak skrypt logowania, licznik, generowanie odnośników, w których dane były przechowywane w plikach na dysku. Jak jednak zostało wtedy wspomniane, wykorzystywanie plików dyskowych ma kilka wad: jest niezbyt wygodne, mało wydajne, występują problemy z synchronizacją, a wraz ze wzrostem ilości przechowywanych danych rośnie też znacznie skomplikowanie skryptów. Dlatego też ogromna większość serwisów internetowych jest oparta na bazach danych. Najczęściej są to bazy relacyjne, a do ich obsługi wykorzystywane są systemy zarządzania relacyjnymi bazami danych RDBMS (z ang. Relational Database Management System). Co prawda dokładne omówienie tej tematyki wykracza poza ramy niniejszej publikacji1, nie oznacza to jednak, że nie można nauczyć się, przynajmniej w podstawowym zakresie, współpracy PHP z bazami danych. Najbardziej znany RDBMS współpracujący z PHP to rozwijany na zasadzie open source MySQL. Jest to bardzo szybki i obecnie jeden z najpopularniejszych serwerów baz danych dostępny na licencji GPL, choć możliwe jest również zakupienie licencji komercyjnej. Korzystać będziemy z wersji darmowej (określanej jako community edition), której każdy może używać bez wnoszenia opłat. Jest ona dostępna dla bardzo wielu systemów operacyjnych, takich jak 1

Czytelnicy, którzy chcieliby pogłębić swoją wiedzę na ten temat, mogą sięgnąć na przykład do publikacji PHP i MySQL dla każdego (http://helion.pl/ksiazki/phsqdk.htm), a także SQL. Ćwiczenia praktyczne (http://helion.pl/ksiazki/cwsqw2.htm) i MySQL. Darmowa baza danych. Ćwiczenia praktyczne (http://helion.pl/ksiazki/cwmsqd.htm).

354

PHP5. Praktyczny kurs

Linux, Windows, MacOS, FreeBSD, Solaris, AIX i in. Pliki z pakietem dystrybucyjnym można pobrać ze strony producenta http://www.mysql.com, są one także zawarte w pakiecie XAMPP. Na wymienionej stronie WWW dostępne są także pełne kody źródłowe bazy. Jeśli nie chcemy korzystać z pełnego systemu obsługi baz, jakim jest MySQL (co wymaga instalowania w systemie dodatkowego oprogramowania RDBMS), a jedynie zapoznać się z technikami bazodanowymi, możemy skorzystać z wbudowanej w PHP5 obsługi bazy SQLite. W większości wypadków na początku przygody z bazami danych powinien on w pełni zaspokoić nasze potrzeby. W dalszej części rozdziału zostanie pokazane, jak posługiwać się zarówno MySQL, jak i SQLite, najpierw jednak dowiedzmy się nieco więcej o relacyjnych bazach danych.

Tabele, klucze i relacje Tabele Pytanie, które trzeba sobie zadać przed rozpoczęciem pracy z bazami danych, brzmi: w jaki sposób dane są przechowywane w bazie? Otóż w systemach relacyjnych baz danych (jakimi są MySQL i SQLite) dane przechowywane są w tabelach. Pomiędzy tymi tabelami, a dokładniej między zawartymi w nich danymi, występują wiążące je relacje2. Jak zatem wygląda pojedyncza tabela z danymi? Każdy, kto widział kiedykolwiek dowolny arkusz kalkulacyjny, może ją sobie z łatwością wyobrazić. Tabela składa się z wierszy i kolumn. Każdy wiersz opisuje jeden rekord, a kolumna jego właściwości. Załóżmy na przykład, że chcielibyśmy przechowywać w bazie dane dotyczące osób, a interesuje nas zapamiętanie imion, nazwisk oraz dat urodzenia. Należałoby zatem utworzyć tabelę zawierającą trzy kolumny. W pierwszej kolumnie byłyby zapisywane dane dotyczące imion, w drugiej nazwisk, a w trzeciej dat urodzenia osób. Przykładowy wygląd takiej tabeli zobrazowano na rysunku 8.1. Tabela ta ma trzy wiersze zawierające dane dotyczące trzech różnych osób. Rysunek 8.1. Przykładowa tabela zawierająca dane dotyczące osób

Klucze Jeśli zastanowimy się nad strukturą tabeli przedstawionej na rysunku 8.1, zapewne dostrzeżemy, że ma ona pewną wadę. Jest ona na tyle poważna, że w praktycznym zastosowaniu użycie takiej tabeli powodowałoby bardzo duże problemy. Otóż ta realizacja nie pozwala na jednoznaczną identyfikację konkretnej osoby. Może się bowiem 2

Ściśle rzecz ujmując, w teorii relacyjnych baz danych relacją jest sama tabela. W książce przyjęto jednak uproszczoną terminologię, dzięki czemu osoby początkujące będą mogły łatwiej przyswoić sobie prezentowaną wiedzę.

Rozdział 8. ♦ Współpraca z bazami danych

355

zdarzyć, że w bazie trzeba będzie zapisać dane dwóch Janów Kowalskich urodzonych 1 stycznia 1970 roku. Użycie struktury z rysunku 8.1 spowodowałoby powstanie dwóch rekordów (wierszy) o takich samych danych, których nie byłoby można rozróżnić (sytuacja taka została przedstawiona na rysunku 8.2). Rysunek 8.2. Nieprawidłowa struktura tabeli nie pozwala na rozróżnienie niektórych rekordów

Oczywiście w praktyce nie wolno do takiej sytuacji dopuścić, a zatem już podczas projektowania tabeli trzeba stworzyć taką strukturę kolumn, aby każdy rekord (zestaw danych) mógł być jednoznacznie zidentyfikowany. W przypadku tabeli przechowującej dane osób moglibyśmy na przykład wprowadzić dodatkową kolumnę zawierającą PESEL danej osoby, który przynajmniej teoretycznie jest unikalny i niepowtarzalny. Najczęściej jednak do tabeli wprowadza się dodatkowe, abstrakcyjne (niemające odzwierciedlenia w rzeczywistych danych) pole identyfikujące każdy wiersz. Mogłoby się ono nazywać na przykład OsobaId. Tabela zawierająca takie pole została przedstawiona na rysunku 8.3. Jak widać, tym razem bez problemów można rozróżnić dwóch Janów Kowalskich, mimo że obaj urodzili się w tym samym dniu. Rysunek 8.3. Utworzenie dodatkowej kolumny pozwala jednoznacznie zidentyfikować każdy rekord

Taka kolumna zawierająca wartość jednoznacznie identyfikującą każdy rekord nazywana jest kluczem podstawowym lub głównym (z ang. primary key). Ogólnie rzecz biorąc, kluczem możemy nazwać dowolnie wybrany zestaw kolumn, czyli np. Imie, Nazwisko. To również jest klucz, niemniej klucz podstawowy powinien zawsze jednoznacznie identyfikować każdy wiersz tabeli, a te dwie kolumny nie spełniają tego warunku. O tym, która kolumna (lub kolumny) będzie kluczem, decyduje programista tworzący bazę. Klucze pozwalają zaś na budowanie relacji między tabelami.

Relacje Wiadomo już, że dane w bazie są przechowywane w tabelach. Logika podpowiada, że takie tabele muszą być ze sobą w jakiś sposób powiązane, inaczej nie będzie z nich większego pożytku. Jeśli prowadzimy na przykład sklep internetowy, a w bazie znajdują się tabele zawierające dane o klientach i o dokonanych przez nich zamówieniach, musi istnieć takie powiązanie, które jednoznacznie przypisze zamówienie do klienta. Oznacza to, że pomiędzy tymi tabelami musi istnieć tzw. relacja. W praktyce takie powiązanie zostanie zrealizowane przez wprowadzenie do jednej z tabel tzw. klucza obcego. Schematycznie sytuacja taka została przedstawiona na rysunku 8.4.

356

PHP5. Praktyczny kurs

Rysunek 8.4. Umieszczenie klucza obcego w tabeli opisującej zamówienia

Mamy tu dwie tabele, jedna opisuje klientów, druga — zamówienia. Obie mają klucze podstawowe, w pierwszej jest to kolumna KlientId, w drugiej — ZamówienieId. W tabeli opisującej zamówienia znajduje się również klucz obcy KlientId, czyli nic innego jak klucz podstawowy z tabeli opisującej klientów. Dzięki takiej strukturze wiemy, że zamówienie o identyfikatorze 1 zostało złożone przez Jana Kowalskiego 25 stycznia 2012 roku, a zamówienie o numerze 3 — 22 kwietnia 2012 roku przez Andrzeja Nowaka. A zatem powtarzając raz jeszcze: KlientId jest kluczem podstawowym w tabeli opisującej klientów, ale kluczem obcym w tabeli opisującej zamówienia. W ten sposób pomiędzy tymi dwiema tabelami powstała relacja. W teorii projektowania relacyjnych baz danych wyróżnia się trzy podstawowe typy relacji:  jeden do jednego,  jeden do wielu,  wiele do wielu.

W przypadku relacji jeden do jednego jednemu rekordowi (wierszowi) z tabeli X odpowiada dokładnie jeden rekord z tabeli Y. Z taką sytuacją moglibyśmy mieć do czynienia na przykład wtedy, gdyby w jednej tabeli zapisać imiona i nazwiska osób, a w innej numery PESEL (rysunek 8.5). Jak wiadomo, każda osoba ma przypisany unikalny numer PESEL, a zatem będzie tu występowała relacja jeden do jednego. Oczywiście od razu należałoby przyjąć założenie, że nie będziemy przechowywać danych obywateli innych państw, którzy nie posiadają PESEL-u.

Rysunek 8.5. Pomiędzy tymi tabelami występuje relacja jeden do jednego

W przypadku relacji jeden do wielu jednemu rekordowi (wierszowi) z tabeli X może odpowiadać jeden lub więcej rekordów z tabeli Y, ale — uwaga — jednemu wierszowi z tabeli Y odpowiada dokładnie jeden z tabeli X. Z taką sytuacją już mieliśmy do czynienia. Spójrzmy ponownie na rysunek 8.4. Znajdują się na nim dwie tabele, z któ-

Rozdział 8. ♦ Współpraca z bazami danych

357

rych pierwsza opisuje klientów, a druga złożone przez nich zamówienia. Jeden klient może złożyć wiele zamówień, ale jedno zamówienie może należeć tylko do jednego klienta. A zatem jest to relacja jeden do wielu (lub też odwrotnie — wiele do jednego). Przypadek trzeci jest bardziej złożony. Otóż relacja wiele do wielu ma miejsce, kiedy jednemu wierszowi z tabeli X może odpowiadać wiele wierszy z tabeli Y oraz jednemu wierszowi z tabeli Y — wiele wierszy z tabeli X. Kiedy możemy mieć do czynienia z taką sytuacją? Na przykład wtedy, gdy chcemy zapisać w bazie dane dotyczące książek i autorów. Wiadomo bowiem, że każda książka może mieć jednego lub więcej autorów, ale także każdy autor mógł napisać jedną lub wiele książek. Jest to więc typowa relacja wiele do wielu. Pytanie, jak taką sytuację odzwierciedlić w praktyce w bazie danych? Osobom, które nie miały dotychczas do czynienia z tworzeniem baz danych, może się nasunąć pomysł, aby stworzyć dwie tabele — Książki i Autorzy — i w pierwszej umieścić dwie lub trzy kolumny odwołujące się do drugiej. Tabele miałyby więc postać taką, jak na rysunku 8.6. Rysunek 8.6. Błędne rozwiązanie problemu relacji wiele do wielu

Tabela Autorzy ma zatem trzy pola: AutorId, Imię, Nazwisko, natomiast tabela Książki — pięć pól: KsiążkaId, Tytuł, Autor1Id, Autor2Id, Autor3Id. Takie rozwiązanie, choć spotykane w praktyce, jest jednak bardzo niedobre i należy go zdecydowanie unikać. Po pierwsze większość książek ma tylko jednego autora, a zatem w większości przypadków pola Autor2Id i Autor3Id pozostaną puste. Po drugie może się zdarzyć sytuacja wyjątkowa, kiedy to jakaś książka będzie miała więcej niż trzech autorów i wtedy nie będziemy w stanie zapisać ich wszystkich w bazie. Dlatego też w przypadku wystąpienia relacji wiele do wielu należy stosować dodatkową tabelę pomocniczą łączącą te, między którymi relacja występuje. Prawidłowe rozwiązanie jest zatem takie jak na rysunku 8.7. Rysunek 8.7. Prawidłowe rozbicie relacji wiele do wielu na trzy tabele

Tabela Autorzy ma taką samą postać jak w poprzednim przypadku, natomiast tabela Książki tym razem ma tylko dwa pola — KsiążkaId (w którym będzie przechowywany unikalny identyfikator) oraz Tytuł. Za połączenia autorów i książek odpowiada natomiast całkiem nowa tabela o nazwie KsiążkiAutorzy. Ma ona dwa pola, w pierwszym, czyli KsiążkaId, zapisywane są identyfikatory książek z tabeli Książki (jest to więc klucz obcy), w drugim, czyli AutorId — identyfikatory autorów z tabeli Autorzy (a zatem to również jest klucz obcy). Dzięki takiemu układowi możemy bez problemu odzwierciedlić sytuację, gdy jeden autor napisał wiele książek i gdy jedna książka ma więcej niż jednego autora. Taka przykładowa sytuacja została przedstawiona na rysunku

358

PHP5. Praktyczny kurs

8.8. Zwróćmy także uwagę, że w tabeli KsiążkiAutorzy obie kolumny zawierają klucze obce, a zatem obie one razem będą stanowić klucz podstawowy (o ile dane w tabeli nie zawierają błędów, a para KsiążkaId, AutorId jednoznacznie wyznacza dany wiersz tabeli KsiążkiAutorzy). Rysunek 8.8. Baza książek i autorów zawierająca przykładowe dane

Widać wyraźnie, że w bazie zapisano dane dwójki autorów: Orsona Scotta Carda3 oraz Kathryn Kidd4 (tabela Autorzy) oraz trzech książek — Gra Endera, Lovelock i Paradise Vue (tabela Ksiażki). Tabela KsiążkiAutorzy zawiera informacje pozwalające dopasować książki do autorów i odwrotnie — autorów do książek. Z danych w niej zawartych wynika, że:  Książka Gra Endera o identyfikatorze 1 ma jednego autora o identyfikatorze 1,

a zatem jest nim Orson Scott Card.  Książka Lovelock o identyfikatorze 2 ma dwoje autorów o identyfikatorach 1 i 2,

a zatem są nimi Orson Scott Card oraz Kathryn Kidd.  Książka Paradise Vue o identyfikatorze 3 ma jednego autora o identyfikatorze 2,

a zatem jest nim Kathryn Kidd.

Bazy danych a PHP Wiadomo z lekcji 1., że gdy serwer WWW współpracuje z PHP, proces pobrania pojedynczej strony składa się z czterech etapów (rysunek 1.2):  wysłania przez przeglądarkę żądania do serwera,  pobrania przez serwer strony z dysku lub pamięci i przesłania jej do aparatu

wykonawczego PHP,  przetworzenia strony przez PHP i odesłania jej z powrotem do serwera,  wysłania przez serwer wygenerowanej ostatecznie strony do przeglądarki. 3

4

Wartym rozważenia może być zagadnienie, jak postępować w sytuacji, gdy autor posługuje się dwoma imionami. Czy zapisywać je oba w jednej kolumnie, tak jak w przedstawionym przykładzie (zmieniając ewentualnie nazwę kolumny na Imiona), czy też zastosować inne rozwiązanie? Dokładniej Kathryn Helms Kidd (w jednym polskich wydań książki Lovelock błędnie wydrukowano jej nazwisko jako Kathryn Kerr).

Rozdział 8. ♦ Współpraca z bazami danych

359

Gdy jednak korzystamy z baz danych, cała procedura wydłuża się o dodatkowe etapy. Sytuację taką schematycznie zobrazowano na rysunku 8.9. Jak widać, tym razem do przejścia jest aż sześć kroków:  Wysłanie przez przeglądarkę do serwera żądania pobrania strony o danym

adresie (1).  Pobranie przez serwer żądanej strony z dysku (lub pamięci). Jeśli jest to

zwykły plik, serwer wysyła jego treść do przeglądarki (6). Jeśli jest to plik o rozszerzeniu .php (lub innym podanym podczas konfiguracji serwera), serwer wysyła do modułu PHP żądanie przetworzenia danych znajdujących się w pliku (2).  Przetworzenie otrzymanych danych przez moduł PHP, czyli wykonanie

zawartego w pliku skryptu. Jeśli skrypt nie wykonuje operacji na bazie danych, następuje wysłanie danych do serwera (5). Jeśli skrypt wykonuje operacje na bazie danych, zostaje nawiązane połączenie z bazą i wysłanie zapytań (3).  Przetworzenie zapytań przez serwer bazy danych i zwrócenie ich wyniku

do modułu PHP (4).  Wysłanie przez moduł PHP przetworzonych danych do serwera WWW (5).  Wysłanie przez serwer przetworzonych danych do przeglądarki (6).

Rysunek 8.9. Schemat przetwarzania danych podczas korzystania z PHP i bazy danych

Instalacja systemu bazy danych Instalacja SQLite Obsługa SQLite jest wbudowana we współczesne wersje PHP i nie wymaga dodatkowych czynności instalacyjnych. Użytkownicy systemu Windows muszą jedynie sprawdzić, czy w pliku konfiguracyjnym php.ini znajdują się następujące linie (dokładnie w przedstawionej kolejności): extension=php_pdo.dll extension=php_sqlite.dll

Jeżeli ich nie ma, należy je dodać. Jeżeli są ujęte w komentarz (poprzedzone znakami średnika), należy komentarze usunąć (po dokonaniu zmian trzeba zrestartować serwer WWW).

360

PHP5. Praktyczny kurs

Do zarządzania bazą na przykład przygotowania danych można natomiast użyć działającego w wierszu poleceń klienta sqlite. Jest on dostępny w internecie pod adresem http://www.sqlite.org (w Linuksie klient SQLite często jest dostępny standardowo i nie wymaga pobierania dodatkowych danych). Aby uruchomić klienta, należy go skopiować do wybranego katalogu oraz wydać polecenie: sqlite nazwa_pliku;

gdzie nazwa_pliku to określenie pliku z bazą danych. Jeśli pliku określonego przez nazwa_pliku nie będzie na dysku, zostanie on utworzony — powstanie w ten sposób nowa baza. Po uruchomieniu klienta (rysunek 8.10) można w nim wykonywać polecenia i zapytania SQL (takie, jakie zostaną zaprezentowane w kolejnej lekcji). Rysunek 8.10. Uruchomienie klienta SQLite

Instalacja MySQL Ze względu na istnienie wielu wersji serwera MySQL dostępnego dla wielu różnych systemów operacyjnych nie można dokładnie opisać wszystkich możliwych rodzajów instalacji. Nie jest to jednak proces skomplikowany i zostanie pokazany dla wersji 5.5 (najnowszej dostępnej w trakcie powstawania książki) w systemach Windows i Linux. W przypadku innych wersji MySQL oraz innych systemów operacyjnych czynność ta wygląda jednak bardzo podobnie i z pewnością nikomu nie sprawi najmniejszych problemów. W razie wątpliwości należy zajrzeć do dokumentacji pakietu dostępnej na stronach serwisu http://www.mysql.com.

Windows Plik instalacyjny pakietu można znaleźć pod adresem http://www.mysql.com. Będzie miał nazwę mysql-wersja-win32.msi (lub podobną), gdzie wersja to określanie wersji serwera, np. mysql-5.5.8-win32.msi. Po uruchomieniu pliku pojawi się standardowe okno instalatora Windows, który przeprowadzi nas przez dalsze czynności. Do wyboru są trzy typy instalacji:  Instalacja standardowa (Typical) — pozwala na zainstalowanie serwera,

klienta oraz kilku innych narzędzi.  Instalacja pełna (Complete) — pozwala na zainstalowanie wszystkich

składników pakietu.  Instalacja użytkownika (Custom) — pozwala na dokładne ustalenie,

które ze składników pakietu mają być zainstalowane.

Rozdział 8. ♦ Współpraca z bazami danych

361

W przypadku wybrania instalacji użytkownika pojawi się nowe okno dialogowe umożliwiające wybór poszczególnych składowych pakietu. W tym przypadku możliwa jest również zmiana katalogu docelowego, w którym będzie zainstalowany pakiet MySQL. Po wybraniu żądanych opcji należy kliknąć Next, co spowoduje wyświetlenie (podobnie jak w przypadku dwóch poprzednich typów instalacji) okna podsumowującego wybór. Procedura instalacyjna rozpocznie się po kliknięciu przycisku Install. Jeśli na ostatnim ekranie instalatora (rysunek 8.11) zaznaczy się opcję Launch the MySQL Instance Config Wizard (lub o podobnej treści, np. Configure the MySQL server now — zależy to od konkretnej wersji instalatora), po kliknięciu przycisku Finish zostanie uruchomiony kreator konfiguracji, warto więc z takiej możliwości skorzystać (kreator konfiguracji może być jednak również wywoływany z menu startowego w dowolnym innym momencie). Rysunek 8.11. Okno kończenia instalacji

Uruchomienie kreatora konfiguracji powoduje wyświetlenie ekranu tytułowego, na którym należy kliknąć przycisk Next. Zostanie wtedy wyświetlone okno dialogowe umożliwiające wybór opcji konfiguracyjnych (rysunek 8.12). W przypadku pierwszej instalacji możliwe są dwa tryby pracy:  Detailed Configuration — przeznaczony dla zaawansowanych użytkowników,

którzy chcą mieć możliwość szczegółowej ingerencji w ustawienia serwera.  Standard Configuration — przeznaczony dla osób, dla których wystarczające

będą standardowe ustawienia. Rysunek 8.12. Okno wyboru trybu pracy konfiguratora

362

PHP5. Praktyczny kurs

Przy pierwszej instalacji w zupełności wystarczą standardowe ustawienia serwera, wybierzmy zatem opcję Standard Configuration (opis konfiguracji w trybie Detailed Configuration można znaleźć w dokumentacji MySQL). Aplikację konfiguracyjną można również wywoływać, uruchamiając plik MySQL InstanceConfig.exe. Znajduje się on w katalogu, w którym został zainstalowany serwer, w podkatalogu bin. Konfigurator jest dostępny także z poziomu menu startowego: Wszystkie programy/MySQL/MySQL Server 5.5/MySQL Server Instance Config Wizard. Po wybraniu opcji Standard Configuration i kliknięciu przycisku Next na ekranie pojawi się kolejne okno dialogowe (rysunek 8.13), które pozwala na podjęcie decyzji, czy MySQL ma być uruchamiane jako usługa systemowa (Windows Service), czy też jako samodzielna aplikacja. Wybór jest dowolny i zależy tylko od naszych preferencji. Domyślnie trybem pracy jest usługa systemowa. Zaznaczenie opcji Launch the MySQL Server automatically powoduje, że usługa MySQL będzie uruchamiana automatycznie wraz ze startem systemu. Z listy rozwijanej Service Name można również wybrać nazwę, jaką usługa będzie miała w systemie. Omawiane okno dialogowe zawiera też opcję Include Bin Directory in Windows PATH, której zaznaczenie powoduje dodanie do zmiennej środowiskowej PATH ścieżki dostępu do plików binarnych MySQL, dzięki czemu będą one mogły być wygodnie wywoływane w wierszu poleceń. Rysunek 8.13. Rejestrowanie MySQL jako usługi systemowej

Kolejne okno dialogowe, widoczne na rysunku 8.14, pozwala na ustawienie hasła dla konta administratora (użytkownika root) bazy danych. Hasło należy wprowadzić w polu tekstowym Enter the root password (New Root password) oraz potwierdzić w polu Retype the password (Confirm). Jeżeli zaznaczymy pole wyboru Enable root access from remote machines, administrator bazy będzie mógł logować się z komputerów zdalnych (np. innego komputera w sieci lokalnej lub przez internet). Jeśli nie ma potrzeby administracji serwerem z innych lokalizacji, dobrze jest pozostawić tę opcję wyłączoną — zwiększy to bezpieczeństwo serwera. Zaznaczenie opcji Create An Anonymous Account pozwala na założenie konta anonimowego, które będzie umożliwiało logowanie do bazy osobom nieposiadającym konta. Ze względów bezpieczeństwa zakładanie takiego konta również nie jest jednak polecane.

Rozdział 8. ♦ Współpraca z bazami danych

363

Rysunek 8.14. Konfiguracja konta administratora

Po kliknięciu przycisku Next na ekranie pojawi się kolejne okno, tym razem jedynie informujące, że wybór opcji został zakończony i kreator może przystąpić do konfiguracji serwera. Należy kliknąć przycisk Execute, co spowoduje wykonanie skryptów konfiguracyjnych oraz wyświetlenie okna podsumowującego konfigurację (rysunek 8.15). W tym (ostatnim już) oknie należy kliknąć Finish, co zakończy proces konfiguracji. W katalogu MySQL zostanie zapisany plik tekstowy my.ini zawierający opcje konfiguracyjne. W razie potrzeby można ten plik edytować ręcznie za pomocą dowolnego edytora tekstowego. Rysunek 8.15. Proces konfiguracji został zakończony

Linux Użytkownicy Linuksa są w lepszej sytuacji niż korzystający z systemu Windows, gdyż wiele popularnych dystrybucji zawiera pakiety MySQL, które zwykle są standardowo instalowane, a jeśli nie, można je zawsze doinstalować, korzystając z dołączonego do

364

PHP5. Praktyczny kurs

systemu instalatora. Instalacja za pomocą graficznego menedżera pakietów przebiega podobnie jak w przypadkach Apache’a i PHP (opisywanych w rozdziale 1). Należy odszukać pakiety opisane jako The MySQL server and related files (rysunek 8.16) i MySQL Client programs and shared libraries (lub podobnie; oba powinny być dostępne w sekcji Serwery), a następnie rozpocząć standardową procedurę instalacyjną. Rysunek 8.16. Wybór pakietu serwera MySQL

Po jej ukończeniu zaleca się nadanie hasła dla konta administratora bazy danych (użytkownika root). W tym celu najpierw trzeba uruchomić serwer (zostało to opisane w punkcie „Uruchamianie serwera”), a następnie w konsoli wydać polecenie: mysqladmin –u root password hasło

Jeśli na przykład hasłem użytkownika root ma być abh126WL, należy wydać polecenie: mysqladmin –u root password abh126WL

Po jego wykonaniu wszelkie czynności administracyjne wymagające logowania do serwera będą wymagały podawania hasła. Uwaga! Polecenie w tej postaci zadziała tylko wtedy, jeśli hasło administratora nie zostało nadane (lub zostało usunięte). Aby zmienić istniejące hasło użytkownika root (które na przykład zostało podane w trakcie instalacji), należy użyć polecenia: mysqladmin –u root –pstare_hasło password nowe_hasło

np.: mysqladmin –u root –pabh126WL password xjwE7401AP

W przypadku instalacji w trybie tekstowym należy w konsoli wydać jedno z poleceń (w zależności od tego, z jakiego instalatora chcemy skorzystać i jaki jest dostępny w używanej dystrybucji systemu): sudo apt-get install mysql-server

lub: sudo aptitude install mysql-server

lub: sudo yum install mysql-server

Rozdział 8. ♦ Współpraca z bazami danych

365

W trakcie instalacji może się pojawić okno pozwalające na wprowadzenie hasła administratora (użytkownika root), np. takie jak na rysunku 8.17. Jeśli tak będzie, hasło należy oczywiście podać. Jeżeli okno się nie pojawi, hasło należy nadać samodzielnie po zakończeniu instalacji pakietu (tak jak zostało to opisane wyżej). Rysunek 8.17. Nadawanie hasła dla konta administratora przy instalacji w trybie tekstowym

XAMPP Gdy korzysta się z pakietu XAMPP, nie trzeba wykonywać żadnych dodatkowych czynności instalacyjnych. Baza MySQL jest standardowo dostępna w tym pakiecie. Należy jedynie nadać hasło administratora (użytkownika Root) bazy danych, co zostało opisane w rozdziale 1. w lekcji 2.

Obsługa serwera MySQL Uruchamianie serwera W systemie Windows Jeżeli pakiet MySQL został zainstalowany jako uruchamiana automatycznie usługa systemowa, nie ma potrzeby wykonywania żadnych dodatkowych czynności. Serwer będzie uruchamiany automatycznie przy starcie systemu i zamykany, również automatycznie, podczas kończenia jego pracy. W przypadku gdy MySQL zostało zainstalowane jako usługa systemowa, która ma być uruchamiana ręcznie, serwer może zostać uruchomiony za pomocą komendy: net start nazwa_usługi

gdzie nazwa_usługi jest nazwą usługi MySQL wybraną podczas konfiguracji pakietu. Domyślnie jest to MySQL, a zatem standardowo usługa może zostać uruchomiona za pomocą komendy: net start MySQL

366

PHP5. Praktyczny kurs

Jeśli nie zainstalowaliśmy MySQL jako usługi systemowej, a chcielibyśmy jednak, aby serwer pracował w taki sposób, należy wydać polecenie: mysqld --install

Tak zainstalowana usługa będzie uruchamiana automatycznie podczas startu systemu. Jeśli usługa ma być uruchamiana manualnie, należy ją zainstalować, wydając polecenie: mysqld --install-manual

Usunięcie usługi mysql (niezależnie od tego, w jaki sposób została zainstalowana) odbywa się przez wydanie polecenia: mysqld --remove

Należy pamiętać, że instalacja MySQL jako usługi nie oznacza równoczesnego uruchomienia tej usługi. Uruchomienie następuje albo w sposób automatyczny przy starcie systemu, albo ręczny po wydaniu polecenia net start mysql, tak jak zostało to opisane powyżej. Do uruchamiania usług można także wykorzystać graficzne narzędzie Usługi (Panel sterowania/Narzędzia administracyjne/Usługi). Serwer bazy danych może być także uruchamiany jako samodzielna aplikacja. W takim przypadku w wierszu poleceń należy wydać komendę: mysqld --console

lub po prostu mysqld

W pierwszym przypadku wiadomości diagnostyczne będą wypisywane wprost na ekranie konsoli. Po uruchomieniu serwer przechodzi w stan oczekiwania na połączenia. Domyślnie do połączeń jest wykorzystywany protokół tcp/ip, a serwer nasłuchuje standardowo na porcie 3306.

W systemie Linux Aby uruchomić serwer MySQL, można skorzystać z graficznego menedżera usług. Robi się to na takiej samej zasadzie, jak było to w przypadku serwera Apache. Ponieważ ta procedura została opisana w lekcji 2., nie ma potrzeby, aby ją ponownie przytaczać. Uruchomienie serwera z wiersza poleceń można osiągnąć przez wywołanie skryptu mysql lub mysqld (zależy to od używanej wersji systemu) znajdującego się w lokalizacji /etc/init.d/ z parametrem start, wydając na przykład komendę: sudo /etc/init.d/mysql start

lub: sudo /etc/init.d/mysqld start

W przypadku systemów zawierających narzędzie service, można również (jeśli usługa ma nazwę mysql) użyć polecenia: sudo service mysql start

Rozdział 8. ♦ Współpraca z bazami danych

367

lub (jeśli usługa ma nazwę mysqld): sudo service mysqld start

Korzystając z pakietu XAMPP Gdy korzysta się z oprogramowania XAMPP, serwer bazy danych można uruchomić, wywołując panel kontrolny (tak jak było to opisywane w rozdziale 1.), a następnie klikając przycisk Start w wierszu MySql. Napis na przycisku zmieni się na Stop, pojawi się także słowo running na zielonym tle.

Kończenie pracy serwera W systemie Windows Jeśli serwer został zainstalowany jako usługa zarządzana automatycznie, będzie również automatycznie kończył pracę wraz z zamknięciem systemu. Jeśli chcemy wymusić zakończenie pracy usługi serwera wcześniej, należy wydać w wierszu polecenie: net stop nazwa_usługi

np.: net stop MySQL

Jeśli serwer został zainstalowany jako samodzielna aplikacja i uruchomiony ręcznie, do zakończenia jego pracy należy użyć programu mysqladmin. W przypadku gdy zgodnie z wcześniejszym opisem zostało założone konto administratora, należy wydać polecenie (oczywiście na innej konsoli niż ta, w której został uruchomiony serwer): mysqladmin --user=root --password=hasło shutdown

lub alternatywnie: mysqladmin -u root -phasło shutdown

W systemie Linux W standardowych warunkach serwer kończy działanie wraz z końcem pracy systemu. Jeśli jednak chcemy wymusić zakończenie pracy serwera, należy użyć programu mysqladmin, skryptu mysql (lub mysqld) lub narzędzia service (o ile jest dostępne). W pierwszym przypadku wydaje się polecenie: mysqladmin --user=root --password=hasło shutdown

lub alternatywnie: mysqladmin -u root -phasło shutdown

Skryptu mysql (mysqld) używa się analogicznie jak w przypadku uruchamiania serwera, zmieniając jedynie komendę start na stop: sudo /etc/init.d/mysql stop

lub: sudo /etc/init.d/mysqld stop

368

PHP5. Praktyczny kurs

Podobnie jest z narzędziem service. Używa się polecenia: sudo service mysql stop

lub: sudo service mysqld stop

Oczywiście należy skorzystać z jednego z przedstawionych sposobów, a nie wszystkich trzech jednocześnie.

Korzystając z pakietu XAMPP Gdy korzysta się z oprogramowania XAMPP, serwer bazy danych można zatrzymać, wywołując panel kontrolny (tak jak było to opisywane w rozdziale 1.), a następnie klikając przycisk Stop w wierszu MySql. Napis na przycisku zmieni się na Start, zniknie także widniejące obok słowo running.

Przygotowanie serwera do pracy Komunikacja z serwerem Aby połączyć się z serwerem i móc wykonywać czynności związane z obsługą baz, potrzebny jest program klienta. Razem z serwerem MySQL dostępny jest działający w wierszu poleceń program mysql (można także użyć innych narzędzi, np. działającego w trybie graficznym klienta MySQL Query Browser czy bardziej zaawansowanej aplikacji MySQL Workbench; oba narzędzia dostępne pod adresem http://www.mysql.com). W dalszej części książki będzie opisywany klient pracujący w trybie tekstowym, jako że dostępny jest on standardowo w każdej wersji pakietu MySQL. Nic jednak nie stoi na przeszkodzie, aby używać klienta graficznego. Wydawane komendy będą w obu przypadkach takie same. Aby uruchomić program klienta i połączyć się z serwerem, należy wydać jedną z komend: mysql –u root --password=hasło mysql –u root -phasło

lub mysql –u root –p

Ponieważ w ostatnim przypadku hasło użytkownika root nie zostało podane w wywołaniu, pojawi się monit o jego podanie. Po chwili nastąpi nawiązanie połączenia z serwerem (rysunek 8.18) i będzie można wykonywać zapytania i inne czynności związane z zarządzaniem, takie jak tworzenie i usuwanie baz, tworzenie, usuwanie i modyfikacja kont użytkowników oraz tabel, a także wydawanie zapytań w języku SQL. Należy zwrócić uwagę, że komendy wydawane serwerowi muszą się kończyć znakiem średnika. Jeśli ten warunek nie będzie spełniony, polecenie nie zostanie przesłane do serwera, klient będzie bowiem czekał na wprowadzenie dalszych danych.

Rozdział 8. ♦ Współpraca z bazami danych

369

Rysunek 8.18. Nawiązanie połączenia z serwerem MySQL

Zarządzanie bazami danych Serwer MySQL może jednocześnie obsługiwać wiele baz danych. W niektórych wersjach standardowo po zainstalowaniu dostępna jest baza o nazwie test służąca do testowania konfiguracji. Można ją wykorzystywać w przykładach prezentowanych w kolejnych lekcjach. Nic nie stoi jednak na przeszkodzie, aby utworzyć własną bazę danych (jeśli bazy testowej nie ma, jest to wręcz konieczne). W celu wykonania tej czynności należy (oczywiście w kliencie mysql po nawiązaniu połączenia z serwerem, tak jak zostało to opisane w poprzednim podpunkcie) wydać polecenie create database w schematycznej postaci: create database nazwa_bazy;

Polecenie musi być zakończone znakiem średnika. Jeśli chcemy na przykład utworzyć bazę o nazwie testphp, napiszemy: create database testphp;

Po kliknięciu przycisku Enter serwer potwierdzi utworzenie bazy, wyświetlając tekst: Query OK, 1 row affected (0.05 sec)

Można teraz wydawać kolejne polecenia (widoczne określenie 0.05 sec odnosi się do czasu, w jakim zostało wykonane polecenie, i oczywiście może mieć inną wartość). W celu usunięcia bazy z serwera należy wykorzystać komendę drop database w postaci: drop database nazwa_bazy;

Jeśli chcielibyśmy usunąć utworzoną przed chwilą bazę testphp, należałoby napisać: drop database testphp;

Odpowiedź serwera będzie podobna jak w poprzednim przypadku, np.: Query OK, 0 rows affected (0.08 sec)

370

PHP5. Praktyczny kurs

Utworzenie konta użytkownika Podczas instalacji i konfiguracji MySQL utworzone zostało jedynie konto administratora — root. Ponieważ użytkownik ten ma pełne prawa do wykonywania wszelkich czynności związanych z zarządzaniem serwerem, ze względów bezpieczeństwa konto to nie powinno być używane podczas zwykłej pracy. Do codziennej pracy należy utworzyć inne konto (lub konta) oraz przydzielić mu odpowiednie prawa. Służy do tego polecenie GRANT. Nie będzie tu omawiana pełna składnia tego polecenia i związane z nim opcje, gdyż nie jest to potrzebne, a dokładny opis zająłby zbyt wiele miejsca. Przedstawiona zostanie jednak uproszczona postać komendy, która w zupełności wystarczy do dalszej pracy. Ogólnie polecenie to można przedstawić jako: GRANT prawa ON obiekt TO użytkownik [IDENTIFIED BY 'hasło']

Oznacza ono: nadaj użytkownikowi użytkownik prawa (uprawnienia, przywileje) prawa do obiektu obiekt, nadając mu jednocześnie hasło hasło. Nadanie hasła jest przy tym opcjonalne. Praw, które można nadać użytkownikowi, jest bardzo wiele i mogą się one odnosić do różnych obiektów, całych baz danych, pojedynczych tabel czy nawet ich kolumn. Do dalszej pracy wystarczy, jeśli utworzymy użytkownika o nazwie php, nadając mu dowolne hasło (w dalszej pracy będzie używane hasło test, które oczywiście należy zmienić na bardziej skomplikowane), i nadamy mu pełne prawa do utworzonej poprzednio bazy danych o nazwie testphp. Utworzenie takiego użytkownika osiągniemy, wydając serwerowi polecenie: GRANT ALL ON testphp.* TO php IDENTIFIED BY 'test';

Od tej chwili podczas wywoływania klienta mysql można będzie posługiwać się kontem php zamiast kontem administratora (root), czyli wydawać w konsoli polecenie: mysql –u php -ptest

Utworzone konto użytkownika może zostać usunięte za pomocą polecenia DROP USER o schematycznej postaci: drop user nazwa_użytkownika;

W wersjach do 5.0.2 przed wydaniem tego polecenia należy najpierw odebrać użytkownikowi jego prawa, wykorzystując instrukcję: REVOKE all ON *.* FROM nazwa_użytkownika;

W wersjach, począwszy od 5.0.2, wystarczy samo: drop user nazwa_użytkownika;

Wybór bazy danych Jak wiadomo, na serwerze może istnieć wiele baz danych tworzonych za pomocą polecenia CREATE DATABASE. Ponieważ każda baza jest niezależna i może zawierać takie

Rozdział 8. ♦ Współpraca z bazami danych

371

same elementy jak każda inna, serwer musi wiedzieć, z którą bazą aktualnie chcemy pracować. Takiego wyboru można dokonać po zalogowaniu się do serwera, za pomocą instrukcji use w postaci: use nazwa_bazy;

Jeśli zatem chcemy pracować z bazą o nazwie testphp, musimy wydać polecenie: use testphp;

Aby wykonanie tego polecenia zakończyło się sukcesem, dany użytkownik musi oczywiście mieć prawa dostępu do tej bazy, inaczej zgłoszony zostanie komunikat o błędzie. Jeśli na przykład powyższe polecenie wydał użytkownik o nazwie user1, który nie ma praw do bazy testphp, serwer wyświetli komunikat podobny do: ERROR 1044: Access denied for user 'user1'@'%' to database 'testphp'

Wyboru bazy można także dokonać w wierszu poleceń, podając nazwę podczas wywoływania aplikacji klienta mysql (przy założeniu, że chcemy używać bazy o nazwie testphp, logując się jako użytkownik php), np.: mysql -u php –ptest testphp

W przypadku gdy żadna baza nie została wybrana, przy próbie wykonania zapytania (utworzenia tabeli, pobrania danych itp.; patrz lekcja 23.) serwer zgłosi błąd, co objawi się w postaci komunikatu podobnego do: ERROR 1046: No database selected

Lekcja 23. Podstawy SQL Czym jest SQL? SQL, czyli Structured Query Language, to strukturalny język zapytań, który umożliwia wykonywanie wszelkich operacji na relacyjnych bazach danych. Jest to język uniwersalny stosowany praktycznie w każdym popularnym systemie relacyjnych baz danych, zarówno w systemach komercyjnych, takich jak DB2, MS SQL czy Oracle, jak i tych rozwijanych na zasadach wolnego oprogramowania, np. PostgreSQL czy niektóre wersje MySQL. SQL realizuje trzy różne typy zadań, w związku z tym występujące w nim instrukcje można podzielić na trzy grupy:  DDL — język definiowania danych (z ang. Data Definition Language)

umożliwiający definicję struktury danych,  DML — język manipulacji danymi (z ang. Data Manipulation Language)

umożliwiający pobieranie i modyfikowanie danych,  DCL — język kontroli danych (z ang. Data Control Language)

umożliwiający kontrolę dostępu do danych.

372

PHP5. Praktyczny kurs

W poprzedniej lekcji pojawiły się podstawowe instrukcje należące do DCL. Umożliwiały one m.in. zarządzanie kontami użytkowników i administratora. W tej lekcji pojawią się instrukcje należące do DDL i DML, które pozwolą na tworzenie struktury bazy oraz na wprowadzanie do niej danych i zarządzanie nimi. W SQL nie są rozróżniane wielkie i małe litery, można ich więc używać zamiennie według własnego uznania. W książce została przyjęta konwencja, że elementy języka SQL będą zapisywane wielkimi literami. Przykłady z niniejszej lekcji opierają się na wykorzystaniu pracującego w wierszu poleceń klienta MySQL, istnieje jednak również możliwość wykonywania ich za pomocą klienta SQLite dostępnego pod adresem http://www.sqlite.org.

Obsługa tabel Jak wiadomo z lekcji 22., dane w bazie są przechowywane w tabelach. Trzeba zatem poznać konstrukcje języka SQL, które pozwalają na manipulację tymi strukturami danych. Aby sprawnie posługiwać się bazą danych, trzeba wiedzieć, jak tworzyć, modyfikować i usuwać tabele. Dopiero potem można zacząć z nich korzystać, czyli nauczyć się, jak wprowadzać dane do bazy oraz jak je z niej pobierać.

Tworzenie tabel Do tworzenia tabel służy instrukcja CREATE TABLE w schematycznej postaci: CREATE TABLE nazwa_tabeli ( nazwa_kolumny_1 typ_kolumny_1 [atrybuty], nazwa_kolumny_2 typ_kolumny_2 [atrybuty], ... nazwa_kolumny_N typ_kolumny_N [atrybuty], )

Nazwa tabeli może zawierać dowolne znaki, które mogą wystąpić w nazwie pliku w systemie operacyjnym, na którym działa serwer MySQL, z wyjątkiem /, \ i .. Jeśli jednak zawiera jakieś znaki specjalne (w tym spacje) lub jest słowem zastrzeżonym dla konstrukcji języka (np. select, create), powinna być jednak ujęta w znaki ‘ (lewy apostrof), np. ‘select‘. Taka instrukcja utworzy tabelę w bieżącej bazie. Nazwa bazy może być jednak podana jawnie. Należy wtedy użyć konstrukcji nazwa_bazy.nazwa_tabeli lub ‘nazwa_bazy‘. ‘nazwa_tabeli‘. Podobne zasady dotyczą nazw kolumn. Typ kolumny określa typ danych, które będzie ona mogła przechowywać, np. łańcuch znaków, liczby itp. Typy danych zostaną omówione w następnej części lekcji. W nazwach tabel i kolumn mogą występować zarówno małe, jak i wielkie litery, jednak nie są one rozróżniane. W dalszych przykładach zostało przyjęte, że nazwy tabel są pisane tylko małymi literami, a kolumn — małymi i wielkimi, wyłącznie za pomocą liter alfabetu łacińskiego (bez polskich znaków) oraz bez spacji (spacje w razie potrzeby będą zastępowane znakami podkreślenia).

Rozdział 8. ♦ Współpraca z bazami danych

373

Dla treningu spróbujmy teraz utworzyć prostą tabelę klient, która będzie zawierała dwie kolumny. Pierwsza kolumna o nazwie Indeks będzie przechowywała liczby całkowite (typ danych INTEGER), a druga o nazwie Nazwa — ciągi o maksymalnej długości 20 znaków (typ VARCHAR(20)). Instrukcja SQL tworząca taką tabelę ma postać: CREATE TABLE klient( Indeks INTEGER, Nazwa VARCHAR(20) );

Każda kolumna może mieć dodatkowe atrybuty. Najczęściej spotykane to: PRIMARY KEY, NOT NULL, AUTO_INCREMENT, INDEX, UNIQUE. Atrybut PRIMARY KEY oznacza, że dana kolumna jest kluczem podstawowym (głównym). Jednocześnie wymusza to zapis unikalnych wartości w kolejnych wierszach (jest to zrozumiałe, skoro klucz podstawowy musi jednoznacznie identyfikować każdy wiersz). Atrybut NOT NULL oznacza, że w danej kolumnie nie mogą znajdować się wartości puste, czyli że każdy jej wiersz musi zawierać jakąś wartość. Próba zapisania wartości pustej zakończy się niepowodzeniem. Atrybut AUTO_INCREMENT ma zastosowanie jedynie do kolumn, które przechowują wartości całkowite. Podczas wstawiania nowego wiersza wartość takiej kolumny może być automatycznie zwiększana o jeden. Kolumny tego typu automatycznie otrzymują też atrybut NOT NULL (czyli nie mogą zawierać wartości pustych; każdy wiersz musi zawierać jakąś wartość). Atrybut INDEX oznacza, że dana kolumna będzie indeksowana. Indeks to specjalna dodatkowa struktura porządkująca dane w kolumnie, a tym samym zwiększająca szybkość niektórych operacji. Klucze podstawowe są indeksowane automatycznie. Atrybut UNIQUE oznacza, że dane w kolumnie będą musiały być unikatowe, tzn. nie będą mogły istnieć dwa wiersze o takiej samej zawartości. Użycie atrybutu UNIQUE powoduje jednoczesne utworzenie indeksu dla kolumny. Jeśli zatem w naszej przykładowej tabeli klient kolumna Indeks miałaby być kluczem podstawowym, należałoby zastosować konstrukcję: CREATE TABLE klient( Indeks INTEGER NOT NULL PRIMARY KEY, Nazwa VARCHAR(20) );

Formalnie kolumna będąca kluczem podstawowym powinna być, tak jak w powyższym przykładzie, deklarowana z atrybutem NOT NULL, jeśli jednak atrybut ten zostanie pominięty, serwer doda go automatycznie. Nie zostanie zatem zgłoszony błąd, jeśli zastosowana zostanie forma skrócona w postaci: CREATE TABLE klient( Indeks INTEGER PRIMARY KEY, Nazwa VARCHAR(20) );

374

PHP5. Praktyczny kurs

Gdybyśmy chcieli, aby kolumna Indeks była kluczem podstawowym z generowanymi automatycznie wartościami, a Nazwa nie mogła zawierać wartości pustych, należałoby zastosować konstrukcję: CREATE TABLE klient( Indeks INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY, Nazwa VARCHAR(20) NOT NULL );

W przypadku gdy klucz podstawowy miałby się składać z więcej niż jednej kolumny, jego definicja powinna wyglądać inaczej. Schematycznie tego typu konstrukcja ma postać: CREATE TABLE nazwa_tabeli ( nazwa_kolumny_1 typ_kolumny_1 [atrybuty], nazwa_kolumny_2 typ_kolumny_2 [atrybuty], ... nazwa_kolumny_n typ_kolumny_n [atrybuty], PRIMARY KEY (kolumna_x, kolumna_y, ..., kolumna_z) )

Jeśli na przykład tabela ma trzy kolumny: Identyfikator o typie INTEGER oraz Imie i Nazwisko o typie VARCHAR i wszystkie one mają tworzyć klucz podstawowy, instrukcja SQL tworząca tę tabelę będzie miała postać: CREATE TABLE klient( Identyfikator INTEGER NOT NULL, Imie VARCHAR(20) NOT NULL, Nazwisko VARCHAR(20) NOT NULL, PRIMARY KEY(identyfikator, imie, nazwisko) );

Modyfikacja tabel Jeśli konieczna jest zmiana struktury już istniejącej tabeli, należy skorzystać z polecenia ALTER TABLE w postaci: ALTER TABLE nazwa_tabeli zmiana1[, zmiana2[, ...,[zmianaN]]]

Można w ten sposób dodawać i usuwać kolumny, modyfikować ich typy i nazwy, dodawać bądź usuwać indeksy itp. Zmiany wprowadza się w podobny sposób jak przy tworzeniu tabeli. Każdy z ciągów zmiana jest w istocie poleceniem do wykonania. Lista dostępnych poleceń zależna jest od systemu bazy danych. Często spotykane polecenia to:  ADD [COLUMN] definicja_kolumny [FIRST | AFTER nazwa_kolumny] — dodaje nową kolumnę, a opcje FIRST i AFTER pozwalają na umiejscowienie

jej w strukturze tabeli.  ADD [COLUMN] (definicja_kolumny) — dodaje nową kolumnę.  ADD INDEX [nazwa] [typ] (kolumna1, kolumna2,...,kolumnaN) — tworzy nowy indeks o nazwie nazwa i typie typ, będą do niego należały

kolumny wymienione w nawiasie okrągłym.

Rozdział 8. ♦ Współpraca z bazami danych

375

 ADD [CONSTRAINT [symbol]] PRIMARY KEY [typ] (kolumna1, kolumna2,..., kolumnaN) — dodaje do tabeli klucz podstawowy, na który będą się składały

kolumny wymienione w nawiasie okrągłym.  ADD [CONSTRAINT [symbol]] UNIQUE [nazwa] [typ] (kolumna1, kolumna2,..., kolumnaN) — dodaje do tabeli nowy unikalny indeks, na który będą się składały

kolumny wymienione w nawiasie okrągłym.  ADD [CONSTRAINT [symbol]] FOREIGN KEY [nazwa] (kolumna1, kolumna2,..., kolumnaN) [definicja odniesienia] — dodaje do tabeli nowy klucz obcy,

na który będą się składały kolumny wymienione w nawiasie okrągłym.  ALTER [COLUMN] nazwa SET DEFAULT wartość — specyfikuje wartość domyślną dla kolumny nazwa.  ALTER [COLUMN] nazwa DROP DEFAULT wartość — usuwa wartość domyślną dla kolumny nazwa.  CHANGE [COLUMN] stara_nazwa definicja_nowej_kolumny [FIRST | AFTER nazwa] — zmienia kolumnę stara_nazwa na zdefiniowaną przez definicja_ nowej_kolumny. Opcje FIRST i AFTER pozwalają na umiejscowienie nowej

kolumny w strukturze tabeli.  [DEFAULT] CHARACTER SET nazwa [COLLATE collation_name] — ustala domyślny zestaw znaków dla tabeli. Opcja COLLATE pozwala na zdefiniowanie

obowiązujących dla danej strony kodowej reguł porównywania i sortowania.  CONVERT TO CHARACTER SET nazwa [COLLATE collation_name] — wykonuje konwersję zestawu znaków (strony kodowej). Opcja COLLATE pozwala na

zdefiniowanie obowiązujących dla danej strony kodowej reguł porównywania i sortowania.  MODIFY [COLUMN] definicja_kolumny [FIRST | AFTER nazwa] — modyfikuje strukturę kolumny, nie pozwala jednak na zmianę jej nazwy. Opcje FIRST i AFTER pozwalają na umiejscowienie nowej kolumny w strukturze tabeli.  DROP [COLUMN] nazwa — usuwa kolumnę nazwa.  DROP PRIMARY KEY — usuwa z tabeli klucz podstawowy.  DROP INDEX nazwa — usuwa indeks nazwa.  DROP FOREIGN KEY nazwa — usuwa klucz obcy nazwa.  ORDER BY nazwa — pozwala na ustawienie wierszy w porządku określonym przez dane w kolumnie nazwa.  RENAME [TO] nowa_nazwa — zmienia nazwę tabeli na nowa_nazwa.

Zmiany tego typu wykonywane są w taki sposób, że najpierw powstaje tymczasowa kopia tabeli, w której wprowadzana jest żądana zmiana. Następnie, jeśli operacja zakończy się powodzeniem, stara tabela jest usuwana, a kopia otrzymuje jej nazwę.

376

PHP5. Praktyczny kurs

Spróbujmy teraz wykonać kilka przykładów, aby przekonać się w praktyce, jak działają niektóre z wymienionych wyżej konstrukcji języka. Utwórzmy tabelę ksiazki, wykorzystując do tego celu instrukcję SQL w postaci: CREATE TABLE ksiazki( KsiazkaId INTEGER, AutorId INTEGER, Tytul VARCHAR(15) );

Szybko zauważymy, że nie zdefiniowaliśmy tutaj klucza podstawowego, którym powinna być kolumna KsiazkaId. Trzeba zatem naprawić to niedopatrzenie, wydając polecenie: ALTER TABLE Ksiazki ADD PRIMARY KEY (KsiazkaId);

Po jego wydaniu na ekranie zobaczymy komunikat: mysql> ALTER TABLE Ksiazki ADD PRIMARY KEY (KsiazkaId); Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0

Oznacza to, że operacja zakończyła się sukcesem. Gdybyśmy chcieli, aby pole KsiazkaId automatycznie zwiększało swoją wartość podczas dodawania rekordów do tabeli, czyli aby miało przypisany modyfikator AUTO_INCREMENT, musielibyśmy wykonać polecenie: ALTER TABLE Ksiazki MODIFY COLUMN KsiazkaId INTEGER AUTO_INCREMENT;

Mogłoby się także okazać, że pole Tytul jest zbyt krótkie i trzeba by zwiększyć jego maksymalną długość do 45 znaków. Taka modyfikacja również nie stanowi problemu, wystarczy instrukcja SQL w postaci: ALTER TABLE Ksiazki MODIFY COLUMN Tytul VARCHAR(45);

Nic nie stoi również na przeszkodzie, aby do tabeli dodać kolumnę ISBN o długości 10 znaków: ALTER TABLE Ksiazki ADD COLUMN ISBN VARCHAR(10);

Usuwanie tabel Tabele usuwa się za pomocą instrukcji DROP TABLE o schematycznej postaci: DROP TABLE nazwa1, nazwa2, ..., nazwaN

Przykładowo usunięcie tabeli o nazwie Klient wykonamy, wydając polecenie: DROP TABLE Klient

Natomiast równoczesne usunięcie dwóch tabel o nazwach Klient i Zamowienie uzyskamy, stosując konstrukcję: DROP TABLE Klient, Zamowienie

Rozdział 8. ♦ Współpraca z bazami danych

377

Typy danych w kolumnach Każda kolumna tabeli w bazie danych ma przypisany typ, który określa rodzaj danych, jakie mogą być w niej przechowywane. Występujące w SQL typy danych można podzielić na trzy główne rodzaje:  typy liczbowe,  typy daty i czasu,  typy łańcuchowe.

Każdy rodzaj to z kolei od kilku do kilkunastu typów i podtypów — nie będą tu jednak omawiane wszystkie możliwe kombinacje, a jedynie te najpopularniejsze, które przydadzą się w dalszych lekcjach (w tym typ specjalny NULL).

Typy liczbowe Typy liczbowe możemy podzielić na dwa rodzaje — całkowitoliczbowe oraz zmiennoprzecinkowe. Zgodnie z nazwami służą one do reprezentowania wartości całkowitych oraz zmiennoprzecinkowych (zmiennopozycyjnych, rzeczywistych). Typy całkowitoliczbowe zostały przedstawione w tabeli 8.1. Tabela 8.1. Wybrane typy liczbowe występujące w SQL Liczba zajmowanych bajtów

Typ

Zakres wartości

Opis

INTEGER

Od –2 147 483 648 (–231) do 2 147 483 647(231 – 1) dla liczb ze znakiem i od 0 do 4 294 967 295 (232 – 1) dla liczb bez znaku

4

Reprezentacja zwykłych wartości całkowitoliczbowych. Można również stosować synonim INT.

FLOAT

Od –3.402823466E+38 do 3.402823466E+38

4

Liczby zmiennoprzecinkowe pojedynczej precyzji.

DOUBLE

Od –1.7976931348623157E+308 do 1.7976931348623157E+308

8

Liczby zmiennoprzecinkowe podwójnej precyzji. Można również stosować synonimy DOUBLE PRECISION i REAL.

DECIMAL

Zmienna

Zmienna

Wartości z separatorem dziesiętnym. W wersjach przed 5.0.3 przechowywane jako łańcuch znaków. Zarówno całkowita maksymalna liczba znaków, jak i liczba znaków po separatorze dziesiętnym mogą być określane przez dodatkowe parametry. Można również stosować synonimy DEC, NUMERIC i FIXED.

378

PHP5. Praktyczny kurs

Przy tych typach dozwolone jest stosowanie modyfikatorów UNSIGNED oraz ZEROFILL. Pierwszy z nich oznacza, że wartość ma być traktowana jako liczba bez znaku (czyli niedopuszczalne są wartości ujemne). Drugi powoduje, że jeżeli liczba cyfr w danej wartości jest mniejsza od maksymalnej liczby wyświetlanych znaków, wolne miejsca zostaną dopełnione zerami. Zastosowanie atrybutu ZEROFILL powoduje, że automatycznie zostanie również zastosowany UNSIGNED.

Typy daty i czasu Typy pozwalające na reprezentację daty i czasu zostały zebrane w tabeli 8.2. Dane tych typów będą wyświetlane w formatach przedstawionych w kolumnie Opis tabeli, mogą być natomiast zapisywane w bazie przy użyciu różnych formatów. W przypadku typów DATE, DATETIME i TIMESTAMP dopuszczalne są formaty:  Ciąg znaków RRRR-MM-DD GG:MM:SS i RR-MM-DD GG:MM:SS. Pomiędzy

składowymi daty oraz pomiędzy składowymi czasu mogą występować dowolne znaki przestankowe, prawidłowe są zatem zapisy: 2012-05-20 20:12:55, 2012.05.20 20-12-55, 2012*05*20 20%12%55.  Ciąg znaków RRRR-MM-DD i YY-MM-DD. Pomiędzy składowymi daty mogą

występować dowolne znaki przestankowe, prawidłowe są zatem zapisy: 2012-05-20, 2012.05.20, 12*05*20.  Ciąg znaków RRRRMMDDGGMMSS i RRMMDDGGMMSS. Pomiędzy składowymi nie mogą

występować żadne znaki przestankowe, cały ciąg musi zaś reprezentować poprawną datę i czas. W związku z tym prawidłowe są zapisy: 20120520201255, 120520201255 — oba interpretowane jako 2012-05-20 20:12:55.  Ciąg znaków RRRRMMDD i RRMMDD. Pomiędzy składowymi nie mogą występować

żadne znaki przestankowe, cały ciąg musi zaś reprezentować poprawną datę. W związku z tym prawidłowe są zapisy: 20120520, 120520 — oba interpretowane jako 2012-05-20.  Wartość liczbowa zapisana jako RRRRMMDDGGMMSS, RRMMDDGGMMSS, RRRRMMDD lub RRMMDD, o ile reprezentuje poprawną datę i/lub czas.

W przypadku typu TIME są dopuszczalne następujące formaty:  Ciąg znaków D GG:MM:SS. Ciąg D reprezentuje dni i może przyjmować

wartości od 0 do 34. Możliwe są również warianty skrócone w następujących postaciach: GG:MM:SS, GG:MM, D GG:MM:SS, D GG:MM, D GG i SS. Poprawne są w związku z tym zapisy: 12:52:24, 12:52, 24.  Ciąg znaków GGMMSS. Pomiędzy składowymi nie mogą występować żadne

znaki przestankowe, cały ciąg musi zaś reprezentować poprawny czas. Poprawne są w związku z tym zapisy: 125224, 182931.  Wartość liczbowa zapisana jako GGMMSS, o ile reprezentuje poprawny czas. Możliwe są również alternatywne zapisy w postaci: SS, MMSS, GGMMSS.

W przypadku typu YEAR są dopuszczalne następujące formaty:  Ciąg znaków w formacie RRRR. Dopuszczalny zakres to 1901 – 2155.

Rozdział 8. ♦ Współpraca z bazami danych

379

Tabela 8.2. Typy daty i czasu Typ

Dopuszczalne wartości

Liczba zajmowanych bajtów

DATE

Od 1000-01-01 do 9999-12-31

3

Typ przeznaczony do reprezentacji daty. Wartości będą pobierane z bazy i wyświetlane w formacie RRRR-MM-DD.

DATETIME

Od 1000-01-01 00:00:00 do 9999-12-31 23:59:59

8

Typ przeznaczony do reprezentacji daty i czasu. Wartości będą pobierane z bazy i wyświetlane w formacie RRRR-MM-DD GG:MM:SS.

TIMESTAMP

Zależne od dodatkowych opcji

4

Typ przeznaczony do reprezentacji znacznika czasu.

TIME

Od -838:59:59 do 838:59:59

3

Typ przeznaczony do reprezentacji czasu. Wartości będą pobierane z bazy i wyświetlane w formacie GG:MM:SS lub GGG:MM:SS.

YEAR

Od 1901 do 2155

1

Typ przeznaczony do reprezentacji lat. Wartości będą pobierane z bazy i wyświetlane w formacie RRRR. Wartości tego typu są zapisywane na jednym bajcie.

Opis

 Ciąg znaków w formacie RR. Dopuszczalny zakres to 00 – 99. Ciągi od 00 do 69 są interpretowane jako lata 2000-2069, natomiast od 70 do 99 jako

1970 – 1999.  Wartość liczbowa w formacie RRRR. Dopuszczalny zakres to 1901 – 2155.  Wartość liczbowa w formacie RR. Dopuszczalny zakres to 1 – 99. Wartości od 1 do 69 są interpretowane jako lata 2001 – 2069, natomiast ciągi od 70 do 99 jako 1970 – 1999.

Jeśli w którymkolwiek z wymienionych przypadków zostanie podana wartość, która nie może zostać zinterpretowana jako poprawny argument danego typu, w bazie będzie ona interpretowana jako wartość specjalna:  dla typu DATE — 0000-00-00,  dla typu DATETIME — 0000-00-00 00:00:00,  dla typu TIMESTAMP — 00000000000000,  dla typu TIME — 00:00:00,  dla typu YEAR — 0000.

Typy łańcuchowe Typy łańcuchowe służą do przechowywania zarówno ciągów znaków, jak i danych binarnych. Można je podzielić na cztery grupy:

380

PHP5. Praktyczny kurs  typy CHAR i VARCHAR,  typy BINARY i VARBINARY,  typy BLOB i TEXT,  typy ENUM i SET.

Typy CHAR i VARCHAR Typy CHAR i VARCHAR służą do przechowywania łańcuchów znakowych, czyli tekstów. Oba wymagają podania długości łańcucha za nazwą typu w nawiasie okrągłym, czyli: CHAR(długość)

i VARCHAR(długość),

gdzie długość oznacza liczbę znaków5. Jeśli chcemy na przykład utworzyć kolumnę, która będzie mogła przechowywać do 20 znaków, należy zastosować konstrukcję: CHAR(20)

lub VARCHAR(20)

W przypadku typu CHAR cała kolumna w bazie danych będzie miała długość wskazaną parametrem długość. Jeśli zapisywane dane będą miały mniej znaków, pozostałe miejsca zostaną uzupełnione spacjami z prawej strony. Zarówno te spacje, jak i te znajdujące się na początku tekstu (!) będą usuwane podczas pobierania danych. Parametr długość może przyjmować wartości od 0 do 2556. W przypadku typu VARCHAR każdy wiersz kolumny ma zmienną długość wynikającą z liczby znaków zapisywanego łańcucha (plus 1 bajt niezbędny do zapisania liczby znaków łańcucha). Parametr długość może przyjmować następujące wartości (dla bazy MySQL): od 1 do 255 w wersjach przed 4.0.2, od 0 do 255 w wersjach od 4.0.2 oraz od 0 do 65 535, począwszy od wersji 5.0.3. W wersjach przed 5.0.3 podczas zapisywania danych do bazy usuwane są spacje z początku i końca tekstu. Począwszy od wersji 5.0.3, spacje te nie są usuwane ani podczas zapisu, ani podczas odczytu. W przypadku próby zapisania w wierszu kolumny większej liczby znaków, niż wynika to z wartości parametru długość, nadmiarowa liczba bajtów zostanie obcięta oraz wygenerowane będzie ostrzeżenie.

Typy BINARY i VARBINARY Typy BINARY i VARBINARY są podobne do CHAR i VARCHAR z tą różnicą, że przechowują łańcuchy bajtów, a nie znaków. Typ BINARY definiuje się w postaci: BINARY(długość) 5

W wersjach wcześniejszych niż 4.1 — liczbę bajtów.

6

W przypadku wersji wcześniejszych niż 3.23 — od 1 do 255.

Rozdział 8. ♦ Współpraca z bazami danych

381

Natomiast typ VARBINARY w postaci VARBINARY(długość)

Pozostałe właściwości są analogiczne. Należy jedynie zwrócić uwagę, że parametr długość w tym przypadku oznacza liczbę bajtów, a nie liczbę znaków.

Typy BLOB i TEXT Typy BLOB i TEXT służą do przechowywania dużej ilości danych. Typ BLOB (z ang. Binary Large Object) do przechowywania ciągów binarnych, natomiast TEXT — tekstowych. Oba typy dzielą się na cztery podtypy, które różnią się od siebie maksymalną wielkością danych, które mogą być za ich pomocą zapisane.

Typy ENUM i SET Typ ENUM jest typem wyliczeniowym pozwalającym ograniczyć zbiór wartości, który będzie mógł być przechowywany w danej kolumnie. Dopuszczalne wartości definiuje się w nawiasie okrągłym za nazwą typu, oddzielając je od siebie znakami przecinka, schematycznie: ENUM('wartość1', 'wartość2', ..., 'wartośćN')

W tak określonej kolumnie w pojedynczym wierszu będzie mogła się znaleźć tylko jedna z zadeklarowanych wartości (lub wartość NULL). Maksymalna liczba wartości w typie ENUM to 65 535. Przykładowo w kolumnie zdefiniowanej jako: ENUM('jeden', 'dwa', 'trzy')

będą dopuszczalne jedynie ciągi znaków: jeden, dwa i trzy. Typ SET jest również typem wyliczeniowym, który definiowany jest w analogiczny sposób jak ENUM, czyli: SET('wartość1', 'wartość2', ..., 'wartośćn')

W tym jednak przypadku każdy wiersz kolumny będzie mógł zawierać dowolny podzbiór zdefiniowanych wartości oddzielonych od siebie przecinkami. Czyli po zdefiniowaniu kolumny jako: SET('jeden', 'dwa')

w każdym wierszu będą mogły być zarówno wartości jeden i dwa, jak i jednocześnie jeden, dwa. Maksymalna liczba wartości możliwa do zadeklarowania w typie SET to 64.

Typ NULL Typ null jest typem specjalnym. Wartość typu null (zapisywana jako ciąg znaków null7) jest wartością pustą, czyli oznacza po prostu brak wartości. Jak to rozumieć? 7

Nie oznacza to jednak, że w tabeli bazy danych faktycznie jest zapisywany ciąg znaków null. To, w jaki sposób wartość ta jest reprezentowana wewnątrz bazy, jest uzależnione od danego serwera bazy danych.

382

PHP5. Praktyczny kurs

Jeśli mamy na przykład kolumnę przechowującą wartości całkowite, może się zdarzyć, że dla części wierszy właściwa wartość będzie nieznana, wtedy takie wiersze będą zawierały właśnie wartość specjalną null.

Zapytania Wprowadzanie danych Tabele utworzone w sposób opisany na wcześniejszych stronach trzeba w jakiś sposób wypełnić danymi. Służy do tego występująca w kilku wersjach instrukcja INSERT INTO. Omówiona zostanie jednak tylko jej podstawowa, najczęściej wykorzystywana postać, która całkowicie wystarcza do wykonywania tego typu zadań. Typowe wywołanie INSERT INTO ma postać: INSERT [INTO] tabela [(kolumna1, kolumna2, ..., kolumnaN)] VALUES (wartość1, wartość2, ..., wartośćN)

Powoduje ona wprowadzenie do tabeli nowego wiersza, w którym w polu kolumna1 została zapisana wartość wartość1, w polu kolumna2 — wartość wartość2 itd. Jak będzie to wyglądało w praktyce? Załóżmy, że w bazie istnieje tabela Klienci utworzona za pomocą instrukcji: CREATE TABLE klienci( KlientId INTEGER PRIMARY KEY, Imie VARCHAR(25), Nazwisko VARCHAR (25), Adres VARCHAR (60) );

i chcielibyśmy zapisać w niej nowy wiersz, np. dane Jana Kowalskiego zamieszkałego przy ulicy Klonowej 24 w Poznaniu, któremu został nadany identyfikator 1. Należałoby zastosować w takiej sytuacji instrukcję INSERT INTO w postaci: INSERT INTO klienci (KlientId, Imie, Nazwisko, Adres) VALUES (1, 'Jan', 'Kowalski', 'Klonowa 24, Poznań');

W celu zwiększenia czytelności można ją rozbić na kilka wierszy, np.: INSERT INTO klienci (KlientId, Imie, Nazwisko, Adres) VALUES (1, 'Jan', 'Kowalski', 'Klonowa 24, Poznań');

Zauważmy, że wszystkie wprowadzane ciągi znaków zostały ujęte w apostrofy (można także użyć znaków cudzysłowu). Jest to niezbędne, aby zapytanie zostało wykonane prawidłowo. Nie ma natomiast potrzeby ujmowania w znaki apostrofu wartości liczbowych. Nazwy kolumn w instrukcji INSERT są opcjonalne i w sytuacji gdy wprowadzamy wartości wszystkich pól, można je pominąć. A zatem powyższa instrukcja mogłaby mieć również postać: INSERT INTO Klienci VALUES (1, 'Jan', 'Kowalski', 'Klonowa 24, Poznań');

Rozdział 8. ♦ Współpraca z bazami danych

383

Istnieje również możliwość zapełnienia tylko niektórych kolumn. Jeśli na przykład nie znamy adresu, a w kolumnie Adres dopuszczalne są wartości puste NULL, można wydać polecenie: INSERT INTO Klienci VALUES (1, 'Jan', 'Kowalski', NULL);

lub INSERT INTO Klienci (KlientId, Imie, Nazwisko) VALUES (1, 'Jan', 'Kowalski');

Zwróćmy uwagę, że w pierwszym przypadku nie trzeba było podawać nazw kolumn, ponieważ wstawialiśmy wartości do wszystkich pól, ale w przypadku drugim były one niezbędne. Co by się jednak stało, gdybyśmy spróbowali wykonać pierwszą z powyższych instrukcji, ale pole Adres byłoby polem wymaganym (czyli zostałoby zdefiniowane jako NOT NULL)? Otóż zostałby zgłoszony komunikat o błędzie podobny do zaprezentowanego na rysunku 8.19.

Rysunek 8.19. Próba wprowadzenia wartości NULL do kolumny zdefiniowanej jako NOT NULL

Jest jednak sytuacja, w której do pola w kolumnie zdefiniowanej jako NOT NULL można wprowadzić wartość pustą. Są to pola typu AUTO_INCREMENT. W takim wypadku wprowadzenie wartości typu NULL jest nie tylko możliwe, ale czasem niezbędne do prawidłowego działania. Załóżmy, że tabela klienci została stworzona za pomocą nieco zmodyfikowanej instrukcji CREATE w postaci: CREATE TABLE klienci( KlientId INTEGER PRIMARY KEY AUTO_INCREMENT, Imie VARCHAR(25), Nazwisko VARCHAR (25), Adres VARCHAR (60) )

Jak zostało to wspomniane wyżej, pola typu AUTO_INCREMENT automatycznie uzyskują atrybut NOT NULL. Jednak instrukcja INSERT powinna mieć w tym przypadku postać: INSERT INTO klienci VALUES (NULL, 'Jan', 'Kowalski', 'Klonowa 24, Poznań');

Wprowadzana do kolumny KlientId wartość NULL zostanie bowiem automatycznie zamieniona na kolejną wartość całkowitą. W przypadku pierwszego wiersza będzie to 1, w przypadku drugiego — 2 itd. Nie oznacza to jednak, że do pola AUTO_INCREMENT nie można wprowadzić własnej wartości. Jeśli po wykonaniu powyższej instrukcji chcemy wprowadzić do bazy dane Andrzeja Nowaka zamieszkałego przy ulicy Lipowej 50 we Wrocławiu i chcemy jednocześnie nadać mu określony identyfikator, np. 25, to nic nie stoi na przeszkodzie, aby wykonać instrukcję: INSERT INTO klienci VALUES (25, 'Andrzej', 'Nowak', 'Lipowa 50, Wrocław');

384

PHP5. Praktyczny kurs

Musimy jednak pamiętać, że kolejny wiersz korzystający z funkcji AUTO_INCREMENT będzie miał już numer 26 (o jeden większy niż maksymalna ostatnio zapisana wartość w kolumnie). Warto również wiedzieć, że kolejność wprowadzania danych w instrukcji INSERT nie musi być taka sama jak struktura kolumn w tabeli. Prawidłowa jest zatem również konstrukcja: INSERT INTO klienci (Nazwisko, Imie, Adres, KlientId) VALUES ('Kowalski', 'Jan', 'Klonowa 24, Poznań', NULL);

W przypadku kolumn z atrybutem AUTO_INCREMENT można też pominąć wartość takiej kolumny przy wprowadzaniu danych. Wtedy zachowanie będzie takie samo, jak gdyby została użyta wartość NULL. Prawidłowa byłaby zatem też instrukcja: INSERT INTO klienci (Imie, Nazwisko, Adres) VALUES ('Jan', 'Kowalski', 'Klonowa 24, Poznań');

Oczywiście przy tego typu konstrukcji (zgodnie z podanymi wcześniej zasadami) konieczne jest wymienienie nazw kolumn w pierwszej części zapytania.

Pobieranie danych Dane zapisane w tabelach bazy można pobierać za pomocą instrukcji SELECT. Posiada ona wiele opcji i klauzul dodatkowych, przedstawiona zostanie zatem tylko jej podstawowa postać, która schematycznie wygląda następująco: SELECT kolumna1, kolumna2, ..., kolumnaN FROM tabela [WHERE warunek] [ORDER BY kolumna1, kolumna2, ..., kolumnaN [ASC | DEC]]

Oznacza ona: pobierz wartości wymienionych kolumn z tabeli tabela spełniających warunek warunek, a wyniki posortuj względem kolumn wymienionych w klauzuli ORDER BY rosnąco (ASC) lub malejąco (DESC). Aby zobaczyć, jak w praktyce działają proste zapytania typu SELECT, utworzymy tabelę przechowującą dane o osobach: imię, nazwisko oraz rok i miejsce urodzenia. Wykorzystamy do tego celu instrukcję CREATE TABLE w postaci: CREATE TABLE osoba ( Id INTEGER PRIMARY KEY, Imie VARCHAR(25), Nazwisko VARCHAR(35), Rok_urodzenia YEAR, Miejsce_urodzenia VARCHAR(35) );

Za pomocą serii instrukcji INSERT wprowadzimy teraz do tak utworzonej tabeli przykładowe dane, w sumie 10 wierszy. Będzie za to odpowiedzialna instrukcja SQL przedstawiona na listingu 8.1.

Rozdział 8. ♦ Współpraca z bazami danych

385

Listing 8.1. Instrukcje SQL wstawiające przykładowe dane do tabeli osoba INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT

INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO

Osoba Osoba Osoba Osoba Osoba Osoba Osoba Osoba Osoba Osoba

VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES

(1, 'Adam', 'Kowalski', 1964, 'Bydgoszcz'); (2, 'Adam', 'Nowak', 1972, 'Szczecin'); (3, 'Andrzej', 'Kowalski', 1986, 'Nidzica'); (4, 'Arkadiusz', 'Malinowski', 1986, 'Kielce'); (5, 'Andrzej', 'Malinowski', 1989, 'Kielce'); (6, 'Krzysztof', 'Nowicki', 1986, 'Bydgoszcz'); (7, 'Kacper', 'Adamczyk', 1971, 'Kielce'); (8, 'Kamil', 'Andrzejczak', 1971, 'Radom'); (9, 'Krzysztof', 'Arkuszewski', 1989, 'Szczecin'); (10, 'Kamil', 'Borowski', 1976, 'Skierniewice');

Pobranie wszystkich wierszy tabeli Najprostsza instrukcja SELECT pozwoli na pobranie wszystkich wierszy zawartych w tabeli. Będzie ona miała postać: SELECT * FROM osoba;

Efekt działania tego polecenia jest widoczny na rysunku 8.20. Widać zarówno to, że faktycznie wyświetlone zostały wszystkie dane wprowadzone uprzednio do tabeli osoba, jak i to, iż kolejność wierszy jest taka, w jakiej zostały wprowadzone do bazy. Rysunek 8.20. Efekt działania instrukcji wyświetlającej wszystkie wiersze tabeli Osoba

Sortowanie wyników Gdybyśmy chcieli, aby wyniki zostały posortowane, należałoby użyć dodatkowej klauzuli ORDER BY. W najprostszym przypadku sortowanie może się odbywać względem jednej kolumny. Domyślnie jest to sortowanie w porządku rosnącym (czyli domyślnie stosowana jest opcja ASC). Porządek sortowania można zmienić na malejący, stosując opcję DESC. Jeśli zatem chcemy wyświetlić wszystkie wiersze tabeli posortowane względem nazwiska w porządku alfabetycznym rosnącym, powinniśmy zastosować konstrukcję: SELECT * FROM osoba ORDER BY Nazwisko

lub: SELECT * FROM osoba ORDER BY Nazwisko ASC

386

PHP5. Praktyczny kurs

W przypadku gdybyśmy chcieli uzyskać sortowanie względem kolumny Nazwisko, ale w porządku malejącym, należy zastosować konstrukcję: SELECT * FROM osoba ORDER BY Nazwisko DESC

Sortowanie może się również odbywać względem większej liczby kolumn. Możemy sobie na przykład zażyczyć, żeby tablica została posortowana najpierw względem nazwiska, a następnie względem roku urodzenia. Zadanie takie zostanie zrealizowane przez instrukcję SELECT w postaci: SELECT * FROM osoba ORDER BY Nazwisko, Rok_urodzenia;

Pobieranie zawartości wybranych kolumn Jeżeli chcemy wyświetlić zawartość jedynie niektórych kolumn z wybranej tabeli, ich nazwy należy umieścić za słowem SELECT, oddzielając je znakami przecinka. Gdyby zatem interesowały nas jedynie imiona i nazwiska osób, należałoby wykonać polecenie: SELECT Imie, Nazwisko FROM osoba;

Uzyskamy wtedy efekt widoczny na rysunku 8.21. Rysunek 8.21. Pobranie z tabeli zawartości wybranych kolumn

Oczywiście tak pobrana zawartość kolumn może być również sortowana na takich samych zasadach jak opisane wyżej. Jeśli więc chcemy posortować wyniki względem kolumny Nazwisko w porządku rosnącym, powinniśmy skorzystać z polecenia: SELECT Imie, Nazwisko FROM osoba ORDER BY Nazwisko;

Jeśli natomiast chcielibyśmy posortować wyniki względem kolumny Imie w porządku malejącym, powinniśmy skorzystać z instrukcji: SELECT Imie, Nazwisko FROM osoba ORDER BY Imie DESC;

Zmiana nazw kolumn w wynikach zapytania W pewnych sytuacjach przy pobieraniu danych oryginalne nazwy kolumn tabeli mogą być niewygodne i trzeba będzie je zmienić. Taka zamiana może zostać wykonana w bardzo prosty sposób, jeśli występujące w zapytaniu SELECT nazwy kolumn zastąpimy sekwencjami o schematycznej postaci: nazwa_kolumny AS alias

Rozdział 8. ♦ Współpraca z bazami danych

387

gdzie nazwa_kolumny to nazwa oryginalnej kolumny, a alias to nazwa, jaka ma się pojawić w wynikach zapytania. Jeśli wykonamy na przykład instrukcję: SELECT Imie AS 'Imię', Nazwisko, Rok_urodzenia AS 'Rok urodzenia', Miejsce_urodzenia AS Miasto FROM osoba;

to w efekcie otrzymamy takie dane jak na rysunku 8.22.

Rysunek 8.22. Zmiana nazw kolumn w wynikach zapytania

Selektywne pobieranie danych Pobieranie całej zawartości tabeli stosuje się rzadko. W praktyce najczęściej interesuje nas pewien podzbiór danych. Otrzymanie określonego zestawu wierszy zapewni nam klauzula WHERE instrukcji SELECT. Należy za nią umieścić warunek, jaki muszą spełniać wiersze, aby znalazły się w wynikach zapytania. Warunek w klauzuli WHERE może zawierać operatory relacyjne przedstawione w tabeli 8.3 oraz logiczne z tabeli 8.4. Tabela 8.3. Operatory relacyjne w MySQL Operator Opis

Przykład

=

Operator równości. Zwraca wartość TRUE (PRAWDA), jeśli argument znajdujący się z lewej strony jest równy argumentowi znajdującemu się z prawej, lub FALSE (FAŁSZ) w przeciwnym razie. Uwaga! Jeżeli jeden z argumentów (lub oba) równy jest NULL, wynikiem działania jest również NULL.

Id=10, Nazwisko=’Kowalski’

<=>

Operator równości. Działa podobnie jak =, z tą różnicą, że jeśli jednym z argumentów jest NULL, wynikiem porównania jest FALSE, a jeśli oba argumenty są równe NULL, wynikiem jest TRUE.

Adres<=>NULL

<>

Zwraca wartość TRUE (PRAWDA), jeśli argument znajdujący się z lewej strony jest różny od tego z prawej, lub FALSE (FAŁSZ) w przeciwnym razie.

Id<>2, Nazwisko<>’Kowalski’

!=

Takie samo znaczenie jak <>.

Id!=2, Nazwisko!=’Kowalski’

<

Zwraca wartość TRUE (PRAWDA), jeśli argument znajdujący się z lewej strony jest mniejszy od tego z prawej, lub FALSE (FAŁSZ) w przeciwnym razie.

Id<10

388

PHP5. Praktyczny kurs

Tabela 8.3. Operatory relacyjne w MySQL — ciąg dalszy Operator Opis

Przykład

>

Zwraca wartość TRUE (PRAWDA), jeśli argument znajdujący się z lewej strony jest większy od tego z prawej, lub FALSE (FAŁSZ) w przeciwnym razie.

Id>10

<=

Zwraca wartość TRUE (PRAWDA), jeśli argument znajdujący się z lewej strony jest mniejszy od tego z prawej lub mu równy, lub FALSE (FAŁSZ) w przeciwnym razie.

Id<=10

>=

Zwraca wartość TRUE (PRAWDA), jeśli argument znajdujący się z lewej strony jest większy od tego z prawej lub mu równy, lub FALSE (FAŁSZ) w przeciwnym razie.

Id>=10

IS NULL

Zwraca wartość TRUE (PRAWDA), jeśli argument znajdujący się z lewej strony jest równy NULL, lub FALSE (FAŁSZ) w przeciwnym razie.

Adres IS NULL, Id IS NULL

IS NOT NULL

Zwraca wartość TRUE (PRAWDA), jeśli argument znajdujący się z lewej strony jest różny od NULL, lub FALSE (FAŁSZ) w przeciwnym razie.

Adres IS NOT NULL, Id IS NOT NULL

BETWEEN N AND M

Zwraca wartość TRUE (PRAWDA), jeśli argument znajdujący się z lewej strony ma wartość z przedziału od N do M, lub FALSE (FAŁSZ) w przeciwnym razie.

Id BETWEEN 10 AND 20

NOT BETWEEN N AND M

Zwraca wartość TRUE (PRAWDA), jeśli argument znajdujący się z lewej strony nie ma wartości z przedziału od N do M, lub FALSE (FAŁSZ) w przeciwnym razie.

Id NOT BETWEEN 10 AND 20

IN

Zwraca wartość TRUE (PRAWDA), jeśli argument znajdujący się z lewej strony jest równy jednej z wartości wymienionych w nawiasie okrągłym za operatorem, lub FALSE (FAŁSZ) w przeciwnym razie.

Id IN(1, 3, 5), Nazwisko IN(’Kowalski’, ’Nowak’)

NOT IN

Zwraca wartość TRUE (PRAWDA), jeśli argument znajdujący się z lewej strony nie jest równy jednej z wartości wymienionych w nawiasie okrągłym za operatorem, lub FALSE (FAŁSZ) w przeciwnym razie.

Id NOT IN(1, 3, 5), Nazwisko NOT IN(’Kowalski’, ’Nowak’)

Tabela 8.4. Operatory logiczne w MySQL Operator Opis

Przykład

AND

Iloczyn logiczny. Zwraca wartość TRUE wtedy i tylko wtedy, gdy oba argumenty mają wartość TRUE. W każdym innym przypadku zwraca wartość FALSE.

Imie=’Jan’ AND Nazwisko=’Kowalski’

&&

Iloczyn logiczny. Znaczenie takie samo jak AND.

Imie=’Jan’ && Nazwisko=’Kowalski’

OR

Suma logiczna. Zwraca wartość TRUE, gdy przynajmniej jeden z argumentów ma wartość TRUE. W każdym innym przypadku zwraca wartość FALSE.

Imie=’Jan’ OR Imie=’Andrzej’

||

Suma logiczna. Znaczenie takie samo jak OR.

Imie=’Jan’ || Imie=’Andrzej’

Rozdział 8. ♦ Współpraca z bazami danych

389

Tabela 8.4. Operatory logiczne w MySQL — ciąg dalszy Operator Opis

Przykład

XOR

Logiczna różnica symetryczna (logiczna alternatywa wykluczająca). Zwraca wartość TRUE, gdy oba argumenty mają różne wartości logiczne, oraz wartość FALSE, gdy mają takie same.

Kolumna1 XOR Kolumna2, Pole XOR 64

NOT

Negacja logiczna. Zmienia wartość argumentu na przeciwną. Jeśli wartością argumentu było TRUE, wynikiem będzie FALSE, jeśli natomiast było to FALSE, wynikiem będzie TRUE.

NOT Aktywny

!

Negacja logiczna. Znaczenie takie samo jak NOT.

! Aktywny

Oprócz przedstawionych w powyższych tabelach operatorów relacyjnych i logicznych stosunkowo często wykorzystywane są także dwie funkcje operujące na ciągach znaków — są to LIKE i NOT LIKE. Wywołanie funkcji LIKE ma postać: wyrażenie LIKE wzorzec

Zwraca ona wartość TRUE, jeśli wyrażenie pasuje do wzorca, lub wartość FALSE, jeśli nie pasuje. Jako wyrażenie zazwyczaj jest stosowana nazwa kolumny. Argument wzorzec może zawierać dwa znaki specjalne. Pierwszy z nich to %, który zastępuje dowolną liczbę znaków, drugi znak specjalny zastępujący dokładnie jeden znak to _ (podkreślenie). Oznacza to, że do przykładowego wzorca Jan% będą pasowały ciągi Jan, Janusz, Janek, Janowski itp., a do wzorca Warszaw_ — ciągi Warszawa, Warszawy, Warszawo itp. Funkcja NOT LIKE ma postać: wyrażenie NOT LIKE wzorzec

i działa odwrotnie do LIKE, czyli zwraca wartość TRUE, jeśli wyrażenie nie jest zgodne ze wzorcem, lub wartość FALSE w przeciwnym razie. Spróbujmy teraz wykonać kilka praktycznych przykładów wykorzystujących niektóre z opisanych operatorów i funkcji. Operować będziemy na wcześniej stworzonej i wypełnionej danymi tabeli osoba. Pobierzmy zatem wszystkie wiersze tabeli osoba, które w polu Nazwisko mają zapisaną wartość Kowalski, czyli odczytajmy dane wszystkich osób o nazwisku Kowalski. Takie zadanie wykonane zostanie przez instrukcję: SELECT * FROM osoba WHERE Nazwisko='Kowalski';

Efekt działania został zaprezentowany na rysunku 8.23. W klauzuli WHERE został wykorzystany operator =. Rysunek 8.23. Efekt działania instrukcji pobierającej dane osób o nazwisku Kowalski

390

PHP5. Praktyczny kurs

Wykorzystajmy teraz operator większości do pobrania listy osób urodzonych po roku 1985. Zapytanie SQL będzie miało postać: SELECT * FROM osoba WHERE Rok_urodzenia > 1985;

Efekt jego działania został przedstawiony na rysunku 8.24. Analogiczny efekt moglibyśmy również osiągnąć, wykorzystując operator >= w postaci: SELECT * FROM osoba WHERE Rok_urodzenia >= 1986;

Rysunek 8.24. Efekt działania zapytania pobierającego listę osób urodzonych po roku 1995

Aby w wyniku zapytania uzyskać wartości pól z danego zakresu, można użyć dwóch operatorów porównywania oraz operatora logicznego lub też skorzystać z operatora BETWEEN. Załóżmy, że chcemy pobrać listę osób o identyfikatorach z przedziału 3 – 6. Można w takim wypadku wykonać zapytanie w postaci: SELECT * FROM osoba WHERE Id >= 3 AND Id <= 6;

lub SELECT * FROM osoba WHERE Id BETWEEN 3 AND 6;

Efekt działania takiego zapytania został przedstawiony na rysunku 8.25. Rysunek 8.25. Efekt działania zapytania pobierającego dane z określonego przedziału

Jeśli chcielibyśmy, aby w wynikach zostały uwzględnione wartości z pewnego zbioru, a nie przedziału, moglibyśmy użyć zarówno serii instrukcji warunkowych połączonych operatorami logicznymi, jak i operatora IN. Działanie będzie takie samo, jednak ta druga możliwość pozwala na prostszy i dużo czytelniejszy zapis instrukcji. Jeśli zatem chcemy otrzymać dane osób o identyfikatorach 3, 5 i 7, możemy zastosować instrukcję: SELECT * FROM osoba WHERE Id=3 OR Id=5 OR Id=7;

lub też: SELECT * FROM osoba WHERE Id IN(3, 5, 7);

Efekt działania (w obu przypadkach będzie identyczny) został przedstawiony na rysunku 8.26.

Rozdział 8. ♦ Współpraca z bazami danych

391

Rysunek 8.26. Pobranie danych, dla których wartość pola Id należy do określonego zbioru

Odwrotnością IN jest NOT IN, które pozwala na pobranie danych nienależących do wymienionego zbioru. Również i w tym wypadku możliwe jest użycie ekwiwalentu w postaci serii instrukcji warunkowych połączonych operatorami logicznymi. Jeśli zatem chcemy wyświetlić dane osób o identyfikatorach różnych od 1, 3, 5, 7 i 9, możemy skorzystać z instrukcji: SELECT * FROM osoba WHERE Id <> 1 AND Id <> 3 AND Id <> 5 AND Id <> 7 AND Id <> 9;

bądź: SELECT * FROM osoba WHERE Id NOT IN(1, 3, 5, 7, 9);

Efekt ich działania został zaprezentowany na rysunku 8.27. Rysunek 8.27. Efekt działania instrukcji wykorzystującej operator NOT IN

Funkcja LIKE (można ją traktować jako operator działający na ciągach znaków) pozwoli na pobranie z tabeli wierszy, których wybrane pola pasują do zdefiniowanego wzorca. Gdybyśmy chcieli na przykład poznać dane wszystkich osób, których imiona zaczynają się od ciągu Ka, powinniśmy zastosować instrukcję: SELECT * FROM osoba WHERE Imie LIKE 'Ka%';

Efekt działania tego polecenia jest widoczny na rysunku 8.28. Rysunek 8.28. Wyświetlenie wierszy zgodnych z przekazanym wzorcem

Oczywiście warunek w klauzuli WHERE nie musi ograniczać się do danych pobieranych z jednej kolumny, można stosować warunki złożone połączone operatorami logicznymi. Jeśli na przykład interesowałyby nas dane osób, których imiona zaczynają się na literę A, urodzonych po roku 1970 w Kielcach lub w Szczecinie, należałoby zastosować konstrukcję: SELECT * FROM osoba WHERE Imie LIKE 'A%' AND Rok_urodzenia > 1970 AND Miejsce_urodzenia IN ('Kielce', 'Szczecin');

392

PHP5. Praktyczny kurs

Efekt działania tego zapytania został zaprezentowany na rysunku 8.29.

Rysunek 8.29. Efekt działania zapytania złożonego

Modyfikacja danych Dane zapisane w tabelach mogą być zmieniane i modyfikowane. Służy do tego instrukcja UPDATE, która ma ogólną postać: UPDATE tabela SET kolumna1=wartość1, kolumna2=wartość2, ..., kolumnaN=wartośćN [WHERE warunek]

Oznacza ona: w tabeli tabela, w wierszach spełniających warunek warunek, zmień pole kolumna1 na wartość1, kolumna2 na wartość2 itd. Klauzula WHERE jest opcjonalna i może zostać pominięta — w takiej sytuacji zmianie ulegną wszystkie wiersze znajdujące się w tabeli. Zastosowanie przykładowej instrukcji: UPDATE osoba SET Imie='Jan';

spowoduje zamianę imion wszystkich osób, których dane zapisane są w tabeli, na ciąg Jan (rysunek 8.30). Zawsze należy więc pamiętać o zastosowaniu odpowiedniego warunku, w przeciwnym razie można osiągnąć efekty niezgodne z założeniami. Oczywiście może się też zdarzyć, że chcemy zmienić wszystkie wartości w danej kolumnie, wtedy taka instrukcja będzie jak najbardziej poprawna. Rysunek 8.30. Zastosowanie skróconej wersji instrukcji UPDATE spowodowało zamianę wszystkich wartości w kolumnie Imie

Najczęściej jednak aktualizuje się jeden lub kilka wierszy, stosowanie klauzuli WHERE jest więc konieczne. Jeśli okaże się na przykład, że w tablicy zapisaliśmy błędny rok urodzenia Andrzeja Malinowskiego, któremu w tabeli został przypisany identyfikator 5, i trzeba zamienić wartość 1989 na 1988, należałoby zastosować instrukcję: UPDATE osoba SET Rok_urodzenia=1988 WHERE id=5;

Został tu zastosowany warunek id=5, ponieważ pole id jest kluczem podstawowym jednoznacznie identyfikującym każdy rekord.

Rozdział 8. ♦ Współpraca z bazami danych

393

Można też jednocześnie zmodyfikować kilka pól w danym wierszu. Gdybyśmy chcieli zmienić imię, rok i miejsce urodzenia osoby o identyfikatorze 8, moglibyśmy zastosować instrukcję: UPDATE osoba SET Nazwisko='Andrzejewski', Rok_urodzenia=1990, Miejsce_urodzenia='Kielce' WHERE Id=8;

Tym samym Kamil Andrzejczak urodzony w 1971 roku w Radomiu stanie się Kamilem Andrzejewskim, urodzonym w 1990 roku w Kielcach. W warunku klauzuli WHERE można używać operatorów i funkcji opisanych w sekcji „Selektywne pobieranie danych”, można zatem jednocześnie wskazać więcej niż jeden rekord do modyfikacji. Jeśli zatem wykryjemy, że osoby o identyfikatorach 4 i 5 mają błędnie przypisane miejsce urodzenia, którym nie są Kielce, ale Radom, do modyfikacji wykorzystamy instrukcję: UPDATE osoba SET Miejsce_urodzenia='Radom' WHERE id=4 OR id=5;

lub UPDATE osoba SET Miejsce_urodzenia='Radom' WHERE ID IN(4,5);

Podobnie jeśli odkryjemy, że w wielu wierszach tabeli zostało nieprawidłowo zapisane np. nazwisko Kowalski — i zamiast niego widnieje ciąg Kowalksi — możemy takie niedopatrzenie szybko naprawić, wykonując instrukcję: UPDATE osoba SET Nazwisko='Kowalski' WHERE Nazwisko='Kowalksi';

Usuwanie danych Wiadomo już, jak dodawać dane do tabeli, jak je pobierać i modyfikować. Do omówienia została jeszcze równie ważna kwestia usuwania wierszy z tabel. Służy do tego instrukcja DELETE o schematycznej postaci: DELETE FROM tabela WHERE warunek

Oznacza ona: usuń z tabeli tabela wszystkie wiersze spełniające warunek warunek. Jeśli warunek zostanie pominięty, zostaną usunięte wszystkie wiersze z tabeli. Jeśli zatem zastosujemy instrukcję: DELETE FROM osoba;

po jej wykonaniu tabela osoba nie będzie zawierała żadnych danych. Taką konstrukcję należy stosować z uwagą, gdyż serwer nie wygeneruje żadnego ostrzeżenia czy dodatkowego pytania. Wpisanie powyższej konstrukcji i zatwierdzenie jej klawiszem Enter spowoduje natychmiastowe skasowanie danych! Warunek w klauzuli WHERE ma taką samą postać jak opisywany podczas omawiania instrukcji SELECT. Jeśli zatem chcemy usunąć z tabeli osoba dane osoby o identyfikatorze 5, zastosujemy konstrukcję: DELETE FROM osoba WHERE Id=5;

394

PHP5. Praktyczny kurs

Jeśli chcemy usunąć kilka rekordów o różnych identyfikatorach, zastosujemy zapewne operator IN. Jeśli na przykład skasowane mają zostać dane osób o id równym 3, 5 i 7, wykorzystamy zapewne instrukcję: DELETE FROM osoba WHERE ID IN (3, 5, 7);

W celu usunięcia danych osób o identyfikatorach należących do określonego przedziału, np. 4 – 8, skorzystamy z zapytania wykorzystującego operator BETWEEN w postaci: DELETE FROM osoba WHERE ID BETWEEN 4 AND 8;

lub też z zapytania wykorzystującego operatory relacyjne <= i >= oraz operator logiczny AND: DELETE FROM osoba WHERE ID >= 4 AND ID <= 8;

Nic nie stoi również na przeszkodzie, aby usunąć z tabeli dane wszystkich osób o nazwisku Kowalski: DELETE FROM osoba WHERE Nazwisko='Kowalski';

lub też wszystkich Kowalskich i Nowaków: DELETE FROM osoba WHERE Nazwisko='Kowalski' OR Nazwisko='Nowak';

Lekcja 24. PHP i bazy danych PHP doskonale współpracuje z MySQL i SQLite, podobnie jak z innymi systemami baz danych. Obsługa tych baz może być realizowana na dwa sposoby — pierwszy jest tradycyjny, proceduralny, polegający na wywoływaniu funkcji obsługujących połączenia, a drugi obiektowy. W tej lekcji zajmiemy się podejściem proceduralnym, a w kolejnej — obiektowym.

PHP i MySQL Nawiązywanie połączenia Do nawiązania połączenia z bazą danych służy funkcja mysql_connect. Ma ona schematyczną postać: mysql_connect(["host"[, "użytkownik"[, "hasło"[, nowe_połączenie[, flagi]]]]])

Parametr host to ciąg znaków określający nazwę lub adres IP serwera, na którym znajduje się baza danych (w naszym przypadku będzie to localhost), użytkownik to ciąg znaków określający nazwę użytkownika, natomiast hasło to ciąg znaków określający hasło użytkownika. Funkcja zwraca identyfikator nawiązanego połączenia, jeśli udało się je nawiązać, lub wartość false w przeciwnym razie. Parametr host może zawierać określenie portu, na którym oczekuje na połączenia serwer MySQL (port domyślny to 3306). W takim wypadku będzie miał on postać: nazwa_hosta:numer_portu

Rozdział 8. ♦ Współpraca z bazami danych

395

np.: localhost:3306

Jeśli w jednym skrypcie funkcja mysql_connect zostanie wywołana więcej niż raz z tymi samymi parametrami, a połączenie pomiędzy wywołaniami nie zostanie zerwane, domyślnie nie nastąpi nawiązanie nowego połączenia. W takim przypadku jest po prostu zwracany identyfikator połączenia już istniejącego. To zachowanie może być zmodyfikowane przez ustawienie parametru nowe_połączenie na true (standardowo parametr ten ma wartość false). Wtedy każde wywołanie mysql_connect będzie tworzyło nowe połączenie. Ostatni parametr — flagi — umożliwia modyfikację domyślnych zachowań protokołu komunikacyjnego, nie będziemy go jednak wykorzystywać w przykładach. Jeśli parametry host, użytkownik i hasło zostaną pominięte, przyjęte zostaną następujące wartości domyślne:  dla parametru host — ciąg znaków localhost:3306,  dla parametru użytkownik — jeśli PHP pracuje w trybie safe_mode,

jest to ciąg znaków określający użytkownika będącego właścicielem procesu serwera, w przeciwnym razie jest to użytkownik domyślny,  dla parametru hasło — pusty ciąg znaków.

To zachowanie może być zmienione przez modyfikację zmiennych w pliku php.ini:  mysql.default_port — określa domyślny port,  mysql.default_host — określa domyślną nazwę lub adres serwera MySQL,  mysql.default_user — określa domyślną nazwę użytkownika,  mysql.default_password — określa domyślne hasło.

Jeśli zatem na serwerze lokalnym localhost jest uruchomiony serwer MySQL i istnieje na nim konto użytkownika o nazwie php, któremu zostało przypisane hasło test (jest to konfiguracja utworzona w poprzednich lekcjach), to połączenie może zostać wykonane za pomocą instrukcji: mysql_connect("localhost", "php", "test");

Kończenie połączenia Połączenie otwarte za pomocą funkcji mysql_connect zostanie zamknięte automatycznie, gdy skrypt zakończy działanie lub gdy zostanie wywołana funkcja mysql_close. Dobrym obyczajem programistycznym jest jawne zamykanie połączenia za pomocą tej funkcji. Schematyczne wywołanie mysql_close ma postać: mysql_close([identyfikator])

gdzie identyfikator to identyfikator połączenia, które ma zostać zamknięte, zwrócony wcześniej przez funkcję mysql_connect. Jeżeli parametr identyfikator nie zostanie podany, zamknięte zostanie ostatnio otwarte połączenie. Funkcja mysql_close zwraca wartość true, jeżeli operacja przez nią wykonywana zakończyła się sukcesem, lub false w przeciwnym razie.

396

PHP5. Praktyczny kurs

Wybór bazy Po nawiązaniu połączenia z serwerem należy wybrać bazę. Służy do tego funkcja mysql_select_db, która jest odpowiednikiem polecenia use database wykorzystywanego we wcześniejszych rozdziałach przy pracy z klientem mysql. Wywołanie mysql_ select_db ma schematyczną postać: mysql_select_db("nazwa_bazy"[, identyfikator])

gdzie nazwa_bazy to nazwa bazy, do której będą wysyłane zapytania, a identyfikator to identyfikator połączenia zwrócony przez funkcję mysql_connect. Jeśli identyfikator połączenia nie zostanie podany, wykorzystane będzie ostatnio otwarte połączenie. Spróbujmy zatem wykonać prosty skrypt, który przetestuje, czy komunikacja między PHP a MySQL działa prawidłowo. Został on zaprezentowany na listingu 8.2. Listing 8.2. Skrypt testujący połączenie z serwerem baz danych '); } else{ echo 'Połączenie z bazą danych zostało nawiązane...
'; } if(!mysql_select_db('testphp', $db_lnk)){ echo 'Wystąpił błąd podczas wyboru bazy danych: test
'; } else{ echo 'Została wybrana baza danych: test
'; } if(!mysql_close($db_lnk)){ echo 'Wystąpił błąd podczas zamykania połączenia z serwerem MySQL...
'; } else{ echo 'Połączenie z serwerem MySQL zostało zamknięte...
'; } ?>

We wszystkich dalszych przykładach zostało przyjęte założenie, że na serwerze MySQL (uruchomionym na komputerze lokalnym localhost) zostało założone konto o nazwie php i haśle test, które pozwala na wykonywanie wszystkich operacji wymaganych przez dany skrypt, oraz że operacje te są wykonywane w bazie o nazwie testphp.

Funkcja mysql_connect wykonuje tu połączenie z serwerem MySQL pracującym na komputerze lokalnym localhost, logując użytkownika php korzystającego z hasła test. Wynik działania funkcji jest przypisywany zmiennej $db_lnk. W zależności od tego, czy został zwrócony identyfikator nawiązanego połączenia czy też wartość false oznaczająca niepowodzenie połączenia, do przeglądarki jest wysyłany stosowny komunikat. Jeśli połączenie z bazą danych zakończy się sukcesem, wykonywana jest funkcja mysql_select_db wybierająca bazę danych o nazwie testphp. Również w tym przy-

Rozdział 8. ♦ Współpraca z bazami danych

397

padku jest sprawdzane, czy operacja została wykonana poprawnie, a do przeglądarki wysyłana jest odpowiednia informacja. Na końcu skryptu połączenie z bazą jest zamykane przez wywołanie funkcji mysql_close. Jeśli uruchomimy skrypt, a wszystkie operacje zostaną wykonane bez błędów, na ekranie zobaczymy widok jak na rysunku 8.31. Ponieważ (jak wiadomo z wcześniejszych opisów) niepodanie identyfikatora w wywołaniach mysql_select_db i mysql_close powoduje wykonanie operacji na ostatnio otwartym połączeniu, obie funkcje można również wywołać bez podawania argumentu $db_lnk. Rysunek 8.31. Nawiązanie połączenia z bazą danych

Wykonywanie zapytań Zapytania są wysyłane do bazy za pomocą funkcji mysql_query, której w postaci argumentów należy podać treść zapytania oraz (opcjonalnie) identyfikator połączenia. Schematycznie takie wywołanie ma postać: mysql_query("zapytanie"[, identyfikator])

Jeżeli nie będzie podany identyfikator, zostanie wykorzystane ostatnio otwarte połączenie. Ciąg tworzący zapytanie nie powinien być zakończony znakiem średnika. Wartość zwracana przez funkcję jest zależna od typu zapytania. Jeśli było to zapytanie pobierające dane (np. typu SELECT), wartością zwracaną jest identyfikator zasobów (pozwalający na dalsze przetwarzanie danych), o ile wykonanie zapytania zakończyło się sukcesem, lub false, jeżeli ta operacja się nie powiodła. W przypadku pozostałych typów zapytań (modyfikujących dane) zwracaną wartością jest true, jeśli zapytanie było poprawne, lub false w przeciwnym razie.

Zapytania pobierające dane W przypadku zapytań typu SELECT funkcja mysql_query zwraca identyfikator zasobów, który może zostać następnie użyty do odczytu danych zwróconych przez zapytanie. Istnieje kilka funkcji odczytujących takie dane, najpopularniejsze to mysql_fetch_row i mysql_fetch_array. Pomocna może być także funkcja mysql_num_rows, która pozwala na określenie, ile wierszy znajduje się w wynikach zapytania. Każde wywołanie funkcji mysql_fetch_row zwraca kolejny wiersz z tabeli będącej wynikiem zapytania. Dane zwracane są w tablicy, w której kolejne komórki zawierają dane z kolejnych kolumn tabeli wynikowej. Jeśli zostaną odczytane wszystkie wiersze, funkcja zwraca wartość false. Oznacza to, że wszystkie wyniki zapytania mogą zostać odczytane w pętli while o schematycznej postaci:

398

PHP5. Praktyczny kurs while($arr = mysql_fetch_row($result)){ //instrukcje przetwarzające wyniki }

gdzie $arr to tablica, do której będą zapisywane dane z kolejnych wierszy, a $result to zmienna zawierająca identyfikator zasobów zwrócony przez funkcję mysql_query. Drugi wariant to użycie funkcji mysql_num_rows do pobrania liczby wierszy z tabeli wynikowej oraz pętli typu for. Taka konstrukcja miałaby schematyczną postać: $count = mysql_num_rows($result); for($i = 0; $i < $count; $i++){ //instrukcje przetwarzające wyniki }

Funkcja mysql_fetch_array działa podobnie, z tą różnicą, że zwraca tabelę asocjacyjną, w której kluczami są nazwy kolumn tabeli zwróconej przez zapytanie. Zobaczmy najpierw, jak w praktyce odczytać dane z wybranej tabeli, wykorzystując do tego funkcję mysql_fetch_row. Niech będzie to wykorzystywana w lekcji 23. tabela osoba. Odpowiedni kod został zaprezentowany na listingu 8.3. Listing 8.3. Odczytanie zawartości tabeli osoba Odczyt danych z bazy '; echo ''; exit; } if(!mysql_select_db('testphp')){ mysql_close(); echo 'Błąd podczas wyboru bazy danych: testphp
'; echo ''; exit; } $query = 'SELECT * FROM osoba'; if(!$result = mysql_query($query, $db_lnk)){ mysql_close(); echo 'Wystąpił błąd: nieprawidłowe zapytanie...
'; echo ''; exit; } ?>

Rozdział 8. ♦ Współpraca z bazami danych

399

"; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; } ?>
Id Imię Nazwisko Rok urodzenia Miejsce urodzenia
$row[0]$row[1]$row[2]$row[3]$row[4]
'; } ?>

Połączenie z bazą jest nawiązywane w sposób omówiony na wcześniejszych stronach. Następnie zmiennej $query jest przypisywany ciąg znaków tworzący treść zapytania SQL: SELECT * FROM osoba

które pobiera wszystkie wiersze z tabeli osoba (zwróćmy uwagę, że nie jest ono zakończone znakiem średnika). Zapytanie jest wysyłane do serwera przez wywołanie funkcji mysql_query, a rezultat jej działania jest przypisywany zmiennej $result. Jeżeli wynikiem jest false, oznacza to błąd w zapytaniu i skrypt kończy działanie. W przeciwnym razie uzyskane wyniki są pobierane w pętli while. Jest w niej wywoływana funkcja mysql_fetch_row, która pobiera kolejne wiersze tabeli wynikowej i zwraca je w postaci tablicy. Pod indeksem 0 tej tablicy znajduje się wartość z kolumny Id, pod 1 — z kolumny Imie, pod 2 — z kolumny Nazwisko itd. Innymi słowy, kolejność kolumn w tablicy $row będzie taka sama jak zwrócona przez wysłane zapytanie. Odczytane wyniki są umieszczane w tabeli HTML w celu ich sformatowania. Po uruchomieniu skryptu powinniśmy zobaczyć w przeglądarce widok taki, jak zaprezentowany na rysunku 8.32. Jeżeli do pobierania danych wykorzystamy funkcję mysql_fetch_array, zamiast indeksów kolumn będziemy mogli wykorzystać ich nazwy. To znaczy (zakładając, że zmienna $row zawiera wynik działania funkcji mysql_fetch_array), będzie można stosować konstrukcję o schematycznej postaci: $row['nazwa_kolumny']

400

PHP5. Praktyczny kurs

Rysunek 8.32. Zawartość tabeli Osoba wyświetlona w przeglądarce

Pętla while pobierająca dane będące wynikiem zapytania mogłaby mieć zatem również postać przedstawioną na listingu 8.4. Pozostała część skryptu pozostanie bez zmian. Listing 8.4. Wykorzystanie funkcji mysql_fetch_array do pobrania danych "; echo "{$row['Id']}"; echo "{$row['Imie']}"; echo "{$row['Nazwisko']}"; echo "{$row['Rok_urodzenia']}"; echo "{$row['Miejsce_urodzenia']}"; echo ""; } ?>

Zapytania aktualizujące dane W przypadku zapytań modyfikujących dane w bazie funkcja mysql_query zwraca jedynie wartość true, jeśli serwer przyjął zapytanie, lub wartość false, jeśli zostało ono odrzucone. Liczbę wierszy w bazie, na które zapytanie miało wpływ, można odczytać przez wywołanie funkcji mysql_affected_rows. Wywołanie mysql_affected_rows ma postać: mysql_affected_rows([identyfikator])

gdzie identyfikator jest identyfikatorem połączenia z bazą zwróconym przez funkcję mysql_connect. Jeśli parametr identyfikator zostanie pominięty, przyjęte będzie, że chodzi o ostatnio otwarte połączenie. Zobaczmy, jak w praktyce dodać wiersz do znanej nam z poprzedniej lekcji tabeli osoba. Takie zadanie wykonuje skrypt widoczny na listingu 8.5.

Rozdział 8. ♦ Współpraca z bazami danych

401

Listing 8.5. Skrypt dodający pojedynczy wiersz do tabeli Osoba '); } if(!mysql_select_db('testphp')){ mysql_close(); exit ('Błąd podczas wyboru bazy danych: testphp
'); } $query = "INSERT INTO osoba VALUES("; $query .= "11, 'Maciej', 'Nowak', 1982, 'Lublin'"; $query .= ")"; if(!$result = mysql_query($query, $db_lnk)){ mysql_close(); exit ('Błąd: zapytanie zostało odrzucone...
'); } $rowsNo = mysql_affected_rows($db_lnk); echo "Liczba dodanych rekordów: $rowsNo
"; if(!mysql_close()){ echo 'Błąd podczas zamykania połączenia z serwerem MySQL...
'; } ?>

Połączenie z serwerem jest nawiązywane za pomocą funkcji mysql_connect, a baza testphp wybierana za pomocą mysql_select_db. Ten fragment kodu ma postać analogiczną do tej z wcześniejszych przykładów. Następnie zmiennej query jest przypisywana treść zapytania SQL w postaci: INSERT INTO osoba VALUES(11, 'Maciej', 'Nowak', 1982, 'Lublin')

Dodaje ona do tabeli osoba nowy wpis. Zapytanie to jest wysyłane do serwera za pomocą funkcji mysql_query. Dalej za pomocą instrukcji if jest sprawdzane, czy wywołanie funkcji zwróciło wartość true, czy false. Jeśli jest to false, czyli zapytanie zostało odrzucone, do przeglądarki jest wysyłany stosowny komunikat, a połączenie z bazą jest zamykane. Jeżeli jednak zwróconą wartością jest true, jest wykonywana dalsza część skryptu. Zmiennej rowsNo przypisuje się wartość zwróconą przez wywołanie funkcji mysql_ affected_rows, która określa, na ile wierszy w bazie miało wpływ ostatnio wykonane zapytanie. Wartość ta jest następnie wysyłana do przeglądarki wraz z odpowiednim komunikatem, tak aby można było stwierdzić, czy na pewno został dodany jeden wiersz. Na zakończenie połączenie z bazą jest zamykane za pomocą funkcji mysql_close.

402

PHP5. Praktyczny kurs

PHP i SQLite Nawiązywanie połączenia Do nawiązania połączenia z bazą danych służy funkcja sqlite_open. Ma ona schematyczną postać: sqlite_open("plik"[, "tryb"[, $msg]])

Parametr plik określa nazwę pliku, w którym znajduje się baza danych lub w którym ma zostać utworzona, tryb w założeniach określa atrybuty pliku, jednak obecnie jest on ignorowany i zaleca się wykorzystywanie wartości 0666. Natomiast $msg to przekazana przez referencję zmienna, w której zostanie zapisany opis błędu, o ile taki wystąpi. Funkcja zwraca identyfikator nawiązanego połączenia, jeśli udało się je nawiązać, lub wartość false w przeciwnym razie. Jeśli zatem chcielibyśmy nawiązać połączenie z bazą zawartą w pliku /var/wwwdata/testphp.db, możemy zastosować wywołanie: sqlite_open("/var/wwwdata/testphp.db")

lub też: sqlite_open("/var/wwwdata/testphp.db", 0666, $msg)

W tym drugim przypadku, jeśli wystąpi błąd (np. na dysku nie będzie katalogu /var/ www/bazy/), będziemy mogli uzyskać bardziej szczegółową informację o jego przyczynie. Jak to zrobić, zobrazowano we fragmencie kodu widocznym na listingu 8.6. Przykładowy efekt jego wywołania został zaprezentowany na rysunku 8.33. Listing 8.6. Przykładowe wywołanie funkcji sqlite_open "; echo "Komunikat błędu: $msg"; exit; } ?>

Rysunek 8.33. Wykorzystanie dodatkowego komunikatu o błędzie

Kończenie połączenia Połączenie otwarte za pomocą funkcji sqlite_open po wykonaniu żądanych operacji w bazie powinno zostać zamknięte przez wywołanie sqlite_close. Schematyczne wywołanie tej funkcji ma postać:

Rozdział 8. ♦ Współpraca z bazami danych

403

sqlite_close(identyfikator)

gdzie identyfikator to identyfikator połączenia, które ma zostać zamknięte, zwrócony wcześniej przez funkcję sqlite_connect. Należy zwrócić uwagę, że w przeciwieństwie do mysql_close tym razem identyfikator nie jest argumentem opcjonalnym i musi zostać podany. Funkcja mysql_close zwraca wartość true, jeżeli operacja przez nią wykonywana zakończyła się sukcesem, lub false w przeciwnym razie.

Wykonywanie zapytań Do wykonywania zapytań do bazy danych są wykorzystywane funkcje sqlite_query i sqlite_unbuffered_query. Każda z nich występuje też w dwóch wersjach. Pierwsza ma postać: mysql_query(identyfikator, zapytanie[, typ_wyniku[, $msg]]) mysql_unbuffered_query(identyfikator, zapytanie[, typ_wyniku[, $msg]])

a druga: mysql_query(zapytanie, identyfikator[, typ_wyniku[, $msg]]) mysql_unbuffered_query(zapytanie, identyfikator[, typ_wyniku[, $msg]])

Jak widać, jedyną różnicą jest zmiana kolejności dwóch pierwszych argumentów. Zalecaną formą jest postać pierwsza, druga została wprowadzona w celu zachowania kompatybilności z funkcjami obsługującymi inne bazy danych, jak np. MySQL. Znaczenie poszczególnych argumentów jest następujące:  identyfikator — identyfikator połączenia z bazą danych zwrócony przez funkcję sqlite_open.  zapytanie — zapytanie SQL wysyłane do bazy.  typ_wyniku — określa, w jaki sposób będzie indeksowana tablica zawierająca wynik zapytania; dopuszczalne wartości to: SQLITE_ASSOC — indeksowanie asocjacyjne, SQLITE_NUM — indeksowanie numeryczne, SQLITE_BOTH — oba

typy indeksowania. Jeśli argument ten zostanie pominięty, zastosowana będzie wartość SQLITE_BOTH.  msg — przekazywana przez referencję zmienna, której zostanie przypisany

komunikat zawierający opis błędu, o ile taki wystąpi. Różnica między tymi funkcjami jest taka, że sqlite_query buforuje wyniki i pozwala na przemieszczanie się po zbiorze rekordów wynikowych w przód i wstecz (za pomocą dodatkowych funkcji realizujących takie zadania, jak np. sqlite_seek, sqlite_rewind itp.), a sqlite_unbuffered_query nie buforuje wyniku i pozwala jedynie na odczyt kolejnych rekordów jeden po drugim (nie można się cofnąć do rekordów poprzednich ani wykonywać jakichkolwiek innych operacji przemieszczania się po zbiorze). W związku z tym sqlite_unbuffered_query jest szybsza i angażuje mniej zasobów systemowych, natomiast sqlite_query bardziej funkcjonalna, ale wolniejsza i wymagająca więcej zasobów (co wynika z tego, że np. niezbędna jest rezerwacja pamięci na cały zbiór rekordów wynikowych). Zaleca się, aby w skryptach, których zadaniem jest jedynie prezentacja na stronie WWW danych odczytanych z bazy, stosować funkcję sqlite_ unbuffered_query.

404

PHP5. Praktyczny kurs

Oprócz sqlite_query i sqlite_unbuffered_query istnieje także funkcja sqlite_array_ query, która łączy zadanie zapytania z odbiorem danych. Zostanie omówiona w dalszej części lekcji.

Zapytania pobierające dane W przypadku zapytań typu SELECT funkcje sqlite_query i sqlite_unbuffered_query zwracają identyfikator zasobów, który może zostać następnie użyty do odczytu zwróconych danych. Istnieje kilka funkcji odczytujących takie dane, najpopularniejsze to sqlite_fetch_array i sqlite_fetch_all. Pomocna może być także funkcja sqlite_ num_rows, która pozwala na określenie, ile wierszy znajduje się w wynikach zapytania — działa ona jednak tylko wtedy, jeśli zapytanie zostało wysłane do bazy za pomocą funkcji sqlite_query (jest to zrozumiałe, bo skoro sqlite_unbuffered_query nie buforuje wyniku, to nie ma dostępu do wszystkich danych wynikowych). Wywołanie sqlite_fetch_array ma postać: sqlite_fetch_array(identyfikator[, typ_wyniku])

Zwraca ono kolejny wiersz z tabeli będącej wynikiem zapytania. Argument identyfikator to identyfikator połączenia z bazą zwrócony przez wywołanie sqlite_query lub sqlite_unbuffered_query, a typ_wyniku ma takie samo znaczenie jak opisane na wcześniejszych stronach. Jeżeli typ_wyniku nie zostanie podany, będzie zastosowana wartość domyślna SQLITE_BOTH. Dane zwracane są w tablicy, w której kolejne komórki zawierają dane z kolejnych kolumn tabeli wynikowej. Jeśli zostaną odczytane wszystkie wiersze, funkcja zwróci wartość false. Oznacza to, że wszystkie wyniki zapytania mogą zostać odczytane w pętli while o schematycznej postaci: while($arr = sqlite_fetch_row($result)){ //instrukcje przetwarzające wyniki }

gdzie $arr to tablica, do której będą zapisywane dane z kolejnych wierszy, a $result to zmienna zawierająca identyfikator zasobów zwrócony przez funkcję sqlite_query. Drugi wariant to użycie funkcji sqlite_num_rows do pobrania liczby wierszy z tabeli wynikowej i pętli typu for. Taka konstrukcja miałaby schematyczną postać: $count = sqlite_num_rows($result); for($i = 0; $i < $count; $++){ //instrukcje przetwarzające wyniki }

Zobaczmy zatem, jak można w praktyce odczytać dane z wybranej tabeli, wykorzystując do tego funkcję sqlite_fetch_array. Niech będzie to wykorzystywana w lekcji 23. tabela osoba. Odpowiedni kod został zaprezentowany na listingu 8.78. 8

Dla wygody Czytelników plik testphp.db zawierający bazę danych z tabelą Osoba został zamieszczony na ftp://ftp.helion.pl/przyklady/ph5pk_2.zip (w katalogu zawierającym treść listingu 8.7). Oczywiście nic nie stoi na przeszkodzie, aby korzystając z klienta sqlite i kodów przedstawionych w lekcji 23., wykonać taką bazę samodzielnie.

Rozdział 8. ♦ Współpraca z bazami danych

405

Listing 8.7. Odczytanie zawartości tabeli osoba Moja strona WWW '; echo "Komunikat błędu: $msg"; echo ""; exit; } $query = 'SELECT * FROM Osoba'; if(!$result = sqlite_unbuffered_query($db_lnk, $query)){ sqlite_close(); echo 'Wystąpił błąd: nieprawidłowe zapytanie...
'; echo ""; exit; } ?> "; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; } sqlite_close($db_lnk); ?>
Id Imię Nazwisko Rok urodzenia Miejsce urodzenia
$row[0]$row[1]$row[2]$row[3]$row[4]


Połączenie z bazą jest nawiązywane w sposób omówiony na wcześniejszych stronach. Następnie zmiennej $query jest przypisywany ciąg znaków tworzący treść zapytania SQL: SELECT * FROM osoba

406

PHP5. Praktyczny kurs

które pobiera wszystkie wiersze z tabeli osoba. Zapytanie jest wysyłane do serwera przez wywołanie funkcji sqlite_unbuffered_query, a jego wyniki są pobierane w pętli while. Jest w niej wywoływana funkcja sqlite_fetch_array, która pobiera kolejne wiersze tabeli wynikowej i zwraca je w postaci tablicy. Pod indeksem 0 tej tablicy znajduje się wartość z kolumny Id, pod 1 — z kolumny Imie, pod 2 — z kolumny Nazwisko itd. Innymi słowy, kolejność kolumn w tablicy $row będzie taka sama jak zwrócona przez wydane zapytanie. Odczytane wyniki są umieszczane w tabeli HTML w celu ich sformatowania. Po uruchomieniu skryptu powinniśmy zobaczyć w przeglądarce widok taki, jak zaprezentowany na rysunku 8.31 przy omawianiu funkcji operujących na bazie MySQL. W wywołaniu sqlite_fetch_array nie został zastosowany drugi argument, co oznacza, że funkcja zachowała się tak, jakby miał on wartość SQLITE_BOTH. Oznacza to, że tablica wynikowa jest indeksowana zarówno numerycznie, jak i asocjacyjnie, a zatem prawidłowe byłyby również odwołania do poszczególnych wierszy w postaci: $row['nazwa_kolumny']

Pętla while mogłaby więc mieć również postać: while($row = sqlite_fetch_array($result)){ echo ""; echo "{$row['Id']}"; echo "{$row['Imie']}"; echo "{$row['Nazwisko']}"; echo "{$row['Rok_Urodzenia']}"; echo "{$row['Miejsce_Urodzenia']}"; echo ""; }

Zapytania modyfikujące dane W przypadku zapytań modyfikujących dane w bazie zamiast sqlite_query należy użyć funkcji sqlite_exec, która zwraca wartość true, jeśli serwer przyjął zapytanie, lub false, jeśli zostało ono odrzucone (np. była to niepoprawna instrukcja SQL). Wywołanie to, podobnie jak w przypadku sqlite_query i sqlite_unbuffered_query, może mieć dwie formy: sqlite_exec(identyfikator, zapytanie[, $msg]) sqlite_exec(zapytanie, identyfikator)

Zalecane jest stosowanie formy pierwszej. Znaczenie poszczególnych argumentów jest następujące:  identyfikator — identyfikator połączenia z bazą danych zwrócony przez funkcję sqlite_open;  zapytanie — zapytanie SQL wysyłane do bazy;  msg — przekazywana przez referencję zmienna, której zostanie przypisany

komunikat zawierający opis błędu, o ile taki wystąpi.

Rozdział 8. ♦ Współpraca z bazami danych

407

Liczbę wierszy w bazie, na które zapytanie miało wpływ, można odczytać przez wywołanie funkcji sqlite_changes. Wywołanie sqlite_changes ma postać: sqlite_changes(identyfikator)

gdzie identyfikator jest identyfikatorem połączenia z bazą zwróconym przez funkcję sqlite_open. Zobaczmy w praktyce, jak można dodać wiersz do znanej z poprzedniej lekcji tabeli osoba. Takie zadanie wykonuje skrypt widoczny na listingu 8.8. Listing 8.8. Skrypt dodający pojedynczy wiersz do tabeli osoba '; echo "Komunikat błędu: $msg"; exit; } $query = "INSERT INTO osoba VALUES("; $query .= "11, 'Maciej', 'Nowak', 1982, 'Lublin'"; $query .= ")"; if(!sqlite_exec($db_lnk, $query, $msg)){ mysql_close(); echo 'Błąd: zapytanie zostało odrzucone...
'; echo "Komunikat błędu: $msg"; exit; } $rowsNo = sqlite_changes($db_lnk); echo "Liczba dodanych rekordów: $rowsNo
"; sqlite_close($db_lnk); ?>

Połączenie z bazą zapisaną w pliku testphp.db (umieszczonym w katalogu /var/www data/; oczywiście lokalizację można zmienić, modyfikując odpowiednio odwołanie w skrypcie) jest nawiązywane za pomocą funkcji sqlite_open. Ten fragment kodu ma postać analogiczną do tej z wcześniejszych przykładów. Następnie zmiennej $query jest przypisywana treść zapytania SQL w postaci: INSERT INTO osoba VALUES(11, 'Maciej', 'Nowak', 1982, 'Lublin')

które dodaje do tabeli osoba nowy wpis. Zapytanie to jest wysyłane do bazy za pomocą funkcji sqlite_exec. Dalej za pomocą instrukcji if jest sprawdzane, czy wywołanie funkcji zwróciło wartość true czy false. Jeśli jest to wartość false, czyli jeśli zapytanie zostało odrzucone, do przeglądarki jest wysyłany stosowny komunikat, a połączenie z bazą jest zamykane. Jeśli jednak zwróconą wartością jest true, wykonywana jest dalsza część skryptu.

408

PHP5. Praktyczny kurs

Zmiennej $rowsNo jest przypisywana wartość zwrócona przez wywołanie funkcji sqlite_changes, która określa, na ile wierszy w bazie miało wpływ ostatnio wykonane zapytanie. Wartość ta jest następnie wysyłana do przeglądarki, tak aby można było stwierdzić, czy na pewno został dodany jeden wiersz. Na zakończenie połączenie z bazą jest zamykane za pomocą funkcji sqlite_close.

Funkcja sqlite_array_query Wcześniej została już wspomniana funkcja sqlite_array_query, która jednocześnie wysyła zapytanie do bazy oraz odbiera wyniki w postaci tablicy zawierającej odczytane rekordy. Zobaczmy zatem, jak z niej skorzystać. Do dyspozycji są dwie postacie różniące się od siebie kolejnością argumentów: sqlite_array_query(identyfikator, zapytanie[, typ_wyniku]) sqlite_array_query (zapytanie, identyfikator[, typ_wyniku])

Znaczenie argumentów jest takie samo jak w przypadku funkcji sqlite_query, nie będą więc ponownie omawiane. Funkcja sqlite_array_query zachowuje się tak, jakby najpierw została wywołana sqlite_query, następnie dla każdego wiersza wynikowego funkcja sqlite_fetch_row, a wyniki zostały umieszczone we wspólnej tablicy. Pod jej indeksem 0 będzie się znajdowała tablica zawierająca pierwszy wiersz zapytania wynikowego, pod indeksem 1 — zawierająca drugi wiersz itd. A zatem wynikiem działania sqlite_array_query jest tablica zawierająca tablice, z których każda zawiera jeden wiersz będący wynikiem zadanego zapytania. Sposób tworzenia tej tablicy można schematycznie przedstawić jako: $query = "Treść zapytania"; $tablica_wynikowa = array(); if($result = sqlite_query($query)){ while($row = sqlite_fetch_row($result)){ $tablica_wynikowa[] = $row; } }

Należy przy tym wspomnieć, że o ile wywołanie sqlite_array_query z reguły jest szybsze niż osobne wywoływanie funkcji sqlite_query i sqlite_fetch_row, to jednak nie powinno się go stosować do wykonywania zapytań zwracających większą liczbę wyników (dokumentacja PHP nie zaleca jej użycia, jeśli wierszy jest więcej niż 45). Przy dużej liczbie wierszy wynikowych należy korzystać (tak jak zostało to opisane na wcześniejszych stronach) z funkcji sqlite_unbuffered_query. Jak w praktyce należy wykorzystać funkcję sqlite_array_query do odczytania zawartości tabeli osoba, zobrazowano w kodzie widocznym na listingu 8.9. Listing 8.9. Wykorzystanie funkcji sqlite_array_query Moja strona WWW

Rozdział 8. ♦ Współpraca z bazami danych

409

'; echo 'Komunikat błędu: '.$msg; echo ''; exit; } $query = 'SELECT * FROM osoba'; if(!$arr = sqlite_array_query($db_lnk, $query)){ sqlite_close(); echo 'Błąd: nieprawidłowe zapytanie...
'; echo ''; exit; } ?> "; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; } sqlite_close($db_lnk); ?>
Id Imię Nazwisko Rok urodzenia Miejsce urodzenia
$row[0]$row[1]$row[2]$row[3]$row[4]


Ćwiczenia do samodzielnego wykonania Ćwiczenie 24.1. Napisz taki skrypt wyświetlający zawartość tabeli osoba z bazy MySQL, aby użytkownik miał możliwość wyboru kolumny, względem której będzie wykonywane sortowanie danych. Ćwiczenie 24.2. Napisz taki skrypt wyświetlający zawartość tabeli osoba z bazy SQLite, aby użytkownik miał możliwość wyboru kolumny, względem której będzie wykonywane sortowanie danych.

410

PHP5. Praktyczny kurs

Ćwiczenie 24.3. Napisz skrypt wyświetlający wszystkie dane z wybranej tabeli bazy MySQL (np. tabeli osoba), który automatycznie będzie się dopasowywał do zmiany jej struktury (np. nie będzie konieczności modyfikacji kodu skryptu po dodaniu do tabeli nowej kolumny). Ćwiczenie 24.4. Napisz skrypt wyświetlający wszystkie dane z wybranej tabeli bazy SQLite (np. tabeli Osoba), który automatycznie będzie się dopasowywał do zmiany jej struktury (np. nie będzie konieczności modyfikacji kodu skryptu po dodaniu do tabeli nowej kolumny). Ćwiczenie 24.5. Zmodyfikuj kod z listingu 8.9 w taki sposób, aby dane będące wynikiem zapytania były wyświetlane automatycznie niezależnie od struktury tabeli osoba. Ćwiczenie 24.6. Napisz skrypt umożliwiający dodawanie rekordów z poziomu WWW do znajdującej się w bazie MySQL tabeli osoba. Ćwiczenie 24.7. Napisz skrypt umożliwiający dodawanie rekordów z poziomu WWW do znajdującej się w bazie SQLite tabeli osoba. Ćwiczenie 24.8. Zmodyfikuj kod ćwiczeń 24.6 i 24.7 oraz tabeli osoba w taki sposób, aby w przypadku gdy użytkownik nie poda identyfikatora wprowadzanego rekordu, był on generowany automatycznie.

Lekcja 25. Podejście obiektowe Korzystanie z PDO PDO, z ang. PHP Data Objects, to rozszerzenie pozwalające na ujednoliconą obsługę różnych baz danych w technice obiektowej. Jest częścią PHP, począwszy od wersji 5.1 (dla wersji wcześniejszych dostępne jako instalowane osobno rozszerzenie PECL). Jego zaletą jest ujednolicona obsługa połączeń z bazami, niezależnie od tego, czy będzie to SQLite, MySQL, PostgreSQL, czy dowolna inna. Jedynym wymogiem jest istnienie odpowiedniego interfejsu pośredniczącego między PDO a daną bazą (takie interfejsy są dostępne dla wielu popularnych systemów baz danych). W przypadku systemów uniksowych obsługa PDO jest włączona domyślnie. Konieczne może być natomiast włączenie obsługi odpowiedniego interfejsu (drivera) bazy danych. Należy to sprawdzić w dokumentacji danego systemu i dokumentacji PHP. Przykładowo aby w Linuksie Ubuntu możliwa była obsługa MySQL przez PDO, może być niezbędne zainstalowanie pakietu php5-mysql (i ewentualnie php5-pdo), np. za pomocą komendy (dla instalatora aptitude): sudo aptitude install php5-mysql

Po jej wykonaniu należy zrestartować serwer WWW (Apache). W dystrybucji Fedora niezbędne może być zainstalowanie zarówno pakietu php-pdo, jak i php-mysql, co można osiągnąć, wydając na przykład polecenie (dla instalatora yum):

Rozdział 8. ♦ Współpraca z bazami danych

411

yum install php-pdo php-mysql

W przypadku systemów z rodziny Windows należy w pliku php.ini odszukać wiersz w postaci: extension=php_pdo_nazwabazy.dll

np. dla bazy MySQL: extension=php_pdo_mysql.dll

i usunąć z niego komentarz (znak średnika) lub (jeśli takiego wiersza nie ma) dodać go. Jeżeli dysponujemy wersją PHP niższą niż 5.3, przed tym wierszem powinna dodatkowo znaleźć się linia: extension=php_pdo.dll

Po dokonaniu zmian trzeba zrestartować serwer WWW.

Nawiązywanie połączenia Nawiązywanie połączenia z bazą wygląda tu zupełnie inaczej niż w przykładach z lekcji 24. Należy w tym celu utworzyć nowy obiekt klasy PDO, korzystając z konstruktora w postaci: PDO(źródło_danych[, użytkownik[, hasło[, opcje]]]);

gdzie źródło_danych (DSN, z ang. Data Source Name) to ciąg znaków określający rodzaj bazy danych i sposób połączenia, użytkownik to nazwa użytkownika, hasło to hasło, a opcje to tablica zawierająca dodatkowe opcje związane z połączeniem (nie będziemy się nim bliżej zajmować). Jedynym wymaganym argumentem jest źródło_danych, pozostałe są opcjonalne. Uproszczona postać ciągu źródło_danych dla bazy MySQL, z której będziemy korzystać, ma postać: mysql:host=nazwa_serwera;port=numer_portu;dbname=nazwa_bazy

Poszczególne składowe mają następujące znaczenie:  nazwa_serwera — określenie nazwy lub adresu serwera baz danych (np. localhost, 127.0.0.1).  numer_portu — port, na którym ma nastąpić połączenie z bazą. Może być

pominięty, zostanie wtedy zastosowana wartość standardowa.  nazwa_bazy — nazwa bazy danych, z którą ma nastąpić połączenie.

Tak więc ciąg źródło_danych, który pozwala na nawiązanie połączenia z bazą testphp znajdującą się na serwerze MySQL pracującym na komputerze lokalnym localhost na porcie 3306, będzie miał postać: mysql:host=localhost;port=3306;dbname=testphp

lub, jeśli określenie portu zostanie pominięte: mysql:host=localhost;dbname=testphp

412

PHP5. Praktyczny kurs

W przypadku bazy SQLite należy użyć ciągu sqlite: uzupełnionego o ścieżkę dostępu do pliku z bazą. Zakładając, że jest to znana z lekcji 24. baza testphp.db znajdująca się w katalogu /var/wwwdata/, użyjemy ciągu: sqlite:/var/wwwdata/testphp.db

W ten sposób obsługiwana jest baza SQLite w wersji 3. Jeśli istnieje potrzeba użycia starszej wersji 2., należy zamiast sqlite: użyć sqlite2:, np.: sqlite2:/var/wwwdata/testphp.db

Jeżeli wywołanie konstruktora zakończy się sukcesem (uda się nawiązać połączenie z daną bazą), zostanie zwrócony obiekt, który będzie służył do dalszej komunikacji, należy więc przypisać go jakiejś zmiennej. W przypadku gdy połączenia nie uda się nawiązać, zostanie wygenerowany wyjątek (por. lekcja 19 z rozdziału 5.) typu PDOException, który będzie zawierał opis przyczyny powstania błędu. A zatem fragment kodu ustalający, czy udało się nawiązać połączenie z bazą danych, mógłby mieć postać (zakładając nawiązywanie połączenia z bazą testphp na serwerze MySQL, podobnie jak w lekcji 24.) przedstawioną na listingu 8.10. Listing 8.10. Nawiązywanie połączenia z bazą za pomocą PDO getMessage(); exit; } //dalsze instrukcje skryptu ?>

Kończenie połączenia Połączenie nawiązane za pomocą obiektu PDO pozostaje aktywne przez cały czas życia obiektu i jest kończone przy usuwaniu obiektu z pamięci. Zostanie to wykonane automatycznie po zakończeniu pracy skryptu lub po przypisaniu wartości null zmiennej obiektowej przechowującej odwołanie do obiektu. Jeżeli zatem mamy do czynienia z obiektem $dbo, jak w skrypcie z listingu 8.10, należy użyć przypisania: $dbo = null;

Zapytania pobierające dane Zapytania są wykonywane za pomocą metody query, której należy przekazać treść zapytania w postaci argumentu. Zakładając zatem, że mamy do dyspozycji obiekt $dbo

Rozdział 8. ♦ Współpraca z bazami danych

413

powstały przez wywołanie konstruktora klasy PDO, zapytanie SQL można wykonać przez zastosowanie konstrukcji: $dbo->query("Treść zapytania");

Oczywiście treść zapytania może być również przekazywana w postaci zmiennej, np.: $query = "Treść zapytania"; $dbo->query($query);

Metoda query zwraca obiekt typu PDOStatement pozwalający na odczyt danych po wykonaniu zapytania, gdy zakończyło się sukcesem, lub też wartość false w przeciwnym razie. Wspomniany obiekt zawiera metodę fetch, która udostępnia pobrane dane. Jej ogólne wywołanie ma postać9: fetch([typ_wyniku])

Zwracaną wartością jest kolejny wiersz z wyników zapytania lub wartość false, jeżeli kolejnego wiersza nie uda się pobrać (np. zostały już odczytane wszystkie dane). Postać zwróconych danych zależy od stanu argumentu typ_wyniku, który może przyjmować następujące wartości:  PDO::FETCH_ASSOC — zwraca tablicę asocjacyjną, w której nazwy kolumn

wynikowych są kluczami,  PDO::FETCH_BOTH — zwraca tablicę indeksowaną zarówno numerycznie,

jak i asocjacyjnie (jest to wartość domyślna),  PDO::FETCH_BOUND — zwraca wartość true oraz przypisuje wartość z kolumn

wyniku do zmiennych PHP ustalonych wcześniej za pomocą wywołania metody bindColumn(),  PDO::FETCH_CLASS — zwraca nową instancję klasy, dokonując mapowania

kolumn wynikowych na właściwości klasy,  PDO::FETCH_INTO — uaktualnia istniejącą instancję klasy, dokonując

mapowania kolumn wynikowych na właściwości klasy,  PDO::FETCH_LAZY — kombinacja PDO::FETCH_BOTH i PDO::FETCH_OBJ,  PDO::FETCH_NUM — zwraca tablicę indeksowaną numerycznie,  PDO::FETCH_OBJ — zwraca obiekt z właściwościami o nazwach i wartościach

odpowiadającym kolumnom wynikowym zapytania. Aby zmienić domyślny tryb obowiązujący dla wszystkich zapytań (czyli standardowe PDO::FETCH_BOTH), należy wykorzystać metodę setFetchMode, której wywołanie ma postać: setFetchMode([domyślny_typ_wyniku])

9

Dostępne są również dwa dodatkowe argumenty związane z kursorami, które jednak nie będą rozważane.

414

PHP5. Praktyczny kurs

gdzie domyślny_typ_wyniku to jedna z wartości wymienionych wyżej. Jeśli chcemy na przykład (zakładając, że zmienna $result zawiera obiekt PDOStatement), aby domyślnym typem wyniku była tablica indeksowana numerycznie, zastosujemy wywołanie: $result->setFetchMode(PDO::FETCH_NUM);

po którym wszystkie wywołania metody fetch będą zwracały właśnie tablice numeryczne. Ponieważ każde wywołanie fetch powoduje zwrócenie kolejnego wiersza wyniku lub wartości false, jeśli zostały odczytane wszystkie wiersze, metodę tę można wywoływać w pętli while o schematycznej postaci: while($row = $result->fetchRow()){ //instrukcje przetwarzające wiersz tabeli }

Czas zatem zebrać przedstawione dotychczas w tej lekcji informacje i napisać skrypt odczytujący za pomocą PDO dane z wybranej tabeli bazy danych. Użyjemy wykorzystywanej już wielokrotnie tabeli osoba, choć oczywiście nic nie stoi na przeszkodzie do zastosowania innej, stworzonej samodzielnie. Kod realizujący to zadanie został przedstawiony na listingu 8.11. Listing 8.11. Odczyt danych z tabeli osoba Moja strona WWW
getMessage(); echo '
'; exit; } $query = "SELECT * FROM osoba"; $result = $dbo->query($query); if(!$result){ echo "Nie mogę wykonać zapytania.
"; echo '
'; exit; } ?>

Rozdział 8. ♦ Współpraca z bazami danych

415

fetch(PDO::FETCH_NUM)){ echo "\n"; echo "\n"; echo "\n"; echo "\n"; echo "\n"; echo "\n"; echo "\n"; } $dbo = null; ?>
IdImięNazwisko Rok urodzeniaMiejsce urodzenia
{$row[0]}{$row[1]}{$row[2]}{$row[3]}{$row[4]}


Połączenie z bazą jest nawiązywane przy tworzeniu obiektu typu PDO dokładnie w taki sam sposób, jaki został opisany na wcześniejszych stronach. Dane dotyczące połączenia są zapisywane w trzech zmiennych pomocniczych: $dsn, $uzytkownik, $haslo. Użyte zostały wartości wskazujące bazę testphp pracującą na serwerze MySQL. Oczywiście można je zmodyfikować, tak aby użyta została baza SQLite (czy też inna). Gdyby połączenia nie udało się nawiązać, czyli nie powstałby obiekt $dbo, wygenerowany będzie wyjątek PDOException, który jest przechwytywany za pomocą instrukcji try…catch. Wtedy w bloku catch za pomocą instrukcji echo będą wysyłane informacje o przyczynie błędu i skrypt skończy działanie. Jeżeli jednak wyjątek nie nastąpił, czyli połączenie udało się nawiązać, do bazy za pomocą metody query jest wysyłane zapytanie SQL (zapytanie to znajduje się w pomocniczej zmiennej $query) w postaci: SELECT * FROM osoba

a wynik działania metody jest przypisywany zmiennej $result. W przypadku gdy wynikiem tym jest false (jest to badane za pomocą instrukcji warunkowej if(!$result){), pojawia się komunikat o błędzie i skrypt kończy działanie. W przeciwnym wypadku (gdy zmienna $result jest różna od false, a więc zawiera obiekt typu PDOStatement) jest wykonywana dalsza część skryptu. Dane z tabeli wynikowej są pobierane w pętli while przez metodę fetch, a każdy wiersz jest zwracany w postaci tablicy indeksowanej numerycznie (jest to osiągane przez użycie argumentu PDO::FETCH_NUM). Tak więc w każdym przebiegu pętli zmienna $row zawiera dane z pojedynczego wiersza tabeli wynikowej i są one umieszczane w kolejnych komórkach tablicy HTML. Na ekranie zobaczymy więc taki sam widok jak zaprezentowany na rysunku 8.32 w lekcji 24. Na końcu skryptu połączenie z bazą jest zamykane przez przypisanie zmiennej $dbo wartości null (warto stosować takie rozwiązanie, choć nie jest to formalnie konieczne, ponieważ połączenie zostałoby i tak zamknięte po zakończeniu pracy skryptu).

416

PHP5. Praktyczny kurs

Warto w tym miejscu pokazać, jak wyglądałby taki skrypt, gdyby wyniki zapytania były pobierane jako obiekty — taka technika jest dosyć często spotykana. W takiej sytuacji wartością zwracaną przez metodę fetch jest obiekt, którego właściwości odpowiadają kolumnom wynikowym zapytania SQL. To znaczy nazwami właściwości są nazwy kolumn, a ich wartościami — wartości zapisane w tych kolumnach. A zatem pętla while generująca kolejne komórki tabeli HTML o zawartości pobieranej z wyników zapytania miałaby w tej technice następującą postać: while($row = $result->fetch(PDO::FETCH_OBJ)){ echo "\n"; echo "{$row->Id}\n"; echo "{$row->Imie}\n"; echo "{$row->Nazwisko}\n"; echo "{$row->Rok_urodzenia}\n"; echo "{$row->Miejsce_urodzenia}\n"; echo "\n"; }

Pobranie pełnych wyników zapytania Jeśli chcemy pobrać cały wynik zapytania SQL w postaci tablicy zawierającej wszystkie wiersze wynikowe, możemy użyć metody fetchAll. Jej wywołanie ma taką samą postać jak omówionej wyżej metody fetch, czyli: fetchAll([typ_wyniku])

Znaczenie argumentu typ_wyniku jest takie samo jak omówione w poprzednim punkcie lekcji. A zatem przykładowe wywołanie (zakładając, że w zmiennej $result znajduje się obiekt typu PDOStatement): $arr = $result->fetchAll(PDO::FETCH_NUM);

spowoduje przypisanie zmiennej $arr tablicy, której kolejnymi elementami będą indeksowane numerycznie tablice zawierające wyniki zapytania. Taka tablica może być odczytana np. w pętli foreach. Sposób użycia metody fetchAll do wyświetlania zawartości tabeli osoba został przedstawiony na listingu 8.12. Listing 8.12. Wykorzystanie metody fetchAll fetchAll(PDO::FETCH_NUM); foreach($arr as $row){ echo "\n"; echo "\n"; echo "\n"; echo "\n"; echo "\n";

Rozdział 8. ♦ Współpraca z bazami danych

417

echo "\n"; echo "\n"; } $dbo = null; ?>

Metody fetchAll nie należy używać do zapytań zwracających dużą liczbę rekordów (wierszy wynikowych), gdyż spowoduje to duże obciążenie zarówno systemu serwera, jak i łącza sieciowego.

Ponieważ zarówno treść HTML, jak i część skryptu związana z nawiązaniem połączenia i wykonaniem zapytania SQL są takie same jak w poprzednim przykładzie (listing 8.11), nie zostały one ponownie zaprezentowane. Zmiany ograniczają się do sposobu pobierania danych z obiektu PDOStatement reprezentowanego przez zmienną $result. Cały wynik zapytania jest pobierany przez wywołanie metody fetchAll, której przekazany został argument PDO::FETCH_NUM. A zatem będzie to tablica $arr, w której w komórce o indeksie 0 został zapisany pierwszy wiersz wyników zapytania, w komórce o indeksie 1 — drugi wiersz itd. Każdy z tych wierszy (ze względu na użycie parametru PDO::FETCH_NUM) jest tablicą indeksowaną numerycznie, która w komórce o indeksie 0 zawiera wartość pierwszej kolumny wyników zapytania (jest to kolumna Id), w komórce o indeksie 1 — wartość drugiej kolumny wyników zapytania (jest to kolumna Imie) itd. Tablica $arr jest przetwarzana w pętli foreach (foreach($arr as $row){), a zatem w każdym przebiegu tej pętli pod zmienną $row jest podstawiana kolejna tablica odzwierciedlająca kolejny wiersz wyników zapytania. Skoro tak, to znaczy, że $row[0] zawiera wartość kolumny Id, $row[1] — wartość kolumny Imie itd. Gdyby zamiast parametru PDO::FETCH_NUM został użyty PDO::FETCH_ASSOC, tablica $arr nadal byłaby indeksowana numerycznie, ale zawarte w niej tablice z wierszami wyników zapytania byłyby indeksowane asocjacyjnie (ich indeksami byłyby nazwy kolumn). Wtedy pętla odczytująca dane wyglądałaby tak jak na listingu 8.13. Listing 8.13. Metoda fetchAll i indeksowanie asocjacyjne $arr = $result->fetchAll(PDO::FETCH_ASSOC); foreach($arr as $row){ echo "\n"; echo "\n"; echo "\n"; echo "\n"; echo "\n"; echo "\n"; echo "\n"; }

418

PHP5. Praktyczny kurs

Zapytania modyfikujące dane Do wykonywania zapytań modyfikujących używa się metody execute. Wysyła ona zapytanie do serwera oraz zwraca wartość całkowitą określającą liczbę rekordów, na które to zapytanie miało wpływ (czyli np. liczbę zmodyfikowanych, usuniętych lub dodanych wierszy tabeli). Jeżeli wykonanie zapytania nie powiodło się (np. miało złą składnię), metoda zwraca wartość false. Zapytanie należy przekazać w postaci argumentu, zatem schematyczne wywołanie (zakładając, że zmienna $dbo zawiera obiekt typu PDO) wygląda następująco: $dbo->execute("Treść zapytania");

Zobaczmy więc, jak w praktyce dodać wiersz do tabeli osoba. Takie zadanie wykonuje skrypt widoczny na listingu 8.14. Listing 8.14. Dodanie nowego rekordu do bazy getMessage(); exit; } $query = "INSERT INTO osoba VALUES("; $query .= "0, 'Maciej', 'Nowak', 1982, 'Lublin'"; $query .= ")"; $result = $dbo->exec($query); if($result === false){ echo 'Nie mogę wykonać zapytania.'; } else{ echo 'Liczba dodanych rekordów: '.$result; } $dbo = null; ?>

PHP i SQLite W lekcji 24. omówiony został m.in. dostępny w PHP moduł obsługi bazy SQLite. Opisywane były jednak wtedy tylko funkcje operujące na bazie w standardowy, proceduralny sposób. To jednak nie wszystkie możliwości, możliwa jest również praca obiektowa, do której nie jest wymagany omówiony wyżej moduł PDO. Zobaczmy zatem, w jaki sposób można obiektowo obsługiwać bazę SQLite przy wykorzystaniu standardowego modułu SQLite.

Rozdział 8. ♦ Współpraca z bazami danych

419

Nawiązywanie połączenia z bazą Aby nawiązać połączenie z bazą, należy utworzyć nowy obiekt klasy SQLiteDatabase. Konstruktor tej klasy może przyjmować do trzech argumentów, a jego wywołanie ma postać: new SQLiteDatabase("plik"[, "tryb"[, &$msg]]);

Parametr plik określa nazwę pliku, w którym znajduje się baza danych lub w którym ma ona zostać utworzona, tryb w założeniach określa atrybuty pliku, jednak obecnie jest on ignorowany i zaleca się wykorzystywanie wartości 0666. Natomiast $msg to przekazana przez referencję zmienna, w której zostanie zapisany opis błędu, o ile taki wystąpi. Oczywiście nowy obiekt należy przypisać jakiejś zmiennej, tak aby można było się do niego odwoływać. Jeśli zatem chcemy nawiązać połączenie z bazą zawartą w pliku /var/wwwdata/testphp.db, możemy zastosować wywołanie: $db = new SQLiteDatabase("/var/wwwdata/testphp.db");

lub też: $db = new SQLiteDatabase("/var/wwwdata/testphp.db", 0666, $msg);

W tym drugim przypadku, jeśli wystąpi błąd (np. na dysku nie ma katalogu /var/www data/), będzie można uzyskać bardziej szczegółową informację o jego przyczynie za pomocą zmiennej $msg. Wykonuje się tu podobne czynności jak przy wykorzystywaniu funkcji sqlite_open, w tym jednak przypadku komunikat o błędzie można uzyskać również w inny sposób. Otóż trzeba przecież jakoś stwierdzić, czy połączenie udało się otworzyć, a zatem czy obiekt klasy SQLiteDatabase faktycznie został utworzony. Wykorzystuje się w tym celu technikę wyjątków. W przypadku niemożności utworzenia obiektu konstruktor klasy SQLiteDatabase generuje bowiem wyjątek SQLiteException, a jak wiadomo z lekcji 19., obiekt wyjątku udostępnia metodę getMessage, która zwraca komunikat informujący o przyczynie zgłoszenia wyjątku. Na listingu 8.15 został zaprezentowany przykład pokazujący, jak wykorzystać obiekt klasy SQLiteException do nawiązania połączenia z bazą zawartą w pliku testphp.db umieszczonym w katalogu /var/wwwdata/. Na rysunku 8.34 jest natomiast widoczny efekt działania tego kodu w sytuacji, gdy połączenie nie może być otwarte ze względu na niepoprawne podanie nazwy katalogu. Listing 8.15. Przykładowe wywołanie funkcji sqlite_open "; echo "Komunikat błędu \$e: "; echo $e->getMessage(), "
"; echo "Komunikat błędu \$msg: ";

420

PHP5. Praktyczny kurs echo $msg, "
"; exit; } //dalsze instrukcje skryptu ?>

Rysunek 8.34. Nieudana próba utworzenia obiektu SQLiteDatabase

Kończenie połączenia O ile połączenie otwarte za pomocą sqlite_open należało zamknąć, używając funkcji sqlite_close, to w przypadku podejścia obiektowego wystarczy usunąć zmienną obiektową. Jeśli zatem obiekt został utworzony za pomocą instrukcji: $db = new SQLiteDatabase("/var/wwwdata/testphp.db");

po zakończeniu pracy należy skorzystać z funkcji unset w postaci: unset($db);

lub też przypisać zmiennej $db wartość null: $db = null;

Połączenie zostanie również zamknięte automatycznie po zakończeniu pracy skryptu.

Zapytania pobierające dane Do wykonywania zapytań służących pobieraniu danych są wykorzystywane metody query i unbuffered_query. Ich wywołania mają postać: query(zapytanie[, typ_wyniku[, $msg]]) unbuffered_query(zapytanie[, typ_wyniku[, $msg]])

gdzie:  zapytanie — to zapytanie SQL wysyłane do bazy.  typ_wyniku — określa, w jaki sposób będą indeksowane wyniki zapytania; dopuszczalne wartości to: SQLITE_ASSOC — indeksowanie asocjacyjne, SQLITE_NUM — indeksowanie numeryczne, SQLITE_BOTH — oba typy

indeksowania. Jeśli argument ten zostanie pominięty, zastosowana będzie wartość SQLITE_BOTH.

Rozdział 8. ♦ Współpraca z bazami danych

421

 msg — to przekazywana przez referencję zmienna, której zostanie przypisany

komunikat zawierający opis błędu, o ile taki wystąpi. Jak widać, metody te są podobne do funkcji sqlite_query i sqlite_unbuffered_query omawianych w lekcji 24. Podobne jest również ich znaczenie. Metoda query buforuje wyniki i należy ją stosować raczej dla zapytań generujących niewielką liczbę wierszy wynikowych. Metoda unbuffered_query natomiast nie buforuje wyników i należy ją stosować w przypadku dużej ilości danych wynikowych. Inne są jednak typy zwracanych wartości — query zwraca obiekt typu SQLiteResult, a sqlite_unbuffered obiekt typu SQLiteUnbuffered. W przypadku gdy wykonanie zapytania się nie uda, obie metody zwracają wartość false. Odczyt kolejnych wierszy zapytania umożliwia metoda fetch dostępna zarówno w klasie SQLiteResult, jak i SQLiteUnbuffered. Każde wywołanie fetch zwraca kolejny wiersz z tabeli będącej wynikiem zapytania. Dane zwracane są w tablicy, w której kolejne komórki zawierają dane z kolejnych kolumn tabeli wynikowej. Jeśli zostaną odczytane wszystkie wiersze, metoda zwraca wartość false. Wywołanie fetch ma postać: $result->fetch([typ_wyniku]);

gdzie typ_wyniku oznacza sposób indeksowania tabeli wynikowej, jest to jednak argument opcjonalny — domyślnie ma on wartość SQLITE_BOTH. Oznacza to, że wszystkie wyniki zapytania mogą zostać odczytane w pętli while o schematycznej postaci: while($row = $result->fetch()){ //instrukcje przetwarzające wyniki }

gdzie $row to tablica, do której będą zapisywane dane z kolejnych wierszy, a $result to obiekt zwrócony przez query lub unbuffered_query. Odczyt może zostać również przeprowadzony za pomocą pętli foreach w postaci: foreach($result as $row){ //instrukcje przetwarzające wyniki }

gdzie $result i $row mają takie samo znaczenie jak przy poprzedniej konstrukcji. Na listingu 8.16 został przedstawiony skrypt, który wykorzystuje opisane wyżej elementy języka do pobrania zawartości tabeli osoba zawartej w pliku /var/wwwdata/testphp.db. Listing 8.16. Obiektowy odczyt zawartości tabeli "; echo "Komunikat błędu \$e: "; echo $e->getMessage(), "
"; echo ""; exit; }

422

PHP5. Praktyczny kurs $query = 'SELECT * FROM osoba'; if($result = $db->query($query, SQLITE_NUM, $msg)){ foreach($result as $row){ echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; } } else{ echo 'Wystąpił błąd: nieprawidłowe zapytanie...
'; echo "Komunikat błędu: $msg
"; } unset($db); ?>

Zapytania modyfikujące dane W przypadku zapytań modyfikujących dane w bazie zamiast query lub unbuffered_ query należy użyć metody queryExec z klasy SQLiteDatabase. Zwraca ona wartość true, jeśli zapytanie zostało wykonane, lub false, jeśli zostało odrzucone. Wywołanie metody queryExec ma postać: queryExec(zapytanie[, $msg])

Znaczenie argumentów jest następujące:  zapytanie — zapytanie SQL wysyłane do bazy.  msg — przekazywana przez referencję zmienna, której zostanie przypisany

komunikat zawierający opis błędu, o ile taki wystąpi. Liczbę wierszy w bazie, na które zapytanie miało wpływ, możemy odczytać przez wywołanie metody changes w postaci: changes();

Zobaczmy, jak w praktyce dodać wiersz do tabeli osoba. Takie zadanie wykonuje skrypt widoczny na listingu 8.17. Listing 8.17. Dodanie wiersza do tabeli za pomocą metody exec ";

Rozdział 8. ♦ Współpraca z bazami danych

423

echo "Komunikat błędu \$e: "; echo $e->getMessage(), "
"; echo ""; exit; } $query = "INSERT INTO osoba VALUES("; $query .= "11, 'Maciej', 'Nowak', 1982, 'Lublin'"; $query .= ")"; if(!$result = $db->queryExec($query, $msg)){ echo 'Wystąpił błąd: nieprawidłowe zapytanie...
'; echo "Komunikat błędu: $msg
"; } else{ $ile = $db->changes(); echo "Liczba dodanych rekordów: $ile
"; } unset($db); ?>

Lekcja 26. Bazy danych w praktyce W pierwszych trzech lekcjach tego rozdziału zostały przedstawione różne sposoby obsługi bazy danych. Wiadomo już, jak wysyłać zapytania i pobierać ich wyniki. Wykorzystajmy zatem zdobytą do tej pory wiedzę do stworzenia kilku realnie działających skryptów wykonujących różne praktyczne zadania, takich jak licznik odwiedzin, ankieta, system logowania czy generowanie listy odwiedzin strony. Tym właśnie tematom poświęcona jest ostatnia, 26. lekcja.

Licznik Pierwszym, najprostszym przykładem będzie oparty na bazie danych licznik odwiedzin strony. Ten skrypt potraktujemy jako rozgrzewkę przed kolejnymi projektami. Oczywiście uruchamianie serwera bazy danych takiego jak MySQL i tworzenie bazy tylko dla licznika nie ma większego sensu. Jeśli jednak cała witryna jest oparta na systemie bazodanowym, nic nie stoi na przeszkodzie, aby umieścić w bazie i taką informację. W przykładzie zostanie użyta baza SQLite zapisana w pliku licznik.db umieszczonym w katalogu /var/wwwdata/ (oczywiście ta lokalizacja może być dowolnie zmieniana). Korzystać będziemy z funkcji operujących na SQLite opisanych w lekcji 24. Zacznijmy od utworzenia tabeli licznik przechowującej liczbę odwiedzin. Będzie ona zawierała tylko jedną kolumnę typu INTEGER o nazwie Hits. Utworzymy ją za pomocą instrukcji SQL: CREATE TABLE licznik( Hits INTEGER );

424

PHP5. Praktyczny kurs

Do tabeli wprowadzimy jeden wiersz odpowiadający początkowemu stanowi licznika: INSERT INTO licznik VALUES(0);

Na tak przygotowanej bazie danych będzie wykonywał operacje skrypt zaprezentowany na listingu 8.18. Listing 8.18. Licznik wykorzystujący bazę SQLite '; //echo "Komunikat błędu $msg"; return false; } $query = 'SELECT Hits FROM licznik'; if(!$result = sqlite_query($db_lnk, $query)){ @sqlite_close(); //echo 'Wystąpił błąd nieprawidłowe zapytanie...
'; return false; } if($row = @sqlite_fetch_array($result)){ $counter = $row[0]; } else{ @sqlite_close($db_lnk); //echo 'Błąd w danych'; return false; } $query = "UPDATE licznik SET Hits = Hits + 1"; if(!@sqlite_exec($db_lnk, $query, $msg)){ @sqlite_close(); //echo 'Wystąpił błąd podczas aktualizacji danych.
'; //echo "Komunikat błędu $msg"; return false; } @sqlite_close($db_lnk); return $counter; } ?>

Za pobranie aktualnej wartości licznika oraz jego uaktualnienie odpowiada znajdująca się na początku kodu funkcja getAndUpdateCounter. Jej działanie jest takie, że zwraca ona aktualną liczbę odwiedzin odczytaną z bazy lub też wartość false, jeśli z jakichś powodów odczyt lub aktualizacja danych zakończyły się niepowodzeniem. Przed wszystkimi wywołaniami funkcji operujących na bazie danych został użyty operator kontroli

Rozdział 8. ♦ Współpraca z bazami danych

425

błędów @. Dzięki temu nawet jeśli w pliku konfiguracyjnym php.ini włączona jest opcja wyświetlania błędów i ostrzeżeń, to w przypadku ich wystąpienia nie pojawią się w treści witryny. Wewnątrz funkcji getAndUpdateCounter za pomocą wywołania sqlite_open jest otwierane połączenie z bazą licznik.db, a następnie przy użyciu funkcji sqlite_query jest wykonywane zapytanie o treści: SELECT Hits FROM licznik

zwracające zawartość tabeli licznik. Wynikiem tego zapytania jest tabela o jednym wierszu i jednej kolumnie, a jej zawartość jest pobierana za pomocą funkcji mysql_ fetch_array i zapisywana w zmiennej $counter. Po pobraniu aktualnej zawartości licznika niezbędne jest zwiększenie jego wartości o jeden. Czynność ta jest wykonywana przez zapytanie SQL w postaci: UPDATE licznik SET Hits = Hits + 1

które jest wysyłane do bazy za pomocą funkcji sqlite_exec. Ostatecznie połączenie z bazą jest zamykane przez wywołanie sqlite_close, a wartość zapisana w $counter jest zwracana za pomocą instrukcji return. Za kodem funkcji należy umieścić właściwą treść witryny, która może wyglądać tak jak na listingu 8.19. Listing 8.19. Przykładowa strona korzystająca z licznika Moja strona WWW

Treść strony



Wewnątrz kodu HTML znajduje się fragment kodu PHP. Jego zadaniem jest wywołanie funkcji getAndUpdateCounter oraz wyświetlenie na ekranie informacji o stanie licznika. Ten fragment wykonuje bardzo proste zadanie: jeśli wywołanie funkcji zakończyło

426

PHP5. Praktyczny kurs

się sukcesem, czyli zwrócona przez nią wartość jest różna od false, jest wyświetlana liczba odwiedzin, jeśli natomiast wartością zwróconą było false, wyświetlana jest informacja o niedostępności licznika.

Logowanie W lekcji 16. z rozdziału 4. powstał prosty system kontroli dostępu do witryny, w którym nazwy i hasła użytkowników były zapisane w pliku tekstowym. Spróbujmy więc teraz wykonać to zadanie, opierając się na bazie danych MySQL. W tym celu w bazie należy utworzyć tabelę users, do czego można wykorzystać instrukcję SQL (kolumna id odpowiada za przechowywanie identyfikatora użytkownika): CREATE TABLE users( id INTEGER AUTO_INCREMENT PRIMARY KEY, nazwa VARCHAR(40) UNIQUE, haslo VARCHAR(40) );

Od razu warto też wprowadzić do niej przykładowy wiersz, który pozwoli na testowanie skryptu: INSERT INTO users (nazwa, haslo) VALUES('user1', 'pass1');

A zatem do witryny będzie miał dostęp użytkownik posługujący się nazwą user1 i hasłem pass1. Struktura skryptów będzie taka sama jak w przykładach z lekcji 16. Całość będzie się składała z trzech plików:  index.php — zawiera procedury i formularz logowania,  glowna.php — zawiera główną część serwisu,  logout.php — zawiera procedury wylogowania.

Pliki glowna.php i logout.php nie wymagają żadnych zmian, dlatego też ich treść nie będzie ponownie zamieszczana. Zmian wymaga natomiast procedura logowania z pliku index.php. Wymienić należy funkcję checkPass. Nowa treść tej funkcji została zaprezentowana na listingu 8.20. Listing 8.20. Kontrola dostępu z wykorzystaniem bazy danych function checkPass($user, $pass) { //nawiązanie połączenia z bazą if (!$db_lnk = @mysql_connect("localhost", "php", "test")){ //echo('Wystąpił błąd podczas próby połączenia z bazą...
'); return false; } if(!@mysql_select_db('testphp')){ @mysql_close(); //echo('Wystąpił błąd podczas wyboru bazy danych testphp
');

Rozdział 8. ♦ Współpraca z bazami danych

427

return false; } //wykonanie zapytania $query = "SELECT haslo FROM users WHERE nazwa='$user'"; if(!$result = mysql_query($query, $db_lnk)){ @mysql_close(); //echo('Wystąpił błąd nieprawidłowe zapytanie...
'); return false; } //błędna liczba wierszy wynikowych if(mysql_num_rows($result) != 1){ @mysql_close(); return false; } //odczyt wyników zapytania if(!$row = mysql_fetch_row($result)){ @mysql_close(); //echo('Wystąpił błąd podczas pobierania wyników zapytania ...
'); return false; } //ostateczne sprawdzenie poprawności danych otrzymanych z formularza if($row[0] == $pass){ $result = true; } else{ $result = false; } @mysql_close(); return $result; }

W treści funkcji checkPass najpierw następuje nawiązanie połączenia z serwerem MySQL oraz wybranie bazy testphp, co jest wykonywane w sposób opisywany już we wcześniejszych lekcjach. Następnie wykonywane jest zapytanie, którego zadaniem jest pobranie hasła użytkownika o nazwie przekazanej w postaci argumentu $user. Ma ono postać: SELECT haslo FROM users WHERE nazwa='$user'

A zatem pobiera takie wiersze tabeli z kolumny haslo, które w kolumnie nazwa mają wpis odpowiadający parametrowi $user (czyli danym otrzymanym z przeglądarki). Zapytanie jest wykonywane za pomocą funkcji mysql_query. Następnie sprawdzana jest liczba wierszy wynikowych, co jest wykonywane za pomocą funkcji mysql_num_rows. Każda liczba inna niż 1 oznacza, że dane są niepoprawne (może bowiem istnieć tylko jeden użytkownik o danej nazwie i danym haśle). W kolejnym kroku jest sprawdzane, czy hasło odczytane z bazy jest takie samo jak hasło otrzymane z przeglądarki (wyniki są odczytywane w znany nam sposób, dzięki

428

PHP5. Praktyczny kurs

funkcji mysql_fetch_row). Jeśli tak jest, użytkownik może być zalogowany i funkcja zwraca wartość true. W przeciwnym razie zwracana jest wartość false. Aby użyć takiego skryptu na realnie działającej witrynie, należy dodatkowo poddać weryfikacji dane otrzymywane w postaci argumentów $user oraz $haslo i usunąć z nich wszelkie niedopuszczalne znaki. Można to wykonać za pomocą wyrażeń regularnych. Ten temat wykracza jednak poza ramy tematyczne książki. Jeżeli taka weryfikacja nie zostanie wykonana, skrypt będzie podatny na ataki typu SQL Injection.

Ankieta Często spotykanym elementem witryn są różnego rodzaju sondy i ankiety, które pozwalają na poznanie opinii na różne tematy osób odwiedzających witrynę. Stworzenie systemu do głosowania za pomocą PHP nie jest skomplikowane. Zacznijmy od przygotowania formularza umożliwiającego oddawanie głosów. W tym przykładzie będzie można głosować na ulubione kolory. Formularz głosowania został przedstawiony na listingu 8.21 i wykorzystuje typowe konstrukcje i znaczniki języka HTML. Po wczytaniu go do przeglądarki pojawi się widok zaprezentowany na rysunku 8.37. Listing 8.21. Formularz umożliwiający oddawanie głosów Moja strona WWW
Jaki jest Twój ulubiony kolor?
IdImięNazwisko Rok urodzeniaMiejsce urodzenia
{$row[0]}{$row[1]}{$row[2]}{$row[3]} {$row[4]}
{$row['Id']}{$row['Imie']}{$row['Nazwisko']}{$row['Rok_urodzenia']}{$row['Miejsce_urodzenia']}
$row[0]$row[1]$row[2]$row[3]$row[4]


Rozdział 8. ♦ Współpraca z bazami danych

429

czerwony
zielony
niebieski
fioletowy
czarny


Rysunek 8.35. Wygląd formularza umożliwiającego oddawanie głosów

Dane będą zapisywane w bazie, w tabeli o nazwie kolory, która będzie miała dwie kolumny — Kolor i Glosy. W pierwszej będą zapisywane nazwy kolorów, a w drugiej liczba oddanych na nie głosów. Tabela taka jest generowana przez instrukcję SQL w postaci: CREATE TABLE kolory( Kolor VARCHAR(15) NOT NULL, Glosy INTEGER );

Po utworzeniu tabeli należy wprowadzić do niej dane początkowe, czyli nazwy kolorów i pierwotną liczbę głosów dla każdego z nich. Tę czynność można wykonać za pomocą serii instrukcji SQL: INSERT INTO kolory (Kolor, Glosy) VALUES('czerwony', 0); INSERT INTO kolory (Kolor, Glosy) VALUES('zielony', 0); INSERT INTO kolory (Kolor, Glosy) VALUES('niebieski', 0);

430

PHP5. Praktyczny kurs INSERT INTO kolory (Kolor, Glosy) VALUES('fioletowy', 0); INSERT INTO kolory (Kolor, Glosy) VALUES('czarny', 0);

Skrypt ankieta.php zajmujący się uaktualnianiem i prezentowaniem danych został przedstawiony na listingu 8.22. Do obsługi bazy jest wykorzystywany moduł PDO. Listing 8.22. Treść skryptu ankieta.php
getMessage(); return false; } $query = "UPDATE kolory SET Glosy = Glosy + 1 ". "WHERE Kolor = '".$kolor."'"; $result = $dbo->query($query); if(!$result){ //echo "Błąd w zapytaniu UPDATE"; return false; } $query = 'SELECT SUM(Glosy) FROM kolory'; if(!$result = $dbo->query($query)){ //echo "Błąd w zapytaniu SELECT SUM"; return false; } if(!$row = $result->fetch(PDO::FETCH_NUM)){ //echo "Błąd przy przetwarzaniu wyników pierwszego zapytania SELECT"; echo "Wyniki ankiety nie są w tej chwili dostępne."; return false; } $votes_no = $row[0]; $query = "SELECT Kolor, Glosy, Glosy * 100 /".$votes_no; $query .= " AS Proc FROM kolory ORDER BY Glosy DESC"; if(!$result = $dbo->query($query)){ //echo "Błąd w drugim zapytaniu SELECT"; return false; } echo "";

Rozdział 8. ♦ Współpraca z bazami danych echo echo echo echo echo

431

""; ""; ""; ""; "";

while($row = $result->fetch(PDO::FETCH_NUM)){ echo ""; echo ""; echo ""; echo ""; echo ""; } echo "
Nazwa koloruLiczba głosówProcent głosów
{$row[0]}{$row[1]}{$row[2]}
"; $dbo = null; return true; } if(isSet($_GET["vote"])){ $kolor = $_GET["vote"]; } else{ $kolor = ""; } if($kolor == "" || ($kolor != 'czerwony' && $kolor != 'zielony' && $kolor != 'niebieski' && $kolor != 'fioletowy' && $kolor != 'czarny')){ echo "Proszę zaznaczyć jeden z kolorów."; } else if(!readAndUpdateData($kolor)){ echo "Wyniki ankiety nie są w tej chwili dostępne."; } ?>


Skrypt sprawdza najpierw, czy zostały do niego przekazane dane określające kolor, na który zagłosował użytkownik (pole vote tablicy $_GET). Jeśli parametr vote został przekazany, jego wartość jest zapisywana w zmiennej pomocniczej $kolor, jeśli nie został — zmiennej tej jest przypisywany pusty ciąg znaków. Następnie następuje sprawdzenie, czy w zmiennej $kolor znajdują się poprawne dane, to znaczy, czy jest ona różna od pustego ciągu znaków oraz czy zawiera jeden z ciągów znaków opisujących kolor. Gdy dane są poprawne, następuje wywołanie funkcji readAndUpdateData, która zajmuje się uaktualnieniem tabeli kolory oraz wyświetleniem wyników głosowania. Jeżeli dane są nieprawidłowe, wyświetlany jest tekst z prośbą o zaznaczenie jednego z kolorów. Wartość zwrócona przez readAndUpdateData jest badana w kolejnej instrukcji warunkowej if. Funkcja ta zwraca bowiem true, jeżeli jej wykonanie zakończyło się sukcesem, bądź też false w przeciwnym razie. Jeśli więc wystąpił jakiś błąd, wyświetlany jest komunikat informujący, że wyniki ankiety nie są dostępne.

432

PHP5. Praktyczny kurs

Funkcja readAndUpdateData nawiązuje połączenie z bazą danych w sposób opisany w lekcji 25., a następnie wykonuje zapytanie uaktualniające dane w postaci: UPDATE kolory SET Glosy = Glosy + 1 WHERE Kolor = '$kolor'

Zwiększa ono o jeden wartość pola Glosy, które odpowiada wybranemu kolorowi. W tabeli wynikowej będzie prezentowana zarówno liczba, jak i procent głosów oddanych na dany kolor. W związku z tym po aktualizacji danych następuje wyliczenie procentów. Zaczyna się to od uzyskania informacji o sumie wszystkich oddanych głosów, za co odpowiada zapytanie: SELECT SUM(Glosy) FROM kolory

Jego wynik jest zapisywany w zmiennej $votes_no (użyta w zapytaniu funkcja SQL SUM sumuje wartości z kolumny o nazwie przekazanej jej w postaci argumentu). Zmienna ta jest następnie wykorzystywana w kolejnym zapytaniu SQL, które już bezpośrednio wylicza procent głosów oddanych na każdy z kolorów. Wykorzystywana jest w tym celu instrukcja SQL w postaci: SELECT Kolor, Glosy, Glosy * 100 / $votes_no AS Proc FROM kolory ORDER BY Glosy DESC

Jak widać, wartość procentowa jest wyliczana z prostego wzoru: procent = liczba głosów na dany kolor * 100 / całkowita liczba głosów

Całość jest sortowana w porządku malejącym, a zatem na górze tabeli wynikowej znajdzie się kolor, na który zostało oddane najwięcej głosów. Do wyświetlenia tak uzyskanych wyników na ekranie służy prosta pętla typu while pobierająca kolejne wiersze tabeli wynikowej za pomocą metody fetch. W przeglądarce pojawia się więc tabela z wynikami głosowania, taka jak zaprezentowana na rysunku 8.36. Rysunek 8.36. Przykładowe wyniki ankiety

Rozdział 8. ♦ Współpraca z bazami danych

433

Lista odwiedzin W lekcji 12. (rozdział 3.). powstał skrypt, który w pliku tekstowym zapamiętywał dane dotyczące odwiedzających witrynę użytkowników: datę i czas odwiedzin, adres IP, rodzaj przeglądarki. Informacje te były następnie wyświetlane na stronie. Warto pokusić się o napisanie skryptu realizującego takie zadanie, który tym razem będzie pracował, opierając się na bazie danych. Wykorzystamy w tym celu bazę MySQL, w której umieścimy tabelę stats zawierającą trzy kolumny:  time — przechowującą informację o czasie,  addr — przechowującą informację o adresie IP,  agent — przechowującą informację o przeglądarce i systemie operacyjnym.

Utworzymy ją za pomocą następującej instrukcji SQL: CREATE TABLE stats( time INTEGER, addr VARCHAR(255), agent VARCHAR(255) );

Treść skryptu obsługującego bazę danych została przedstawiona na listingu 8.23. Składa się on z trzech funkcji:  writeData — zapisuje dane w tabeli Stats,  readData — odczytuje dane z tabeli Stats,  getAndWriteData — zajmuje się nawiązaniem połączenia z bazą oraz

przetwarzaniem danych. Tym razem do łączenia z bazą została użyta technika przedstawiona w lekcji 24. Listing 8.23. Lista ostatnich odwiedzin strony
434

PHP5. Praktyczny kurs } else{ return true; } } function readData($ile, $delimiter) { $query = "SELECT * FROM stats ORDER BY time DESC LIMIT $ile"; if(!$result = @mysql_query($query)){ //echo("Problem z bazą danych odrzucone zapytanie"); return false; } $str = ""; while(($row = @mysql_fetch_row($result)) != false){ $str .= date("Y-m-d G:i", $row[0]) . " "; $str .= $row[1] . " "; $str .= $row[2] . $delimiter; } return $str; } function getAndWriteData($ile, $delimiter) { if(!$link = @mysql_connect("localhost", "php", "test")){ //echo("Problem z połączeniem z bazą danych"); return; } if(!@mysql_select_db("testphp")){ //echo("Problem z wyborem bazy testphp"); @mysql_close($link); return; } $dane = readData($ile, $delimiter); writeData(); @mysql_close($link); return $dane; } ?> Lista odwiedzin
"); ?>


Rozdział 8. ♦ Współpraca z bazami danych

435

Zadaniem funkcji writeData jest zapisanie danych w bazie. Odczytuje ona czas wejścia na stronę przez wywołanie funkcji time (będzie on zatem zapisany jako znacznik czasu Uniksa), a adres IP i dane dotyczące przeglądarki z tablicy $_SERVER, dokładnie tak samo jak w przypadku skryptu 3.25. Następnie wykonuje zapytanie SQL w postaci: INSERT INTO stats (time, addr, agent) VALUES('$time', '$addr', '$agent')

Jeśli jego wykonanie zakończy się sukcesem, zwracana jest wartość true, a w przeciwnym wypadku false. Funkcja readData ma za zadanie pobrać dane z bazy i zwrócić je w postaci ciągu typu string. Przyjmuje ona dwa argumenty — $ile i $delimiter. Pierwszy z nich określa, ile wierszy powinna mieć tabela wynikowa, czyli ile wpisów chcemy prezentować na stronie, drugi — ciąg, jaki zostanie użyty do rozdzielania poszczególnych wierszy. Dzięki temu w przypadku wyświetlania danych na stronie WWW będzie można zastosować np. znaczniki
, a w przypadku wyświetlania danych na konsoli np. znacznik końca linii \n. Funkcja wysyła do bazy zapytanie SQL w postaci: SELECT * FROM stats ORDER BY time DESC LIMIT $ile

Oznacza ono pobranie z tabeli stats wierszy ustawionych względem kolumny time (ORDER BY time) w kolejności malejącej (DESC), w liczbie nie większej niż $ile (LIMIT $ile). Dane zwrócone przez zapytanie są odczytywane w pętli while i dopisywane do zmiennej $str. Ponieważ czas każdego wpisu w bazie ma postać znacznika czasu Uniksa, dane z kolumny time są formatowane za pomocą funkcji date. Na końcu każdego odczytanego wiersza jest natomiast umieszczany ciąg znaków zapisany w parametrze $delimiter. Ostatecznie po zakończeniu pętli while uzyskany ciąg znaków jest zwracany za pomocą instrukcji return. Funkcja getAndWriteData nawiązuje połączenie z bazą danych, wybiera bazę testphp, a następnie wywołuje funkcję readData, przypisując wynik jej działania zmiennej $dane. Po wykonaniu tych czynności zapisuje dane dotyczące aktualnego połączenia, wywołując funkcję writeData, oraz zamyka połączenie z bazą i zwraca odczytane dane za pomocą instrukcji return.

Liczba osób na stronie Informacją często spotykaną na witrynach WWW jest liczba osób aktualnie przeglądających daną stronę. Skrypty realizujące takie zadanie nie są wcale skomplikowane, gdyż zazwyczaj przyjmują upraszczające założenie, że każdy użytkownik jest obecny na stronie przez taki sam czas. Jeśli będzie to na przykład 120 sekund, należy wtedy wykonać następujące czynności:  zapisanie czasu wejścia użytkownika na stronę w bazie danych,  usunięcie z bazy wszystkich wpisów starszych niż 120 sekund,  zsumowanie wpisów znajdujących się w bazie,  wyświetlenie uzyskanej wartości na ekranie.

436

PHP5. Praktyczny kurs

Te wszystkie czynności wykonywane są przez skrypt widoczny na listingu 8.24. Korzysta on z bazy SQLite w pliku wizyty.db umieszczonym w katalogu /var/wwwdata/. Listing 8.24. Liczba osób obecnych na stronie '; //echo "Komunikat błędu $msg"; return false; } $time = time(); $query = "INSERT INTO czas_wizyt (czas) VALUES($time)"; if(!@sqlite_exec($db_lnk, $query, $msg)){ @sqlite_close(); //echo 'Błąd podczas wykonywania instrukcji INSERT.
'; //echo "Komunikat błędu $msg"; return false; } $val = $time - $timeout; $query = "DELETE FROM czas_wizyt WHERE czas < '$val'"; if(!@sqlite_exec($db_lnk, $query, $msg)){ @sqlite_close(); //echo 'Błąd podczas wykonywania instrukcji DELETE.
'; //echo "Komunikat błędu $msg"; return false; } $query = "SELECT COUNT(*) FROM czas_wizyt"; if(!$result = sqlite_query($db_lnk, $query)){ @sqlite_close(); //echo 'Błąd podczas wykonywania instrukcji SELECT.
'; return false; } if($row = @sqlite_fetch_array($result)){ $counter = $row[0]; } else{ @sqlite_close($db_lnk); //echo "Błąd w danych"; return false; } @sqlite_close($db_lnk); return $counter; } ?>

Rozdział 8. ♦ Współpraca z bazami danych

437

Moja strona WWW


Dane są przechowywane w tabeli bazy danych o nazwie czas_wizyt. Tabela ta posiada tylko jedną kolumnę o nazwie czas, utworzymy ją zatem za pomocą instrukcji SQL w postaci: CREATE TABLE czas_wizyt ( czas INTEGER );

Każde wywołanie skryptu z listingu 8.24 powoduje nawiązanie połączenia z bazą danych i dodanie do tabeli nowego wpisu, za co jest odpowiedzialna instrukcja SQL: INSERT INTO czas_wizyt (czas) VALUES($time)

Zmienna $time zawiera znacznik czasu zwrócony przez funkcję o takiej samej nazwie, czyli time. Po uzyskaniu aktualnego czasu usuwane są wszystkie wpisy starsze niż liczba sekund wskazywana przez zmienną $timeout. W tym celu najpierw jest obliczana różnica: $time – $timeout, a jej wynik jest zapisywany w zmiennej $val. W kolejnym kroku usuwane są wszystkie wpisy, których wartość jest mniejsza niż wartość zmiennej $val. Za usunięcie wpisów odpowiada instrukcja SQL: DELETE FROM czas_wizyt WHERE czas < '$val'

po wykonaniu której w tabeli pozostaną jedynie wpisy nie starsze niż liczba sekund zapisana w $timeout. Wystarczy je zatem policzyć, aby stwierdzić, ile osób w ostatnim czasie było obecnych na stronie. Tego obliczenia dokonuje instrukcja SQL: SELECT COUNT(*) FROM czas_wizyt

Ćwiczenia do samodzielnego wykonania Ćwiczenie 26.1. Zmodyfikuj przykład dotyczący ankiety w taki sposób, aby opcje wyboru były generowane automatycznie na podstawie zawartości tabeli kolory.

438

PHP5. Praktyczny kurs

Ćwiczenie 26.2. Napisz skrypt wyświetlający liczbę osób obecnych na stronie i operujący na danych z tabeli stats ze skryptu generującego listę odwiedzin, ale pracujący na podstawie bazy danych SQLite. Ćwiczenie 26.3. Napisz taki skrypt logowania, który pozwoli na to, aby każdy użytkownik mógł mieć własną stronę startową. Ćwiczenie 26.4. Napisz skrypt, który na podstawie tabeli stats z przykładu „Lista odwiedzin” wyświetli statystykę systemów operacyjnych i przeglądarek. Ćwiczenie 26.5. Napisz skrypt, który będzie umożliwiał zarządzanie danymi (dodawanie, modyfikację i usuwanie rekordów) w tabeli users używanej w skrypcie logowania.

Skorowidz -, 69 --, 76 !, 389 !=, 77, 80, 387 !==, 77, 80 ", 66 #, 48 ∗/, 47 $, 57, 62, 66 $_COOKIE, 85 $_ENV, 85 $_FILES, 85 $_GET, 85 $_POST, 85 $_REQUEST, 85 $_SERVER, 84 $_SERVER (tablica), 339 $_SESSION, 85 $dbo = null;, 412 $dbo->query("Treść zapytania") 413 $GLOBALS, 84 $host, 349 $HTTP_COOKIE_VARS, 85 $HTTP_ENV_VARS, 85 $HTTP_GET_VARS, 85 $HTTP_POST_FILES, 85 $HTTP_POST_VARS, 85 $HTTP_SERVER_VARS, 84 $pass, 349 $plik_lokalny, 349 $plik_zdalny, 349 $user, 349 %, 69 %%, 149 %=, 76 %>, 46 %kod, 149 &, 71, 124

&&, 388 &=, 76 *, 69 *=, 76 ,, 83 ., 142 .=, 76 /, 69 /∗, 47 //, 48 /=, 76 ?>, 42, 45, 46 @, 81 \, 66 ^, 71 ^=, 76 __autoload, 284, 285 __construct, 281 |, 71 ||, 388 |=, 76 ~, 71 +, 69, 79 ++, 76 +=, 76 <, 77, 387 , 42 <%, 46

, 44 , 46 , 387 <>, 77, 80, 387

440

PHP5. Praktyczny kurs

, 42

, 50 , 42 , 42

, 42