Kurs PHP. Wstęp do programowania w popularnym języku skryptowym

19. Baza danych MySQL

19.1. Wstęp

Użycie bazy danych to dobra alternatywa wobec plików, zwłaszcza przy budowaniu dużych serwisów czy złożonych statystyk. Pobieranie i zapisywania informacji w bazach danych opiera się na wysyłaniu do serwera bazy zapytań zapisywanych w języku SQL (Structured Query Language) zawartych w kodzie PHP, na które serwer odpowiada, pobierając z bazy odpowiednie dane i wysyłając je do nas. Zrozumienie tego na pewno ułatwi poniższy schemat:

1. Klient wysyła do serwera zapytanie o stronę WWW,
2. Serwer WWW wysyła do parsera kod PHP,
3. Parser wysyła do serwera baz danych zapytanie o jakieś dane,
4. Serwer baz zwraca dane, które są używane dalej w skrypcie,
5. Parser wysyła gotowy kod HTML do serwera WWW,
6. Serwer WWW wysyła kod HTML do przeglądarki klienta.

Przy pisaniu kodu PHP z użyciem baz danych trzeba pamiętać, że jeśli serwer PHP nie znajduje się (fizycznie) na tym samym serwerze (czyli nie ma możliwości odwołania się do niego poprzez localhost), tylko np. w innej części Internetu, może to znacznie zwolnić pracę skryptu. Podczas naszych standardowych testów po uruchomieniu strony z połączeniem do serwera bazy MySQL na innym hoście czas generowania strony wyniósł około 2,5 sekundy, a podczas gdy baza znajdowała się na serwerze lokalnym, nie trwało to nawet 0,01 sekundy.

19.2. Struktura bazy

Schemat obrazuje strukturę bazy danych. Najważniejszym elementem jest oczywiście sama baza – zazwyczaj jedna przydzielana jest dla każdego użytkownika, chociaż na serwerach komercyjnych dostaje się czasem nawet i dziesięć baz. Ingerować w bazy (tworzyć, usuwać) może zazwyczaj tylko administrator. Kolejnym elementem są tabele – to są dopiero struktury przechowujące dane. Są one dowolnie modyfikowane przez użytkownika i może ich być dowolna liczba, bo ograniczenia są zazwyczaj nakładane przez firmy hostingowe na wielkość całej bazy, a nie na liczbę tabel.
Aby ułatwić naukę obsługi bazy MySQL-a i tworzenia własnych przykładów, we wszystkich przykładach tworzona będzie i rozbudowywana baza danych fikcyjnych uczniów jakiejś klasy oraz skrypty ją obsługujące.

19.3. Połączenie z bazą

Przed wykonywaniem operacji na bazie danych należy się z nią połączyć. Trzeba użyć funkcji mysql_connect(host[:port], nazwa użytkownika, hasło). Funkcja ta łączy się z serwerem podanym jako argument host. Opcjonalnie, jeśli MySQL pracuje na innym porcie niż 3306, należy też to podać. Dla przykładu:

Zmienna $sql podana na początku to uchwyt (handle) do połączenia z bazą. Ponownie potrzebny będzie tylko na końcu wykonywania skryptu, aby rozłączyć się z bazą:

Jeśli chcemy utworzyć duży serwis, korzystający z bazy i często odświeżany, warto połączyć się z bazą funkcją mysql_pconnect (permanent connect – stałe połączenie), dzięki któremu nie trzeba się za każdym razem łączyć z bazą. Wszystkie argumenty są te same. Występuje tutaj tylko jedna różnica: połączenia pconnect nie trzeba zamykać na końcu skryptu funkcją mysql_close.

19.4. Język SQL

Z bazą „kontaktujemy się” za pomocą zapytań. Są to polecenia pisane w języku SQL (bardzo proste) wysyłane dzięki funkcji mysql_query, która zwraca zmienną z wynikami zapytania. Najczęściej używane tutaj nazwy zmiennych są widoczne na przykładzie, ale oczywiście możemy ustalić swoje własne. Dla przykładu:

19.5. Pierwsze operacje, nowa baza

Jeśli mamy dostęp do serwera, możemy sobie założyć bazę. Jedyną sprawą, o której należy pamiętać, jest wywołanie funkcji mysql_create_db(nazwa), która tworzy bazę o nazwie podanej w argumencie. Po utworzeniu i przed rozpoczęciem pracy należy pamiętać, aby też wybrać bazę, z którą chcemy pracować. Robimy to za pomocą polecenia mysql_select_db(nazwa). Dla przykładu:

19.6. Nowe tabele

Gdy założyliśmy już bazę danych, kolejnym krokiem jest utworzenie tabel. W tym celu budujemy zapytanie o następującej składni:

Możemy teraz utworzyć tabelę według podanego powyżej zapytania.

Stworzona zostanie tabela o nazwie uczniowie z polami id, imie, nazwisko, urodzony, przy czym id będzie kluczem podstawowym, czyli indeksem, i nie będzie się mógł powtarzać. Oprócz tego, jeśli dodamy NOT NULL, to klucz nie będzie mógł być pusty. Opcja AUTO_INCREMENT sprawia natomiast, że jeśli nie podamy wartości id do wpisania, to zostanie ona automatycznie wpisania (wartością stanie się wartość poprzedniego rekordu id podniesiona o jeden).
Dostępnych jest wiele rodzajów pól – oto kilka najważniejszych:

• char(x) – ciąg znaków o maksymalnej długości podanej jako parametr x (x<256),
• longtext – ciąg znaków o praktycznie nieograniczonej długości,
• date – data w formacie YYYY-MM-DD,
• year – rok; jeśli zostanie podany zły, jego wartość zmieni się na 0000,
• time – przechowuje czas w formacie timestamp,
• tinyint, smallint, mediumint, int, bigint – liczby całkowite,
• longblob – przechowuje dane binarne (np. obrazki) o praktycznie nieograniczonej wielkości,
• enum, set – podobne jak „radio” w formularzach, do wyboru są tylko opcje zadeklarowane uprzednio przy tworzeniu bazy (np. tak/nie).

19.7. Wstawianie danych

Aby wstawić jakieś dane do tabeli, należy posłużyć się instrukcją INSERT. Jej schemat jest następujący:

Jako pole1, pole2 itd. wpisujemy nazwy pól, które chcemy wypełnić, a w wart_pola1 itd. w odpowiedniej kolejności wartości do dodania. Dla przykładu:

W powyższym wypadku wartość pola id zostanie ustalona na 1, ponieważ jest to pierwszy rekord w tabeli. Jeśli wstawialibyśmy to samo jeszcze raz, to miałby on cały czas wartość id o jeden większą od poprzedniej. Wstawmy do bazy kilka pojedynczych rekordów z danymi uczniów:

19.8. Wybieranie konkretnych rekordów

Aby wybrać z bazy danych jakieś dane, należy posłużyć się operatorem SELECT. Jego składnia to:

Jest to dość zawiłe, dlatego teraz po kolei wytłumaczymy całe zapytanie.

• pole1, pole2, pole_n – określają nazwy pól, które serwer baz ma wysłać w wyniku. Jeśli chce się otrzymać wszystkie, wystarczy wpisać * (gwiazdkę),
• FROM nazwa_tabeli – to chyba najprostszy fragment; określa nazwę tabeli, z której mają być wybrane rekordy,
• WHERE warunek1 AND warunek2 – to warunek (warunki), według których serwer ma przeszukiwać bazę. Można nie podać ich wcale i wtedy wynik będzie zawierał wszystkie rekordy z tabeli. Warunki można łączyć operatorami AND oraz OR, a także brać w nawiasy (tak jak w IF w PHP),
• ORDER BY nazwa_klucza rodzaj_sortowania – określa, według jakiego klucza posortowane mają być wyniki i w jakiej kolejności. Dostępne są dwa rodzaje sortowania: ASC (rosnące) i DESC (malejące),
• LIMIT klucz_poczatkowy, ilosc_rekordów – określa, ile wyników ma być zwróconych. Warto pamiętać, że ilosc_rekordow pokazuje, ile ma być zwróconych rekordów od klucz_początkowy, a nie jest to id klucza końcowego (częsty błąd).

Na schemacie pokazujemy przykład użycia LIMIT. W wynikach jest 16 rekordów, potrzebne są rekordy od 3 do 14. Ważne jest, aby się nie pomylić i w ilosc_rekordow podać całkowitą liczbę potrzebnych rekordów, a nie id ostatniego (tak jak w większości baz danych czy na adresowaniu komórek w Excelu). Dla przykładu:

W warunkach do przeszukiwania mogą też wystąpić takie operatory, jak IN lub BETWEEN. Dla przykładu:

Zastępcą znanego z instrukcji warunkowych wykrzyknika jest NOT. Można go użyć zarówno przed pojedynczą instrukcją, jak i przed całym nawiasem. Dla przykładu:

Nie zawsze da się dokładnie porównać dwa ciągi znaków. Czasem należy posłużyć się operatorem LIKE. Porównuje on konkretną wartość ze wzorcem. Zaznaczmy przy tym, że % (procent) zastępuje w wyrażeniu dowolną liczbę znaków, a _ (podkreślenie) zastępuje dokładnie jeden znak. Dla przykładu:

19.9. Modyfikacja rekordów

Czasem zachodzi potrzeba poprawienia jakiegoś rekordu. Błąd może być wynikiem nieuwagi przy wprowadzaniu danych, ale możemy też chcieć zmienić adres klienta w bazie danych sklepu internetowego. Służy do tego polecenie UPDATE. Jego składnia to:

Przyjrzyjmy się bliżej temu zapytaniu:

• nazwa_tabeli – to po prostu nazwa tabeli, w której ma nastąpić zamiana rekordów,
• SET pole1='xyz', pole2 = 'abc', polen = 'bcd' – określa pola i wartości, które mają być do nich przydzielone,
• WHERE warunki – określa, które rekordy mają być modyfikowane. W wypadku gdy nie podamy warunku w WHERE, modyfikacji ulegają wszystkie rekordy w tabeli.

Dla przykładu:

19.10. Usuwanie rekordów

Do usuwania jednego, kilku określonych rekordów lub zawartości całej tabeli służy DELETE. Jego składnia to:

Oznaczenia są takie same jak w poprzednich poleceniach i nie trzeba ich jeszcze raz opisywać. Warto zaś zaznaczyć, że jeśli niepodany zostanie warunek WHERE, to tabela zostanie wyczyszczona. Dla przykładu:

19.11. Funkcje do obsługi MySQL-a

W PHP istnieje wiele funkcji do kontaktu z bazą danych MySQL i obsługi zapytań. Podstawowe już znamy:

• mysql_connect – łączy z bazą danych,
• mysql_pconnect – łączy trwale z bazą danych,
• mysql_create_db – tworzy bazę danych,
• mysql_select_db – wybiera bazę danych,
• mysql_close – kończy połączenie z bazą danych,
• mysql_query – wysyła zapytanie do bazy danych.

19.12. mysql_num_rows()

Funkcja mysql_num_rows(rezultat) zwraca liczbę rekordów z wyniku zapytania skierowanego do serwera bazy. Poniższy przykład wyświetla liczę rekordów w tabeli uczniowie:

19.13. mysql_fetch_array()

Funkcja mysql_fetch_array(rezultat [,typ]) zwraca przy każdym wywołaniu kolejny wiersz z wyniku zapytania w postaci tablicy zwykłej lub asocjacyjnej (lub jednej i drugiej). Typ tablicy zależy właśnie od parametru typ. Może on przybrać wartość MYSQL_ASSOC (zwraca wynik w postaci tablicy asocjacyjnej), MYSQL_NUM (w postaci zwykłej tablicy) albo (domyślnie) MYSQL_BOTH (tablicy indeksowanej asocjacyjnie i numerycznie). Dla przykładu:

19.14. mysql_insert_id()

Funkcja mysql_insert_id() zwraca ID wygenerowane dla pola z własnością AUTO_INCREMENT. Uwaga! Funkcja mysql_insert_id() zwróci 0, gdy rekord był dodawany do tabeli bez pola AUTO_INCREMENT. Dla przykładu:

19.15. mysql_result()

mysql_result(rezultat ,wiersz [,kolumna])- Bardzo przydatna funkcja, gdy trzeba się odwołać do konkretnego pola w konkretnym rzędzie wyniku. Wystarczy podać jako pierwszy argument zmienną z rezultatem zapytania, jako drugi wiersz, a jako trzeci (opcjonalnie)pole w danym wierszu (domyślne to 0).

19.16. mysql_error()

Funkcja mysql_error() pokazuje treść ostatniego błędu wygenerowanego przez serwer MySQL. Warto wywołać tę funkcję po zapytaniu do bazy, które powoduje błędy. Dla przykładu:

wyświetli coś w stylu „#1146 – Table 'baza.ucnziowie' doesn't exist”.

19.17. Wyświetlanie wyniku zapytania

Częstym błędem początkującego programisty jest próba wykonania następującego kodu:

Niestety, $res jest niejako obiektem zawierającym zgrupowane dane, a więc za pomocą echo nie da się ich bezpośrednio wyświetlić. Istnieją dwie podstawowe metody przeglądania danych z zapytania do MySQL-a. Pierwsza – wiersz po wierszu – i druga – bezpośrednio odwołując się do danego wiersza lub kolumny.

1. Pętla while i mysql_fetch_array
Dla przykładu:

Przy każdej iteracji pętli zmienna $row staje się czteroelementową tablicą. Dzięki ustawionemu MYSQL_BOTH do rekordów mamy dostęp zarówno poprzez nazwy indeksów (np. config_value), jak i poprzez kolejne cyfry (w danym wypadku config_name to 0, a config_value to 1). Ważne jest to, by wybrać wygodniejszy dla siebie sposób, pamiętając o wadach każdego z nich (MYSQL_ASSOC to więcej pisania, przy MYSQL_NUM natomiast podczas zmiany zapytania może się „posypać” cały skrypt).

2. Funkcja mysql_result()
Metoda ta jest dobra przy pobieraniu pojedynczego elementu z dużego zapytania do bazy. Jej zaletą jest to, że nie trzeba przechodzić przez całą bazę, wadą jest natomiast konieczność znajomości dokładnej lokalizacji wyniku w zapytaniu (współrzędne wiersz i kolumna).
Dla przykładu:

Do administrowania swoją bazą danych, a w szczególności całym serwerem i wszystkimi bazami, polecamy używania darmowego skryptu phpMyAdmin. Pozwala on na bardzo szybki i sprawny nadzór nad bazami, tabelami i użytkownikami baz.