PHP/Zarządzanie rekordami

Zarządzanie rekordami

Wiemy już, jak utworzyć bazę danych oraz strukturę tabel. Niewątpliwie bez tych operacji nie można zacząć, lecz później podstawowymi operacjami stają się te, dzięki którym możemy zarządzać danymi. Właśnie w tym odcinku dowiemy się, jak dodawać, modyfikować oraz usuwać niepotrzebne rekordy. Na początek proste polecenie, które pozwoli nam na wyświetlenie obecnej zawartości tabeli:

SELECT * FROM `tabela`

Gdzie zamiast tabela wstawiamy nazwę naszej tabeli. Szczegółowy opis tego zapytania poznamy w następnych dwóch odcinkach. Teraz posłuży nam on jedynie do sprawdzania, czy wszystko przebiega poprawnie.

Dodawanie rekordów

Aby dodać rekord do tabeli, należy wysłać zapytanie INSERT. Ma ono generalnie dwie możliwe składnie:

INSERT INTO `tabela` VALUES('Wartość pola 1', 'Wartość pola 2', 'Wartość pola 3');

INSERT INTO `tabela` (`pole1`, `pole2`, `pole3`) VALUES('Wartość pola 1', 'Wartość pola 2', 'Wartość pola 3');

Oba powodują utworzenie nowego rekordu w podanej tabeli, lecz istnieje między nimi pewna różnica. W pierwszym zapytaniu musimy bezwzględnie podać wartości wszystkich pól nowego rekordu, jakie mamy zdefiniowane w strukturze tabeli, w identycznej kolejności. Drugie zapytanie pozwala nam w pierwszym z nawiasów wymienić listę pól, jakie nas interesują i dopiero potem podać ich wartości. W praktyce znacznie częściej używa się właśnie jego, gdyż nie trzeba podawać wartości pól ID, które nadawane są przez bazę automatycznie. Wróćmy zatem do naszej tabeli produkty. Wstawmy do niej kilka rekordów:

INSERT INTO `produkty`
  (`nazwa`, `opis`, `ilosc`, `cena`, `jakosc`) VALUES(
   'Długopisy niebieskie',
   'Długopisy z niebieskim wkładem firmy XXX',
   100,
   2.15,
   3);
INSERT INTO `produkty`
  (`nazwa`, `opis`, `ilosc`, `cena`, `jakosc`) VALUES(
   'Długopisy czerwone',
   'Długopisy z czerwonym wkładem firmy XXX',
   50,
   2.15,
   3);
INSERT INTO `produkty`
  (`nazwa`, `opis`, `ilosc`, `cena`, `jakosc`) VALUES(
   'Zszywacze',
   'Metalowy zszywacz + 100 zszywek.',
   30,
   9.50,
   4);
INSERT INTO `produkty`
  (`nazwa`, `opis`, `ilosc`, `cena`, `jakosc`) VALUES(
   'Karteczki samoprzylepne',
   'Samoprzylepne kartki koloru żółtego 10x10 cm w kompletach po 100 sztuk',
   200,
   3.60,
  2);

Istnieje także możliwość wstawienia kilku rekordów naraz za pomocą jednego zapytania INSERT:

INSERT INTO `produkty` (`nazwa`, `opis`, `ilosc`, `cena`, `jakosc`) VALUES
('Strugaczki', 'Czerwone, do dwóch rozmiarów ołówków', 60, 0.90, 4),
('Gumki do ścierania', 'Gumki do ścierania ołówków firmy ZZZ', 97, 0.50, 3),
('Spinacze do papieru', 'Metalowe spinacze do papieru w kompletach po 50 sztuk.', 68, 0.50, 4);

Tutaj po VALUES podajemy kilka bloków wartości dla kolejnych rekordów, każdy z nich w nawiasach i oddzielony przecinkiem.

Pamiętaj, że wartości tekstowe musimy zawsze podawać w apostrofach. Liczby można podawać zarówno z nimi, jak i bez, lecz podczas programowania apostrofami obejmuje się najczęściej wszystkie wstawiane ze skryptu wartości.

Modyfikowanie rekordów

Czasem zachodzi konieczność zmodyfikowania niektórych informacji. Tu pomocne będzie zapytanie UPDATE. Ma ono bardzo prostą składnię:

UPDATE `tabela` SET `pole1` = 'Nowa wartość', `pole2` = 'Nowa wartość';

Jednak uważaj! Gdybyś wykonał powyższe zapytanie, podstawiając odpowiednie dane, okazałoby się, że zmiany zostały wprowadzone we wszystkich rekordach! Bardzo rzadko jest to pożądana rzecz, ponieważ o wiele częściej chcemy zmodyfikować pewną, konkretną grupę. Do jej uwzględnienia użyjemy nowej klauzuli: WHERE wyrażenie umieszczanej po liście pól do podmiany. Wyrażenie jest dowolnym poprawnym wyrażeniem języka SQL, a układa się je podobnie, jak te w PHP. Szczegółowe informacje o budowaniu wyrażeń poznamy w następnym odcinku, teraz ograniczymy się do kilku prostych sztuczek.

Na początek zmienimy opis i cenę produktu o ID 1:

UPDATE `produkty` SET `opis` = 'Długopisy niebieskie firmy YYY', `cena` = '2.45' WHERE `id` = '1';

Teraz coś trudniejszego: nasz sklep planuje podwyżkę cen najlepszych produktów (jakość 4) o 50 groszy. Wiele osób próbuje robić to, pobierając ceny wszystkich interesujących je produktów i zmieniając je setkami zapytań UPDATE, po jednym dla jednego rekordu. Jest to bardzo niepraktyczne, ponieważ język SQL jest na tyle zaawansowanym narzędziem, że radzi sobie z tym bez trudu:

UPDATE `produkty` SET `cena` = (`cena` + 0.5) WHERE `jakosc` = 4;

Powinniśmy teraz ujrzeć informację, że zmodyfikowane zostały trzy rekordy.

Usuwanie rekordów

Niepotrzebne rekordy kasujemy zapytaniem DELETE. Podobnie, jak w poprzednim przypadku, należy zastosować klauzulę WHERE, aby określić, które z nich chcemy usunąć. Inaczej możemy pożegnać się z całą zawartością tabeli.

DELETE FROM `produkty` WHERE `id` = 3;

Zdarza się, że tabelę trzeba rzeczywiście wyczyścić, np. z danych testowych, aby aplikacja mogła być używana na normalnym serwerze. Jednak wtedy nie powinno się wykorzystywać polecenia DELETE FROM `produkty`. Do tego celu służy specjalne zapytanie:

TRUNCATE `produkty`;

Różnica pomiędzy pierwszym i drugim jest podobna, jak pomiędzy zaznaczeniem wszystkich plików na dysku i kliknięciu "Delete", a uruchomieniem jego formatowania.

Wykonując wiele zapytań DELETE oraz INSERT zauważysz, że pozostają Ci luki w numeracji. Spróbuj dodać teraz jakiś rekord. Jeżeli wykonałeś wcześniej zapytanie kasujące ten o ID 3, MySQL pozostawi tam lukę, nadając nowemu rekordowi następny w kolejności ID - 4. Prawdopodobnie z przyczyn estetycznych niektórym nowym programistom to zachowanie przeszkadza, lecz jest to zupełnie błędne podejście do problemu. Jak wspomnieliśmy bowiem, MySQL jest relacyjną bazą danych, w której rekordy z jednej tabeli mogą być połączone odpowiednimi relacjami z rekordami w drugiej. Wyobraźmy sobie więc, co by się stało, gdybyśmy skasowali jakąś kategorię np. newsów w naszym serwisie, a potem dodali nową i okazało się, że MySQL zaliczył w jej poczet wszystkie newsy z tej usuniętej, gdyż nowy rekord zajął pustą lukę w numeracji. Działanie takie stwarza poważne zagrożenie synchronizacji bazy i wprowadza w nią element losowości. Dlatego zaufaj twórcom serwerów DB, oni naprawdę znają się na rzeczy i nie podejmuj zbędnych prób zmieniania na gorsze tego, co wyraźnie służy zarówno tobie, jak i twojej bazie.

Zamiana rekordów

Bardzo przydatną operacją jest automatyczne podmienianie rekordów. Polega ono na tym, że jeżeli wstawiamy rekord A do tabeli, w której znajduje się już rekord B o podobnym kluczu, jest on automatycznie nadpisywany przez system. MySQL posiada dwa zapytania, które są pomocne przy podmienianiu rekordów, jednak zanim się do nich dobierzemy, musimy utworzyć sobie nową tabelę.

Aby MySQL mógł zdecydować, czy rekord należy nadpisać, musi wiedzieć, które pola tabeli przechowują wartości unikalne, czyli takie, że nie można znaleźć dwóch rekordów o identycznej wartości w tym polu. Wiemy już, że taką właściwość powinno mieć pole id, i rzeczywiście - informujemy o tym bazę, tworząc dla niego tzw. klucz główny (PRIMARY KEY). Dba on o to, aby wartości się nie powtarzały. Jednak klucz ten można nałożyć tylko na jedno pole naraz, a operacja REPLACE przy rekordach mających z definicji nie tylko unikalne, ale też nadawane automatycznie ID, zbyt dużego sensu nie ma. Dlatego istnieje indeks UNIQUE, który może być nałożony na dowolnie dużo pól i także sprawi, że zyskają one tę właściwość. Aby pokazać to w praktyce, utwórzmy tabelę dla elektronicznego słownika (w wersji uproszczonej, oczywiście):

CREATE TABLE `slownik` (
`id` MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`haslo` VARCHAR(40) NOT NULL,
`znaczenie` VARCHAR(255) NOT NULL,
  UNIQUE(
   `haslo` 
  )
) ENGINE = MYISAM;

Mamy tu trzy pola: id, haslo oraz znaczenie. ID służy jedynie celom administracyjnym (wyszukiwanie względem liczby jest zawsze szybsze, niż względem tekstu). Dla użytkownika naszego słownika najważniejsze będzie jednak pole haslo, które także powinno mieć wartości unikalne. Indeks UNIQUE tworzymy tak, jak na przykładzie: po liście pól wstawiamy słowo kluczowe UNIQUE, w którym w nawiasie wymieniamy pola posiadające taką właściwość.

Jeżeli jesteśmy administratorami słownika, mogącymi dodawać do niego nowe hasła, unikalność obsługiwana przez MySQL ma dla nas duże znaczenie. Kiedy będzie w nim bowiem już dużo słów, ze zwykłej pomyłki możemy spróbować dodać po raz drugi to samo. Tradycyjne polecenie INSERT zareagowałoby błędem powiadamiającym, że próbujemy utworzyć rekord, w którym powtarza się unikalna wartość. Dlatego możemy zastosować inne zapytanie: REPLACE. Wstawiamy nim pierwsze trzy hasła:

REPLACE slownik (haslo, znaczenie) VALUES('tree', 'drzewo');
REPLACE slownik (haslo, znaczenie) VALUES('house', 'dom (budynek)');
REPLACE slownik (haslo, znaczenie) VALUES('sign', 'znak');

W tym momencie działają one identycznie, jak znane nam zapytanie INSERT (mają nawet podobną składnię, chociaż dozwolone jest stosowanie także składni polecenia UPDATE). Możemy zapytać się, jaka jest zawartość tabeli i pokażą nam się wszystkie trzy wymienione rekordy. Spróbujmy teraz po raz drugi dodać istniejące hasło (np. tree):

REPLACE slownik (haslo, znaczenie) VALUES('tree', 'drzewo, drzewko');

Query OK, 2 rows affected (0.00 sec)

Zwróć uwagę, jaki komunikat kontrolny pokazał MySQL: zmodyfikowane zostały dwa rekordy. Wyświetlmy zawartość tabeli: okazuje się, że pierwotny rekord tree (z ID równym 1) został skasowany, a na jego miejscu pojawił się nowy, któremu serwer nadał ID 4. Mamy zatem sformułowaną zasadę działania polecenia REPLACE:

1. Spróbuj dodać nowy rekord.
2. Jeśli nie powiedzie się z powodu duplikacji unikalnej wartości, usuń stary rekord powodujący kolizję.
3. I ponownie dodaj nowy rekord.

REPLACE nie jest jedynym poleceniem realizującym zamianę rekordów. Od wersji 4.1.0 MySQL obsługuje także rozszerzenie zapytania INSERT o element ON DUPLICATE KEY UPDATE, zgodne ze standardem ANSI SQL. Dzięki niemu nie trzeba kasować kolidującego rekordu, lecz jedynie nadpisać jego wartości nowymi. Dodajmy ponownie jakieś istniejące hasło:

INSERT INTO slownik (haslo, znaczenie) VALUES('house',  'dom (budynek), rodzaj obiektu mieszkalnego.')
ON DUPLICATE KEY UPDATE znaczenie=VALUES(znaczenie);

Query OK, 2 rows affected (0.02 sec)

Pierwsza część tego zapytania to znany nam już dobrze INSERT. Próbuje on wstawić nowy rekord. Kiedy jednak zajdzie kolizja, do akcji wkracza nowa część: ON DUPLICATE KEY UPDATE, w której możemy zdefiniować sposób nadpisania starego rekordu według składni nazwa_pola = nowa_wartosc, nazwa_pola = nowa_wartosc, .... Możemy tutaj ustawiać statyczne wartości, np. pole = 1, lub też odwołać się do wartości pola, która miała być wstawiona poleceniem INSERT. Służy do tego funkcja VALUES(nazwa_pola). Wykorzystaliśmy ją w powyższym przykładzie, aby wprowadzić nową definicję hasła na miejsce starej.

Dzięki automatycznej możliwości nadpisywania rekordów z unikalnymi kluczami, nasza baza jest wygodniejsza. Jeżeli jednak tworzona aplikacja ma pracować z różnymi systemami DB, musimy sprawdzić, czy obsługują one podaną operację i w razie potrzeby napisać odpowiedni emulator.

Błędy w zapytaniach

Także w języku SQL można popełnić błędy, które uniemożliwią wykonanie zapytania. Każdy serwer DB raportuje je inaczej. Ponieważ sztandarową bazą w tym podręczniku jest MySQL, pokażemy sposoby debugowania zapytań właśnie dla niego.

Zacznijmy od błędów składni. Wykonaj następujące zapytanie:

UPDATE `produkty` SET `opis` = 'Długopisy niebieskie firmy YYY' `cena` = '2.45' WHERE `id` = '1';

Zawiera ono celowo wprowadzony błąd, który powoduje pokazanie się komunikatu

You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'cena` = '2.45' WHERE `id` = '1'' at line 1

Komunikat pokazał nam użyteczną informację o miejscu lokalizacji problemu. Według niego coś jest nie tak w okolicy ciągu cena` = '2.45' WHERE `id` = '1'. Niepisana zasada mówi, że najczęściej błąd popełniliśmy bezpośrednio przed nim. Zobaczmy więc, co znajduje się w zapytaniu przed odwołaniem do pola "cena". Okazuje się, że brakuje przecinka oddzielającego je od porzedniej definicji nowej wartości. Kiedy go tam umieścimy, wszystko zaczyna prawidłowo działać.

Inny rodzaj problemu może powstać przy pracy z polami "PRIMARY KEY" oraz "UNIQUE" (o nich dalej). Oba te atrybuty nadają polu właściwość unikalności, czyli nie mogą istnieć dwa rekordy o takich samych wartościach w tym miejscu. Przykładowo wywołajmy takie zapytanie INSERT:

INSERT INTO `produkty`
  VALUES(
   '3',
   'Długopisy niebieskie',
   'Długopisy z niebieskim wkładem firmy XXX',
   100,
   2.15,
   3);

Jest w nim wymieniony ID produktu. Jeżeli wykonywałeś wszystkie zapytania w tym odcinku, prawdopodobnie nic się teraz nie stanie, ponieważ rekord o ID 3 niedawno skasowaliśmy. W takim wypadku wykonaj je jeszcze raz, a baza pokaże ci wtedy

Duplicate entry '3' for key 1

Oznacza on tyle, że w kluczu pierwszym (w naszym przypadku jest to pole "id") próbujemy po raz drugi umieścić wartość 3, co jest niedozwolone.