Tworzenie tabeli w bazie danych

Przygotowanie zajęć lekcyjnych
z informatyki dotyczących zastosowania
MySQL i PHP w tworzeniu
zaawansowanych stron internetowych
Autor:
Tadeusz Koziemczyk
kontakt: [email protected]
MySQL i PHP w tworzeniu zaawansowanych stron internetowych na lekcjach informatyki
Wstęp
Jeśli prowadzący zajęcia zamierza w sposób praktyczny zapoznać uczniów
z zaawansowanymi technikami tworzenia stron internetowych to z pewnością będzie musiał
poświęcić około 14 godzin lekcyjnych na języki SQL i PHP. Należy zwrócić uwagę, że
przygotowanie takich lekcji od strony oprogramowania na komputerach uczniowskich,
w porównaniu z innymi zagadnieniami, wymaga większego zaangażowania ze strony
prowadzącego zajęcia. Również od strony dydaktycznej takie zajęcia nie są łatwe do
przygotowania i poprowadzenia. Z pewnością łatwiej znaleźć właściwą koncepcje
przeprowadzenia tych tematów na kółku informatycznym niż na zwykłych lekcjach.
W korzystniejszej sytuacji są nauczyciele, którzy wcześniej mogli, w ramach przydziałowych
godzin na lekcje informatyki, realizować SQL w Access’ie i składnię języka C. W takim
przypadku zaprezentowane techniki posługiwania się SQL i PHP nie będą dużym
zaskoczeniem, a prowadzący zajęcia będzie miał możliwość nawiązania do wcześniej
realizowanych tematów.
Poniższy artykuł zawiera informacje o przygotowaniu środowiska na komputerach
uczniowskich oraz merytoryczny materiał dotyczący podstawowych zapytań MySQL.
Do każdej części dodane zostały propozycje ćwiczeń sprawdzających opanowanie materiału.
Artykuł nie przedstawia treści związanych z posługiwaniem się językiem PHP, zakładając, że
język ten został opanowany przez uczniów w sposób podstawowy. Natomiast obecnie
prowadzący zajęcia ma za zadanie zachęcić uczniów do zastosowania tego języka do
komunikacji z bazą danych oraz przedstawić możliwości uzyskania interaktywności stron w
oparciu o PHP i MySQL.
2
MySQL i PHP w tworzeniu zaawansowanych stron internetowych na lekcjach informatyki
Przygotowanie środowiska pracy na stacjach uczniowskich
Środowisko pracy do ćwiczeń z zaawansowanych technik stron internetowych powinno
składać się:
o z serwera WWW Apache, do pobrania z http://httpd.apache.org (2.0.49). Opis
instalacji znajdują się w oficjalnej dokumentacji, którą można znaleźć pod adresem
http://httpd.apache.org/docs-2.0/platform/windows.html.
o z Interpretera PHP, do pobrania z http://php.net (4.3.4)
o z serwer baz danych MySQL http://mysql.com (4.0.18)
o z Edytora kodu PHP (i innych) Notepad++, można pobrać ze strony http://notepadplus.sourceforge.net
Ostatnia opcja nie jest konieczna, stanowi tylko ułatwienie pisania składni języka PHP.
Oczywiście podana wyżej propozycja może być w niektórych przypadkach uciążliwa
i pracochłonna w konfiguracji. Dlatego też można przygotować środowisko pracy w prostszy
sposób. Zainstalować oprogramowanie KRASNAL Serv 2.7 ze strony www.krasnal.tk.
W skład KRASNAL Serv 2.7 wchodzą:
o
o
o
o
o
o
o
o
o
o
o
Apache 1.3.31
PHP 4.3.9 + SQLite
PHP 5.0.2
MySQL 3.23.58
ActivePerl 5.6.1.628
phpMyAdmin 2.6.2 pl2
Zend Optimizer 2.0.3
CesarFTP 0.99g
WebAlizer 2.1
Free SMTP Server
SQLiteManager 1.0.1
Wszystko to uzyskamy przy pomocy jednej instalacji, nie jest wymagana konfiguracja (tylko
ustalenie katalogu na strony www).
Kolejna propozycja to VertrigoServ, można pobrać ze strony http://vertrigo.sourceforge.net/
Komponenty VertrigoServ:
o
o
o
o
o
o
o
Apache 2.0.55
PHP 5.1.2
MySQL 5.0.19
SQLite 3.3.4
PhpMyAdmin 2.8.0.1
ZendOptimizer 2.6.2
SQLiteManager 1.1.3
Tak jak i w poprzedniej propozycji wszystko to uzyskamy przy pomocy jednej instalacji, nie
jest wymagana konfiguracja.
3
MySQL i PHP w tworzeniu zaawansowanych stron internetowych na lekcjach informatyki
Ćwiczenie 1.
Jakie środowisko musi być zainstalowane na twoim komputerze oraz jakie procesy powinny
być uruchomione aby mogły poprawnie funkcjonować strony internetowe wykonane w
oparciu o technologię PHP i MySQL?
SQL - Structured Query Language
MySQL jest to darmowy systemem obsługi baz danych, licencja GPL (General Public
License). Jest wykorzystywany do stworzenia serwera obsługującego bazy danych. MySQL
jest jednym z najpopularniejszych interfejsów języka SQL
Do połączenia się z serwerem baz danych należy użyć odpowiedniego oprogramowania
tzw. klienta lub języka skryptowego (umieszczanego na serwerach WWW), który posiada
wbudowaną obsługę baz danych. Programem klienckim może być oprogramowanie
dostarczane razem z pakietem MySQL. Można oczywiście korzystać z bazy danych przez
połączenie wywoływane odpowiednim skryptem.
Zalecanym narzędziem do obsługi SQL, jest panel administracyjny do baz danych –
phpMyAdmin (oczywiście jeśli usługodawca nam go oferuje). Połączenie z bazą MySQL
można uzyskać z poziomu skryptów PHP lub Perl oraz kompilowanych CGI (możliwości te
zależą od oprogramowania na serwerze).
Aby uzyskać połączenie z serwerem baz danych należy znać:
- adres domenowy lub adres IP
- nazwę użytkownika
- oraz hasło
Ćwiczenie 2. Jakie warunki muszą być spełnione aby można było wykonywać operacje w
bazie danych?
Ćwiczenie 3. Wymień podstawowe narzędzie do obsługi bazy SQL z poziomu przeglądarki
internetowej.
Łączenie się z bazą danych
Aby dokonać oczekiwanych operacji w bazie danych należy: połączyć się z bazą danych,
wysłać do niej informacje. Informacje przesłane do bazy danych są w postaci zapytań. Aby
nawiązać połączenie z serwerem MySQL należy użyć odpowiedniej funkcji języka PHP,
wygląda to następująco:
$db = mysql_connect ("adres", "użytkownik", "hasło"); gdzie adres, użytkownik, hasło
są danymi wartościami otrzymanymi od usługodawcy hostingu.
Jeśli praca z bazą danych jest zakończona należy użyć funkcji: mysql_close ($db);
Ćwiczenie 4. Napisz skrypt PHP (config.php), który umożliwia połączenie z serwerem
MySQL. W jaki sposób należy posłużyć się tym skryptem (podaj listę kroków)?
4
MySQL i PHP w tworzeniu zaawansowanych stron internetowych na lekcjach informatyki
Tworzenie nowej bazy i wybieranie bazy
Do stworzenia nowej bazy używamy funkcji mysql_create_db(nazwa_bazy). Jeśli chcemy
założyć bazę o nazwie „baza1” używamy polecenia mysql_create_db("baza1");. Po
prawidłowym podłączeniu do serwera MySQL należy wskazać bazę, na której chce się
pracować (ilość możliwych baz do utworzenia zależy od ustalenia z usługodawcą hostingu).
Jeśli będziemy wykonywać operacje na określonej bazie musimy ją wybrać, używając funkcji
mysql_select_db(nazwa_bazy);.
Dla
„bazy1”
będzie
następująca
składnia:
mysql_select_db("baza1");. Baza1 jest od tego momentu dostępna, jeśli została wcześniej
utworzona. Aby wykonywać podstawowe operacje w bazie1 tworzymy zapytania. Tworzy się
je stosując funkcję mysql_query(zapytanie_SQL);. W praktyce wygląda to następująco:
$zaptanie="miejsce na twoje zapytanie SQL";
$zrealizuj=mysql_query($zapytanie);
Funkcja mysql_query zwróci wartość TRUE, jeśli operacja zostanie zrealizowana, w
przeciwnym wypadku - zwróci wartość FALSE.
Ćwiczenie 5. Napisz skrypt PHP, który tworzy bazę danych o nazwie absolwenci.
Tworzenie tabeli w bazie danych
Tabele służą do przechowywania informacji. W celu utworzenia tabeli należy zaplanować jej
pola określające jakie dane będzie zawierał pojedynczy rekord. Aby utworzyć tabelę
przechowującą dane o studentach projektujemy pola: imię, nazwisko, data_urodzenia.
Przykładowy rekord będzie wyglądał następująco: 'Jan', 'Kowalski', '1968-09-10'.
Podane dane są różnego typu. imię i nazwisko to dane tekstowe, data_urodzenia to pole
zawierające datę. Oczywiście można projektować pola reprezentuje dane liczbowe. Polecenie
SQL CREATE TABLE służące do utworzenia tabeli jest następujące:
CREATE TABLE nazwa_tabeli struktura_tabeli;
nazwa_tabeli - oznacza nazwę definiowanej tabeli, struktura_tabeli - zawiera dokładną
specyfikację poszczególnych pól tabeli. Przykładowe polecenie SQL dla utworzenia tabeli
studenci
CREATE TABLE studenci (
imie VARCHAR(20),
nazwisko VARCHAR(40),
data_urodzenia DATE
);
Jak widać dokładny schematu składni SQL dla tego polecenia można przedstawić w
następujący sposób:
CREATE TABLE nazwa_tabeli (
nazwa1 typ1,
nazwa2 typ2,
5
MySQL i PHP w tworzeniu zaawansowanych stron internetowych na lekcjach informatyki
....................
nazwa typ,
PRIMARY KEY(nazwa_id)
);
PRIMARY KEY oznacza, że pole nazwa_id stanowi klucz główny, a klucz główny nie może
się powtarzać
Oto kilka typów pól:
TINYINT - 1-bajtowe pole, przechowujące wartości całkowite bez znaku z przedziału od 0 do
255 lub ze znakiem z przedziału od -127 do 127
FLOAT(dokładność) - Liczba zmiennoprzecinkowa z precyzją wyrażaną liczbą bajtów. Dwie
wartości akceptowane jako precyzja to 4 i 8. Użycie 4 tworzy liczbę zmiennoprzecinkową o
pojedynczej precyzji, natomiast 8 - liczbę zmiennoprzecinkową o podwójnej precyzji.
Szczegóły dotyczące tych dwóch typów danych opisują pola FLOAT i DOUBLE
FLOAT - 4-bajtowa liczba zmiennoprzecinkowa z zakresu od -1.402823466E+38 do 1.175494351E-38, 0 i 1.175494351E-38 do 3.402823466E+38
DOUBLE - 8-bajtowa liczba zmiennoprzecinkowa przechowująca wartości z zakresu od 1.7976931348623157E+308 do -2.2250738585072014E-308, 0, i 2.2250738585072014E-308
do 1.7976931348623157E+308
DECIMAL - Pole zmiennoprzecinkowe o takiej samej pojemności jak DOUBLE. Liczba,
zamiast zostać zredukowana do postaci szesnastkowej, pamiętana jest w formacie znakowym
DATE - Data (bez czasu), wyświetlana w formacie RRRR-MM-DD. Akceptuje daty w
różnych formatach z zakresu od 1000-01-01 do 9999-12-31
TIME - Czas mieszczący się w przedziale od -838:59:59 do 838:59:59
YEAR - Rok. Dozwolone wartości to przedział od 1901 do 2155 oraz wartość 0000. MySQL
wyświetla wartość typu YEAR w formacie RRRR
CHAR Pole znakowe o stałej długości z zakresu od 1 do 255 bajtów. Po wstawieniu wartości
puste miejsca pola CHAR są uzupełniane z prawej strony spacjami
VARCHAR - Pole znakowe o zmiennej długości z zakresu od 1 do 255 bajtów. Zajmowany
jest jedynie taki obszar pamięci, jakiego wymaga wartość wstawiona w to pole
BLOB, TEXT - Kolumna binarna lub tekstowa o rozmiarze nie przekraczającym 65 535
bajtów
Ćwiczenie 6. W jaki sposób z poziomu PHP utworzyć w bazie danych absolwenci
odpowiednią tabelę byli_uczniowie przechowującą dane o absolwentach? Jak uruchomić taki
skrypt?
6
MySQL i PHP w tworzeniu zaawansowanych stron internetowych na lekcjach informatyki
Podstawowe polecenia SQL dotyczące tabeli
Polecenie INSERT. wstawia pojedynczy rekord do tabeli.
INSERT INTO nazwa_tabeli VALUES (
wartosc_pola1,
wartosc_pola2,
wartosc_pola3,
.......................
wartosc_pola
);
Aby wstawić rekord do tabeli studenci należy posłużyć się poleceniem:
INSERT INTO studenci VALUES ('Jan', 'Kowalski', '1980-04-24');
Powyższe polecenie można ponowić w celu wstawienia nowego rekordu. Należy tylko
zmodyfikować wartości pól dla nowego rekordu np.:
INSERT INTO studenci VALUES ('Aneta', 'Kaczmarek', '1982-05-16'); Jednak nie zawsze
chcemy wstawić wartości dla wszystkich pól tabeli. Wtedy polecenie INSERT należy
zmodyfikować. Po nazwie tabeli tworzymy listę pól, które będą wypełniane danymi. W takim
przypadku zapytanie należy zredagować w następujący sposób:
INSERT INTO studenci (imie, nazwisko) VALUES ('Alicja', 'Tomala'); Jak łatwo zauważyć
pole data_urodzenia zostało pominięte. W ten sposób można postąpić z dowolnym polem
tabeli studenci. Schemat polecenia SQL dla tego przypadku wygląda następująco:
INSERT INTO nazwa_tabeli (
nazwa_pola1,
nazwa_pola2,
nazwa_pola3
)
VALUES (
wartosc_pola1,
wartosc_pola2,
nazwa_pola3
);
Tak więc jeśli chcemy wstawiać nowe dane do istniejącej tabeli używamy polecenia INSERT.
Natomiast do modyfikacji danych będących w danej tabeli służy polecenie UPDATE. Jego
składnia jest następująca:
UPDATE nazwa_tabeli SET
nazwa_pola1='zmodyfikowana_wartość1',
nazwa_pola2='zmodyfikowana_wartość2',
nazwa_pola3='zmodyfikowana_wartość3';
Tak więc polecenie UPDATE studenci SET imie='Janek'; zmodyfikuje wszystkie rekordy w
taki sposób, że pole imie będzie miało wartość 'Janek'. Oczywiście jest to tylko przykład i
należy to wziąć pod uwagę. Bardziej przydatną konstrukcją dotyczącą modyfikacji danych z
7
MySQL i PHP w tworzeniu zaawansowanych stron internetowych na lekcjach informatyki
określonej tabeli jest zastosowanie w poleceniu UPDATE klauzuli WHERE. Wtedy zostaną
uaktualnione pola w danym rekordzie o ile dane z tego rekordu będą spełniać określony
warunek.
UPDATE studenci SET imie='Andrzej' WHERE nazwisko='Kowalski';
Powyższe polecenie zmodyfikuje pole imie na ‘Andrzej’ w tych rekordach, w których pole
nazwisko ma wartość ‘Kowalski’.
Ćwiczenie 7. W jaki sposób można wprowadzać dane do tabeli w bazie danych?
Ćwiczenie 8. Napisz kod strony internetowej, która umożliwi osobom mającym dostęp do
Internetu wprowadzanie swoich danych do bazy danych.
Ćwiczenie 9. Napisz kod strony internetowej, która umożliwi osobom mającym dostęp do
Internetu modyfikowanie swoich danych, które wcześniej wprowadzili do bazy danych.
Pobieranie danych z tabeli
Przechowywane dane w bazie danych mogą być pobierane z określonych tabel. Do tego celu
służy zapytanie SELECT. Nie jest konieczne pobranie wszystkich rekordów w pełnym
zestawie pól. Przykładowa składnia zapytania SELECT:
SELECT pole1, pole2, pole3 FROM nazwa_tabeli;
Jeśli interesuje nas pełny zestaw z danej tabeli wstawiamy zamiast wyszczególnionych pól
znak „*”.
SELECT * FROM nazwa_tabeli;
Można też podać warunek, który spowoduje znalezienie interesujących nas rekordów:
SELECT * FROM nazwa_tabeli WHERE warunek;
W praktyce takie zapytania dla naszej tabeli studenci będą miały postać:
SELECT * FROM studenci;
SELECT imie, nazwisko FROM studenci;
SELECT * FROM studenci WHERE imie = ‘Andrzej’;
Po WHERE stosując warunki wyboru można je łączyć ze sobą stosując operatory OR oraz.
AND. W warunkach wyboru można stosować następujące operatory porównań:
=
>
<
>=
<=
<> lub (!=)
like
równy
większy
mniejszy
większy równy
mniejszy równy
różny
porównanie
łańcuchów
Dane z tabeli można pobrać w określonym porządku. Wskazanie kolumn, według których
mają być posortowane dane podaje się po klauzuli ORDER BY oddzielone przecinkami. Jeśli
chcemy uzyskać listę studentów rosnąco (ASC) według nazwiska i malejąco (DESC) według
stypendium wpiszesz następujące polecenie:
SELECT * FROM studenci ORDER BY nazwisko ASC, stypendium DESC;
8
MySQL i PHP w tworzeniu zaawansowanych stron internetowych na lekcjach informatyki
Można też wpływać na ilość pobieranych rekordów stosując klauzulę LIMIT.
SELECT * FROM studenci LIMIT 5;
Odpowiedzią na to zapytanie będzie pięć pierwszych rekordów z tabeli studenci.
Ćwiczenie 10. Napisz kod strony internetowej, która umożliwi osobom znającym login i hasło
podgląd do danych z bazy danych.
Ćwiczenie 11. Napisz kod strony internetowej, która umożliwi osobom znającym login i hasło
podgląd do wybranych danych z bazy danych.
Ćwiczenie 12. Napisz kod strony internetowej, która umożliwi osobom znającym login i hasło
podgląd danych z bazy danych z możliwością sortowania ich wg trzech wybranych pól tabeli.
Ćwiczenie 13. Napisz kod strony internetowej, która umożliwi osobom znającym login i hasło
podgląd danych z bazy danych z możliwością wyświetlania rekordów wg określonego klucza
(np. tylko tych absolwentów, którzy ukończyli szkołę 5 lat temu, 2 lata temu).
Literatura:
MySQL
Autor: Leon Atkinson
Tłumaczenie: Jarosław Dobrzański, Tomasz Żmijewski
ISBN: 83-7361-170-3
Tytuł oryginału: Core MySQL
Format: B5, stron: 608
Bazy danych i MySQL. Od podstaw
Autorzy: Richard Stones, Neil Matthew
Tłumaczenie: Paweł Gonera
ISBN: 83-7197-728-X
Format: B5, stron: 560
PHP i MySQL. Dynamiczne strony WWW. Szybki start
Autor: Larry Ullman
Tłumaczenie: Michał Dadan (rozdz. 1-7), Piotr Pilch (rozdz. 8-13, dod. A - D)
ISBN: 83-7361-157-6
Format: B5, stron: 580
PHP i MySQL. Tworzenie stron WWW. Vademecum profesjonalisty. Wydanie trzecie
Autorzy: Luke Welling, Laura Thomson
Tłumaczenie: Paweł Gonera, Daniel Kaczmarek
ISBN: 83-7361-784-1
Format: B5, stron: 912
PHP i MySQL. Witryna WWW oparta na bazie danych. Wydanie III
Autor: Kevin Yank
Tłumaczenie: Sławomir Dzieniszewski, Paweł Janociński
ISBN: 83-7361-967-4
Format: B5, stron: 336
9