Baza danych MySQL


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.

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.

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:
<?
$sql = mysql_connect(’serwer_baz.org’, ‘login’, ‘moje_haslo’);
?>

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ą:
<?
mysql_close($sql);
?>

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.

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:
<?
$query=”ZAPYTANIE”;
$res = mysql_query($query);
?>
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:
<?
mysql_create_db(“klasa”);
mysql_select_db(“klasa”);
?>

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:
“CREATE TABLE nazwa (nazwa1 typ1, nazwa2 typ2,
nazwa3 typ3, nazwa4 typ4, PRIMARY KEY(nazwa))”

Możemy teraz utworzyć tabelę według podanego powyżej zapytania.
“CREATE TABLE uczniowie (id char(2) NOT NULL
AUTO_INCREMENT, imie char(20) NOT NULL,
nazwisko char(40) NOT NULL, urodzony date,
PRIMARY KEY(id) )”

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

Wstawianie danych

Aby wstawić jakieś dane do tabeli, należy posłużyć się instrukcją INSERT. Jej schemat jest następujący:
“INSERT INTO nazwa_tabeli (pole1, pole2, pole3)
VALUES (wart_pola1, wart_pola2, wart_pola3)”

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:
“INSERT INTO uczniowie (imie, nazwisko, dziennik)
VALUES (‘Kornel’, ‘Kornelowicz’, ‘1989-12-12′)”

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:
<?
$res = mysql_query(“INSET INTO uczniowie
(imie, nazwisko, dziennik) VALUES (‘Wacek’, ‘Kapusta’, ‘2′)”);
$res = mysql_query(“INSET INTO uczniowie
(imie, nazwisko, dziennik) VALUES (‘Jurek’, ‘Ogórek’, ‘3′)”);
$res = mysql_query(“INSET INTO uczniowie
(imie, nazwisko, dziennik) VALUES (‘Wojtek’, ‘Jarząbek’, ‘4′)”);
$res = mysql_query(“INSET INTO uczniowie
(imie, nazwisko, dziennik) VALUES (‘Sebastian’, ‘Warzywo’, ‘5′)”);
?>
Wybieranie konkretnych rekordów

Aby wybrać z bazy danych jakieś dane, należy posłużyć się operatorem SELECT. Jego składnia to:
SELECT pole1, pole2, pole_n FROM nazwa_tabeli
WHERE warunek1 AND warunek2 ORDER BY
nazwa_klucza rodzaj_sortowania LIMIT
klucz_poczatkowy, ilosc_rekordów

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:
<?
$res = mysql_query(“SELECT * FROM uczniowie
ORDER BY dziennik ASC”);
$res = mysql_query(“SELECT imie FROM uczniowie
WHERE nazwisko = ‘Kapusta’”);
$res = mysql_query(“SELECT * FROM uczniowie
WHERE dziennik > 5″);
//a oto popularny przykład na wyświetlanie porcji
//wyników, np z księgi gości
$res = mysql_query(“SELECT * FROM księga
ORDER BY id ASC LIMIT $start, 10″);
?>

W warunkach do przeszukiwania mogą też wystąpić takie operatory, jak IN lub BETWEEN. Dla przykładu:
<?
$res = mysql_query(“SELECT * FROM uczniowie
WHERE id=5 OR id=7 OR id=10″);
//te dwa przykłady to to samo
$res = mysql_query(“SELECT imie FROM uczniowie
WHERE id IN(5,7,10)”);
$res = mysql_query(“SELECT * FROM uczniowie
WHERE dziennik > 5 AND dziennik < 20″);
//i to też to samo – between określa zakres
$res = mysql_query(“SELECT * FROM uczniowie
WHERE dziennik BETWEEN 5 AND 20″);
?>

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:
<?
$res = mysql_query(“SELECT imie FROM uczniowie
WHERE id NOT IN(5,7,10)”);
//zwraca wszystkich uczniów o id różnym od 5, 7 lub 10
$res = mysql_query(“SELECT * FROM uczniowie
WHERE dziennik NOT BETWEEN 5 AND 20″);
//zwraca wszystkich uczniów o id mniejszym od 5 i większym od 20
?>

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:
<?
$res = mysql_query(“SELECT * FROM uczniowie
WHERE imie LIKE ‘%ek’ AND nazwisko LIKE ‘_apusta’”);
?>
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:
UPDATE nazwa_tabeli SET pole1=’xyz’,
pole2 = ‘abc’, polen = ‘bcd’ WHERE warunki

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:
<?
$res = mysql_query(“UPDATE uczniowie
SET dziennik = 11 WHERE id = 3″);
$res = mysql_query(“UPDATE uczniowie
SET dziennik = 12 WHERE id = 2″);
?>
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:
DELETE FROM nazwa_tabeli WHERE warunki

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:
<?
$res = mysql_query(“DELETE
FROM uczniowie WHERE id = 3″);
$res = mysql_query(“DELETE
FROM uczniowie WHERE id > 15″);
?>
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.

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:
<?
$res = mysql_query(“SELECT * FROM
uczniowie”);
$ile = mysql_num_rows($res);
echo $ile;
?>
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:
<?
$res = mysql_query(“SELECT
* FROM uczniowie”);
while($row =
mysql_fetch_array($res, MYSQL_BOTH)) {
echo ?ID to ?.$row[id].? a jego imie to: ?.$row[1];
}
?>
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:
<?
$res = mysql_query(“INSERT INTO
uczniowie (imie, nazwisko, dziennik)
VALUES (‘Justyna’, ‘Koma’, 13)”);
$id = mysql_insert_id();
echo “ID ostatnio dodanego rekordu to: “.$id;
?>
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).
<?
$res = mysql_query(“SELECT * FROM uczniowie”);
echo mysql_result($res, 0, 1);
//wyświetli imię pierwszego ucznia w tabeli
?>
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:
<?
$res = mysql_query(“SELECT * FROM ucnziowie”);
echo mysql_error();
?>

wyświetli coś w stylu „#1146 – Table ‘baza.ucnziowie’ doesn’t exist”.
Wyświetlanie wyniku zapytania

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

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:
<?
$res = mysql_query(“SELECT * FROM config”);
while($row = mysql_fetch_array($res, MYSQL_BOTH)) {
echo “config_name to “.$row[0].” a jego wartość to:
“.$row[config_value];
}
?>

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

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:
<?
$res = mysql_query(“SELECT * FROM config”);
echo mysql_result($res,3,1); //wyświetli po prostu ‘25′
?>

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.