Opcje dwuwartościowe - przechowywanie
Często nasze tabele w bazie danych zawierają pola, która mogą przyjmować dwie wartości (dwa stany): 0 i 1. Na przykład tabela z użytkownikami zawiera pole ZABLOKOWANY, które mówi czy użytkownik jest zablokowany czy nie. Jeśli jest zablokowany to przyjmuje wartość 1 a jeśli nie jest - wartość 0. Wszystko ładnie pięknie gdy są to pojedyncze pola i nie zajmują dużo miejsca. Ale co w przypadku gdy nasza tabela zawiera kilka, kilkanaście lub nawet kilkadziesiąt opcji opisu (np. tabela z ofertami, która zawiera pola OPCJA_1, OPCJA_2, OPCJA_3, .... OPCJA_N) ? Czy mamy wówczas też tworzyć kilkadziesiąt pól na każdą z opcji o możliwych wartościach 0 i 1? Nie - tutaj z pomocą przyjdzie nam system dwójkowy (binarny) oraz operacje bitowe.
Całość przedstawię na podanym powyżej przykładzie: tabela z jakimiś ofertami. Tabela zawiera osiem pól. Każde z pól będzie typu TINYINT czyli zajmować będzie jeden bajt. Pola mogą mieć wartości 1 lub 0 oznaczające kolejno posiadanie danej opcji przez ofertę lub nie.
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
`ID` int unsigned NOT NULL auto_increment,
`NAZWA` varchar(32) NOT NULL,
`OPCJA_1` tinyint unsigned NOT NULL default 0,
`OPCJA_2` tinyint unsigned NOT NULL default 0,
`OPCJA_3` tinyint unsigned NOT NULL default 0,
`OPCJA_4` tinyint unsigned NOT NULL default 0,
`OPCJA_5` tinyint unsigned NOT NULL default 0,
`OPCJA_6` tinyint unsigned NOT NULL default 0,
`OPCJA_7` tinyint unsigned NOT NULL default 0,
`OPCJA_8` tinyint unsigned NOT NULL default 0,
PRIMARY KEY (`ID`),
KEY `OPCJA_1` (`OPCJA_1`),
KEY `OPCJA_2` (`OPCJA_2`),
KEY `OPCJA_3` (`OPCJA_3`),
KEY `OPCJA_4` (`OPCJA_4`),
KEY `OPCJA_5` (`OPCJA_5`),
KEY `OPCJA_6` (`OPCJA_6`),
KEY `OPCJA_7` (`OPCJA_7`),
KEY `OPCJA_8` (`OPCJA_8`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Jak widać tabela już wizualnie jest duża. Stworzyliśmy 8 pól. Każde z pól zajmuje jeden bajt. Dodatkowo jeśli tych opcji chcemy używać przy wyszukiwaniu to dobrze też jest założyć na nie indeksy. Także mamy już 8 bajtów na opis opcji jednego rekordu plus indeksy
Ale możemy to znaczącą zmniejszyć. Wystarczy znać podstawy systemu dwójkowego oraz operacji bitowych. Nie będę tu tego opisywał, przedstawię jedynie podstawy do zrozumienia na czym cały myk polega.
Teoria
Jak zapewne wiecie jeden bajt zawiera 8 bitów. Bit to po prostu wartość 0 lub 1. Czyli skoro jeden bajt zawiera osiem bitów to na jednym bajcie możemy zapisać do ośmiu informacji zerojedynkowych. Teraz wystarczy "wirtualnie" określić, że ustawienie w danej pozycji bita stanowi wybór jednej z opcji i mamy problem z głowy.
2
3
4
5
6
7
8
9
10
11
0 0 0 0 0 0 0 0 0 żadna opcja
0 0 0 0 0 0 0 1 1 opcja 1
0 0 0 0 0 0 1 0 2 opcja 2
0 0 0 0 0 1 0 0 4 opcja 3
0 0 0 0 1 0 0 0 8 opcja 4
0 0 0 1 0 0 0 0 16 opcja 5
0 0 1 0 0 0 0 0 32 opcja 6
0 1 0 0 0 0 0 0 64 opcja 7
1 0 0 0 0 0 0 0 128 opcja 8
Ustawiając wartości 1 na różnych bitach otrzymujemy inne wartości dzisiętne.
2
3
4
5
6
7
0 0 0 0 0 0 0 0 0 żadna opcja
0 0 0 0 0 1 0 1 5 opcja 1 + opcja 3
1 0 0 0 0 1 0 1 133 opcja 1 + opcja 3 + opcja 8
0 0 1 1 0 0 0 0 48 opcja 5 + opcja 6
..............
Tyle teorii "dwójkowej". Teraz trochę teorii w php. Zapoznajcie się proszę z operatorami bitowymi
Chcąc w php dodać bitowo do siebie dwie liczby (ustawić wartość, która odpowiadać będzie zaznaczeniu dwóch naszych opcji) należy wykonać taki kod
2
3
4
5
6
//liczba 4 odpowiada opcji 3 0 0 0 0 0 1 0 0
//liczba 16 odpowiada opcji 5 0 0 0 1 0 0 0 0
$opcje = 4 | 16; //20
//liczba 20 odpowiada opcji 3 oraz opcji 5 0 0 0 1 0 1 0 0
?>
Ok, umiemy już dodawać opcje do siebie. Ale jak sprawdzić w wyszukiwaniu czy dana opcja jest zaznaczona mając podaną liczbę określającą zaznaczone opcje? Posłużymy się operatorem &
2
3
4
5
6
7
8
//$opcje = 20; opcja 3 oraz opcja 5
//sprawdzamy czy w zmiennej $opcje znajduje się opcja 3 (wartość 4)
//jeśli wynikem sprawdzania będzie liczba której szukamy, to znaczy że szukana opcja jest w naszym zbiorze
//jeśli wynikiem będzie inna liczba wówczas opcji nie ma
$res = $opcje & 4; //wynikiem jest 4 - opcja 3 więc tu jest
$res = $opcje & 8; //wynikiem jest 0 - nie ma tu więc opcji 4
?>
Możemy też jednocześnie poszukiwać kilku opcji
2
3
4
5
6
7
8
//szukamy czy występują jednocześnie opcje 1 (1) oraz 3 (4) czyli szukamy liczby 5 (0 0 0 0 0 1 0 1 )
//1 0 0 0 0 1 0 1 133 opcja 1 + opcja 3 + opcja 8
$res = 133 & 5;//wynikem jest 5 czyli zawarte są tu opcje 1 oraz 3
//szukamy czy występują jednocześnie opcje 2 (2) oraz 3 (4) czyli szukamy liczby 6 (0 0 0 0 0 1 1 0 )
$res = 133 & 6;//wynikem jest 4 czyli liczba różna od szukanej. Nie ma więc tutaj jednocześnie opcji 2 oraz 3
?>
Powyższe obliczenia można również wykonywać bezpośrednio na bazie danych - przyda nam się to w praktycznym pisaniu wyszukiwarki.
Zauważcie, że jeśli szukamy tylko jednej opcji, to w przypadku gdy jej nie znajdziemy, wynikiem zawsze będzie 0. W przypadku poszukiwania kilku opcji, wynikiem negatywnym może być liczba większa od 0 ale inna niż ta, której szukaliśmy. Właściwość ta nam się przyda przy zaznaczaniu checkboxów, ale o tym za chwilę.
Praktyka
Tak więc nasze 8 pól opcji zamieniamy na jedno pole typu TINYINT. Typ ten zajmuje jeden bajt czyli osiem bitów. Jeśli byście chcieli przechowywać do 16 opcji, musicie użyć typu SMALLINT (2 bajty - 16 bitów). Zwiększając typy możecie przechowywać więcej opcji - ale to już chyba hardcore robić kilkadziesiąt opcji dla jednej tabeli ;)
2
3
4
5
6
7
8
`ID` int unsigned NOT NULL auto_increment,
`NAZWA` varchar(32) NOT NULL,
`OPCJE` tinyint unsigned NOT NULL default 0,
PRIMARY KEY (`ID`),
KEY `OPCJE` (`OPCJE`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Porównując tę tabelę do naszej pierwszej od razu widać wizualnie różnicę. Pamięciowo też już jest o wiele lepiej.
Zajmiemy się teraz reprezentacją opcji. Opcje możemy przedstawić jako checkboxy
2
3
4
5
6
<input type="checkbox" name="opcja[]" value="2" />Opcja2
<input type="checkbox" name="opcja[]" value="4" />Opcja3
....
<input type="checkbox" name="opcja[]" value="128" />Opcja8
Zauważcie, iż użyłem nazwy checkboxa z [] - określa to nazwę tablicową. Dzięki temu po zaznaczeniu kilku opcji, formularz wyśle na serwer wszystkie zaznaczone opcje jako tablica wartości. Tablicę tę będzie można odebrać w php z $_POST['opcja'].
Jeśli pobierzemy z bazy wartość pola OPCJE i chcemy w formularzu zaznaczyć te opcje, które są zawarte w tym polu, korzystamy ponownie z operatora &
2
3
4
5
6
7
8
9
10
11
12
13
14
$opcje = 20; //wartość pobrana z bazy z pola OPCJE
//poniżej zostaną zaznaczone opcja 3 oraz opcja 5
echo '<input type="checkbox" name="opcja[]" value="1" '.($opcje & 1 ? 'checked="checked"' : '').'/> Opcja 1';
echo '<input type="checkbox" name="opcja[]" value="2" '.($opcje & 2 ? 'checked="checked"' : '').'/> Opcja 2';
echo '<input type="checkbox" name="opcja[]" value="4" '.($opcje & 4 ? 'checked="checked"' : '').'/> Opcja 3';
echo '<input type="checkbox" name="opcja[]" value="8" '.($opcje & 8 ? 'checked="checked"' : '').'/> Opcja 4';
echo '<input type="checkbox" name="opcja[]" value="16" '.($opcje & 16 ? 'checked="checked"' : '').'/> Opcja 5';
echo '<input type="checkbox" name="opcja[]" value="32" '.($opcje & 32 ? 'checked="checked"' : '').'/> Opcja 6';
echo '<input type="checkbox" name="opcja[]" value="64" '.($opcje & 64 ? 'checked="checked"' : '').'/> Opcja 7';
echo '<input type="checkbox" name="opcja[]" value="128" '.($opcje & 128 ? 'checked="checked"' : '').'/> Opcja 8';
?>
Teraz jeśli chcemy zapisać zaznaczone checkboxy (opcje) do bazy to musimy użyć operatora |
2
3
4
5
6
7
8
9
10
11
12
13
if (!empty($_POST)){ //jeśli wysłano formularz
$opcje = 0;//zaczynamy od nie wybranej żadnej opcji
if (!empty($_POST['opcja'])){ //jeśli wybraliśmy jakieś checkboxy
foreach ($_POST['opcja'] as $op){ //przelatujemy po wartościach wybranych checkboxów
$op = (int)$op; //rzutujemy wartość na inta by przypadkiem ktoś nam "kuku" nie zrobił
//dodajemy bitowo opcje
$opcje |= $op; //zapis ten jest równoważny do $opcje = $opcje | $op;
}
}
//teraz w zmiennej $opcje mamy zaznaczone opcje i możemy tę wartość zapisać do bazy
}
?>
Mamy już wczytywanie i zapisywanie opcji. Pozostało jeszcze zrobić nam wyszukiwanie. Wyszukiwarka również będzie zawierała formularz z checkboxami określającymi opcje. Wyszukanie opcji będzie podobne do ich zapisania - też trzeba najpierw zsumować zaznaczone opcje, a następnie przy pomocy operatora & wyszukać oferty, które je zawierają.
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
$opcje = 0; //na początku nic nie szukamy
//w wyszukiwarkach zazwyczaj korzysta się z get zamiast post
if (!empty($_GET['opcja'])){ //jeśli wybraliśmy jakieś checkboxy to ich będziemy szukać
foreach ($_GET['opcja'] as $op){ //przelatujemy po wartościach wybranych checkboxów
$op = (int)$op; //rzutujemy wartość na inta by przypadkiem ktoś nam "kuku" nie zrobił
//dodajemy bitowo opcje
$opcje |= $op; //zapis ten jest rownoważny do $opcje = $opcje | $op;
}
}
//tworzymy podstawowe zapytanie do pobrania ofert
$sql = 'select * from oferta';
//jeśli wybraliśmy jakieś opcje, to jeszcze bedziemy ich szukac.
//dodajemy więc WHERE do zapytania
if (!empty($opcje))
$sql.=" where OPCJE & $opcje = $opcje"; //jak już pisałem wcześniej, jeśli szukamy opcji to wynik musi nam zwrócić szukaną liczbę
//wykonujemy zapytanie
$res = mysql_query($sql);
//i tutaj już sobie wyświetlamy znalezione wyniki
?>
I to już wszystko. Na pierwszy rzut oka może to się wydawać skomplikowane - wystarczy jednak przerobić to ze dwa razy i powinno już być lżej. A znajomość tej metody może Wam się przydać - ja z niej korzystam dość często.
Wiem, że kod można było napisać bardziej uniwersalnie, np. poprzez dodanie pętli zamiast generować wszystkie opcje ręcznie. Nie chciałem jednak wprowadzać dodatkowych "bajerów" by nie zaciemniać istoty problemu.
W dziale download znajdziecie paczkę, która zawiera przykładową bazę danych oraz kody do zapisywania i wyszukiwania opcji. Przeanalizujcie je dokładnie - pomogą Wam utrwalić przedstawione tu informacje.
I na koniec mały "bonus" dla tych wszystkich, którzy nie lubią 0 i 1 lecz wolą bardziej wartości tekstowe. Mysql udostępnia specjalny typ danych, który umożliwia w opisanej tu metodzie operować na tekstach: SET
