Forum_EduPlus
Tworzenie bazy danych w języku SQL i PHP - Wersja do druku

+- Forum_EduPlus (http://edukasprzak.ugu.pl)
+-- Dział: TiABD (/forumdisplay.php?fid=4)
+--- Dział: MYSQL (/forumdisplay.php?fid=6)
+--- Wątek: Tworzenie bazy danych w języku SQL i PHP (/showthread.php?tid=49)



Tworzenie bazy danych w języku SQL i PHP - Serwisant - 11-05-2020 10:50 PM

Podstawy MySQL (z wykorzystaniem PHP)


Wstęp

MySQL jest chyba jednym z najpopularniejszych interface'ów języka SQL (Structured Query Language), charakteryzuje się ogromną elastycznością, jeszcze większą prędkością i co najważniejsze jest bardzo łatwy w obsłudze.

Instalacja ( pod Win32 )

Instalacja nie sprawi najmniejszych problemów nawet 'zielonym' - wystarczy otworzyć zwykłym edytorem tekstu plik php3.ini (lub php.ini - w zależności od wersji PHP), następnie znaleźć linijkę ;extension=php3_mysql.dll i usunąć średnik, który znajduje się na początku linii. W ten sposób będziemy mogli korzystać z dobrodziejstw MySQL'a na serwerze Apache.

Zapytania

Aby wykonać cokolwiek w swojej bazie należy wysłać do niej informacje, wszystkie informacje wysyłane są do bazy danych w postaci zapytań, do czego służy funkcja mysql_query(zapytanie). Wszystkie zapytania wysyłamy w następujący sposób:

$zaptanie="twoje zapytanie";
$wykonaj=mysql_query($zapytanie);

Po takiej operacji zmienna $wykonaj będzie zawierać wartość TRUE, jeśli operacja powiedzie się, jeśli nie - zwróci wartość FALSE.

Łączymy się z bazą danych

Aby połączyć się z bazą danych używamy komendy:

mysql_connect (nazwa_hosta[:port] [, użytkownik] [,hasło]);

W praktyce oznacza to, że wymagana jest nazwa hosta - następnie opcjonalnie możemy wpisać nazwę użytkownika oraz jego hasło. Jeśli nie podamy nazwy hosta automatycznie nastąpi próba połączenia się z localhostem. Jeśli zabraknie nazwy użytkownika automatycznie zostaniemy zalogowani jako właściciel procesu baz danych, przeważnie właściciel nie ma ustawionego hasła, więc trzeci parametr nie jest wymagany. Poniższy przykład pokazuje jak połączyć się z serwerem mojserwer.pl jako użytkownik foo, z hasłem off:

$sql = mysql_connect (mojserwer.pl, foo, off);

Funkcja mysql_connect zwróci wartość TRUE, jeśli zakończy się sukcesem, jeśli nie powiedzie się - zwróci wartość FALSE.

Jeśli zakończyłeś już operacje w swojej bazie należy się rozłączyć, co robi się funkcją mysql_close(id_połączenia). Aby zakończyć połączenie, które zaprezentowałem powyżej należałoby zastosować komendę 'mysql_close' w następujący sposób:

mysql_close($sql);

Tworzenie nowej bazy oraz jej wybieranie

Do stworzenia nowej bazy używamy komendy mysql_create_db(nazwa) np.:

mysql_create_db("zawodnicy");

Samo stworzenie bazy nie oznacza, że właśnie na niej będziemy wykonywać operacje, dlatego musimy ją wybrać, co robimy komendą mysql_select_db(nazwa) np.:

mysql_select_db("zawodnicy");

Od tej pory można już wykonywać różne operacje w bazie 'zawodnicy'.

Zagadnienia SQL

Bazę danych możemy stworzyć także za pomocą wyrażeń SQL w formie zapytania:

$zapytanie="CREATE DATABASE zawodnicy";
$wykonaj = mysql_query($zaptanie);

Gdy nasza baza jest już utworzona możemy zacząć wprowadzać dane, najpierw jednak trzeba utworzyć tabelę, robimy to wg następującego schematu składni SQL:

CREATE TABLE nazwa_tabeli (nazwa1 typ1, nazwa2 typ2, PRIMARY KEY(nazwa_x))

Postarajmy się stworzyć tabelę według danego schematu:

$zapytanie = "CREATE TABLE nba (id char(3) NOT NULL, imie char(30), lata char(3), punkty char(3), mistrzostwa char(3), PRIMARY KEY(id))";

Rozpatrzmy powyższy przykład: klucz oznaczony jako PRIMARY KEY oznacza, że klucz ten jest kluczem głównym, a klucz główny nie może się powtarzać - w przypadku gdyby klucz główny się powtórzył po prostu rekord nie zostanie dodany do tabeli, klucz oznaczony jako NOT NULL nie może być pusty.

Dostępnych jest kilka typów pól, niektóre z nich przedstawiam poniżej:

char(x) - ciąg znaków o maksymalnej długości x, gdzie x nie może być większy od 255,
blob - ciąg znaków o długości ograniczonej przez twoją pamięć,
integer - liczba całkowita z zakresu od do ,
data - data w formacie DBMS (uwaga format odwrotny do normalnego: YYYY-MM-DD),
year - rok, jeśli zostanie podany zły, jego wartość zmieni się w 0000,
decimal(x, y) - liczba dziesiętna, gdzie x oznacza maksymalną liczbę cyfr, a y maksymalną liczbę cyfr po przecinku.

Mamy już stworzoną tabelę, teraz trzeba coś do niej dodać, robimy to wg następującego schematu:

INSERT INTO nazwa_tabeli VALUES (wartosc_pola1, wartosc_pola2, wartosc_pola3...)

Nie zawsze musimy wypełniać wszystkich pól, a robi się to w następujący sposób:

INSERT INTO nazwa_tabeli (nazwa_pola1, nazwa_pola2) VALUES (wartosc_pola1, wartosc_pola2)

Teraz pora na przykład, załóżmy, że chcemy dodać do naszej tabeli nowego zawodnika, operacja ta wygląda tak:

$zapytanie = "INSERT INTO nba VALUES(Jordan, 13, 32, 6)";

Po wykonaniu takiej operacji tabela 'nba' będzie już zawierać jednen rekord. Teraz kolejno możemy dodawać w ten sam sposób kolejne rekordy.

Jeśli chcemy przeszukać naszą tabelę w poszukiwaniu jakiegoś konkretnego rekordu robimy to za pomocą komendy SELECT wg schematu:

SELECT nazwa_pola FROM nazwa_tabeli WHERE warunek

Oczywiście nazw pól możemy podać kilka, lub wszystkie - jednak zamiast tego po prostu wstawiamy znak gwiazdki (SELECT * FROM itd.), warunek może być bardzo złożony, ale to jest temat na kolejny artykuł. Załóżmy, że chcemy przeszukać naszą tabelę w poszukiwaniu zawodnika o nazwisku 'Pipen' robimy to w następujący sposób:

$zapytanie = "SELECT imie FROM nba WHERE imie='Pipen'";

Funkcja zwróci wartość TRUE jeśli znajdzie w naszej tabeli Pipena, jeśli nie - FALSE. Pamiętaj jednak, że rozróżniane są wielkie i małe litery, dlatego 'Pipen' to nie to samo co 'pipen' czy 'PIPEN'...

Jeśli jakiś rekord jest już nie aktualny, lub po prostu źle wpisaliśmy dane, należałoby go zaktualizować, do czego służy komenda UPDATE:

UPDATE nazwa_tabeli SET wartosc_pola WHERE warunek

Powiedz my, że chcemy zmienić w naszej tabeli ile lat grał Jordan, robimy to tak:

$zapytanie = "UPDATE nba SET lata=15 WHERE imie='Jordan'";

Teraz Jordan grał już 15, a nie tak jak wcześniej 13 lat. Jeśli jakieś dane z danej tabeli już nas nie interesują pozbywamy się ich komendą DELETE:

DELETE FROM nazwa_tabeli [WHERE warunek]

W powyższym przykładzie warunek wziąłem w nawias, ponieważ jeśli nie podamy warunku z tabeli zostaną wymazane wszystkie rekordy. W praktyce kasowanie prezentuje się następująco:

$zapytanie = "DELETE FROM nba WHERE imie=Jordan";

Od tej chwili Jordana już nie ma w naszej tabeli zawodników nba.

Jak wyświetlić wyniki przeszukiwania tabeli?

Skoro poznaliście już podstawowe zagadnienia SQL'a to możemy przejść do bardziej zaawansowanych struktur - wyświetlanie wyników przeszukiwania tabeli. Wszystko odbywa się wg poniższego schematu:

$zapytanie = "SELECT * FROM nazwa_tabeli";
$wykonaj = mysql_query($zaptanie);
while($wiersz = mysql_fetch_array($wykonaj))
{
print "Pole pierwsze".$wiersz['pole1']."<br>";
print "Pole drugie".$wiersz['pole2']."<br>";
print "Pole trzecie".$wiersz['pole3']."<br>";
}

Wszystko to odbywa się dzięki komendzie 'mysql_fetch_array', której zadaniem jest pobieranie wierszy ze zbioru wyników i zwracanie jej w postaci tablicy. Oczywiście każda osoba znająca HTML'a i PHP zorientowała się, że zamiast pisać 'print' trzy razy, można by napisać tylko raz, ale napisałem tak dla przejrzystości kodu.

====================================
Dowiedzieliście się jak tworzyć bazy danych oraz jak w prosty sposób nimi zarządzać - teraz postaram się wam przedstawić bardziej zaawansowane struktury.

Budujemy prostą wyszukiwarkę

Na początku pierwszej lekcji wśród ogromnych zalet MySQL'a podałem m. in. łatwość jego obsługi - zawsze wydawało mi się, że wyszukiwarka jest strasznie trudną strukturą, żeby nie nazwać jej magiczną. Jak się okazuje wyszukiwarka jest niczym innym jak użyciem operatora SELECT (który poznałeś podczas poprzedniej lekcji) w połączeniu z operatorem LIKE. Tradycyjne użycie SELECT wygląda tak:

SELECT nazwy_pol FROM nazwa_tabeli WHERE warunek

Jeśli w warunku zastosujemy operator LIKE otrzymamy bardzo prostą wyszukiwarkę:

SELECT nazwy_pol FROM nazwa_tabeli WHERE nazwa_pola LIKE '%szukane_wyrażenie%'

Operator LIKE służy tutaj do przeszukiwania pól tekstowych w poszukiwaniu danego wyrażenia, znak procenta przed i po wyrażeniu odgrywa bardzo ważną rolę - powoduje dopasowanie dowolnych znaków przed i po danym wyrażeniu, spróbuję przedstawić to na przykładzie.

Poszukiwane wyrażenie 'nik' (zakładając, że znak procentu znajduje się przed i po wyrażeniu tj. %nik%):
Pasuje do niego np.: konik, nikt, nikogo, komornik, nikotyna. Jeśli nasze wyrażenie zawierało by procent tylko na początku (%nik) wtedy pasowałyby 'konik' i 'komornik' i inne wyrazy kończące się na 'nik', ponieważ dopasowywane są tylko wyrazy zaczynające się dowolną liczbą dowolnych znaków a kończących się na 'nik'. Natomiast wyrazy 'nikogo', 'nikt', 'nikotyna' nie pasowałyby, ponieważ 'nik' powinno być końcem wyrazu. Sytuację odwrotną uzyskamy, jeśli znak procentu postawimy na końcu wyrażenia.

Nawiązując do poprzedniej lekcji, podczas której stworzyliśmy tabelę z zawodnikami NBA, możemy np. przeszukać tabelę w poszukiwaniu wszystkich zawodników, w których imieniu i nazwisku występuje np. litera 'a'. Wyrażenie służące przeszukaniu takiemu wyglądałoby następująco:

$zapytanie = "SELECT * FROM nba WHERE imie LIKE \"%a%\"";

Równie dobrze możemy szukać wszystkich imion kończących się na 'a':

$zapytanie = "SELECT * FROM nba WHERE imie LIKE \"%a\"";

Lub zaczynających się literą 'a':

$zapytanie = "SELECT * FROM nba WHERE imie LIKE \"a%\"";

Taką samą wyszukiwarkę, a nawet dużo bardziej skomplikowaną i z większymi możliwościami można również napisać za pomocą wyrażeń regularnych SQL, jednak to jest temat na osobny artykuł.

Rozszerzamy wyszukiwarkę

Jak na razie nasze poszukiwania opierały się na wyszukiwaniu na tylko jednym warunku - jeśli jednak zechcemy wyszukać jakiejś danej, która zawiera więcej niż jeden warunek? W tym momencie z pomocą przychodzą nam dwa znane operatory z języka PHP 'AND' i 'OR' oraz dwa nowe 'BETWEEN' i 'IN'.

AND (można również &&) - jest to operator, dzięki, któremu można stworzyć złożone warunki, jego schemat wygląda następująco:

instrukcja WHERE warunek1 AND warunek2

Funkcja zwróci wartość TRUE, jeśli obydwa warunki zostaną spełnione, jeśli jeden z nich lub oba nie zostaną spełnione funkcja zwróci wartość FALSE.

OR (można również ||) - kolejny operator, dzięki, któremu można stworzyć złożone warunki, jego schemat wygląda następująco:

instrukcja WHERE warunek1 OR warunek2

Funkcja zwróci wartość TRUE, jeśli przynajmniej jeden z warunków zostanie spełniony, jeśli nie zostanie spełniony żaden warunek funkcja zwróci wartość FALSE.

IN - za pomocą tego operatora sprawdzamy czy dana wartość należy do określonego zbioru. Elementy zbioru podaje się w nawiasach i oddziela przecinkami. Schemat:

instrukcja WHERE nazwa_pola IN (1,2,5,8)

Funkcja zwróci wartość TRUE, jeśli wartość danego pola będzie należeć do określonego przez nas wcześniej zbioru - w tym przypadku, jeśli wartość będzie równa 1, 2, 3, lub 8. Jeśli chcemy np. sprawdzić czy wartość należy do jakiegoś przedziału to zamiast wypisywać cały zbiór przy użyciu operatora IN, możemy użyć operatora BETWEEN.

BETWEEN - sprawdza czy dana wartość należy do przedziału. Schemat:

instrukcja WHERE nazwa_pola BETWEEN dolny_przedział AND gorny_przedział

Jeśli chcielibyśmy teraz sprawdzić czy wartość pola należy do przedziału od 4 do 259 to nasza 'formułka' wyglądałaby następująco:

instrukcja WHERE nazwa_pola BETWEEN 4 AND 259

Łącząc powyższe operatory moglibyśmy np. wyszukać wszystkich zawodników z naszej tabeli, którzy grają co najmniej 10 do maksymalnie 15 lat, zbierają średnio co najmniej 20 punków na mecz i w swoim imieniu mają literę 'e'. Konstrukcja taka wyglądałaby następująco:

$zapytanie = "SELECT * FROM nba WHERE imie (LIKE \"e\") AND (lata BETWEEN 10 AND 15) AND (punkty > 20)";

Sortowanie wyników

Wyniki poszukiwań do tej pory były posortowane w takiej kolejności, w jakiej dodaliśmy je do bazy danych, ale jeśli zechcielibyśmy posortować naszych graczy np. w kolejności alfabetycznej lub wg ilości punktów, jakie zdobywają na mecz? Do takiej operacji potrzebna będzie nam komenda ORDER BY wraz z komendą DESC. Schemat stosowania tych komend przedstawia się następująco:

SELECT * FROM nazwa_tabeli ORDER BY nazwa_pola DESC

Oczywiście możemy sortować wg kilku pól, wtedy wymieniamy kolejne pola po przecinkach:

SELECT * FROM nazwa_tabeli ORDER BY nazwa_pola1 DESC, nazwa_pola2 DESC, nazwa_pola3 DESC...

Brak operatora DESC spowoduje wyświetlenie danych w kolejności odwrotnej do pożądanej. Przykładowo postaram się wam pokazać jak wyświetlić wszystkich zawodników z naszej tabeli w kolejności alfabetycznej, jeśli jednak znalazłoby się dwóch koszykarzy o identycznych personaliach kolejnym kryterium do ustawiania w kolejności będzie średnia ilość zdobywanych punków na mecz.

SELECT * FROM nba ORDER BY imie DESC, punkty DESC

Analiza błędów

Często nasz kod zawiera błędy, jednak jeśli nawet jakaś operacja się nie powiedzie my nie zostaniemy o tym poinformowani. Co zrobić aby uniknąć takich sytuacji lub chociaż odpowiednio je przeanalizować? Podczas pierwszej lekcji zwróciłem uwagę, że jeśli wykonanie zapytania, jakie wysłaliśmy do serwera nie powiedzie się funkcja mysql_query() zwróci wartość FALSE, w takim wypadku należałoby odpowiednio wykorzystać fakt, że o ty wiemy. Funkcja mysql_error() zawiera treść błędu baz danych MySQL, jeśli takowy istnieje. Teraz w połączeniu z elementarną instrukcją If otrzymujemy bardzo potężne narzędzie służące analizie błędów. Poniższy kawałek kodu przedstawia jak zatrzymywać program i wyświetlać komunikat błędu:

$sql = mysql_connect (mojserwer.pl, foo, off);
$zaptanie="twoje zapytanie";
if (!($wykonaj=mysql_query($zaptanie))) /* Sprawdza czy wykonanie powiodło się */
{
$error = mysql_error($sql) ; /* Kopiuje zawartość błędu do zmiennej $error */
echo "Bład skryptu, odpowiedź serwera: $error"; /* Drukuje błąd na stronie */
}
else /* Jeśli błąd nie wystąpił kontynuuje działanie programu */
{
[ dalsze dzialanie programu ]
}

Oczywiście nie wszystkie błędy są na tyle poważne, żeby od razu zatrzymywać działanie programu - można po prostu w przypadku mniej poważnego błędu wyświetlać odpowiednią informację i dalej kontynuować program.

=========================================

Mała rzecz, a cieszy ;-)

Podczas poprzedniej lekcji przy rozszerzaniu naszej wyszukiwarki zapomniałem wspomnieć o jednej rzeczy. Chodzi tutaj o wyświetlanie ilości znalezionych rekordów, można to zrobić na około tj. za pomocą wyrażeń SQL przeszukiwać i za każdym razem, kiedy coś znajdzie drukować to na stronie i zliczać. Potem na samym dole strony wyświetlić ile rekordów znaleziono. Można także, najpierw osobno zliczać, później wydrukować na górze strony, potem jeszcze raz przeszukiwać i dopiero wtedy drukować właściwą zawartość strony, jednak w ten sposób za bardzo zajmujemy serwer. Jest o wiele prostsza i przyjemniejsza metoda, a do serwer nie będzie nadużywany. Komenda 'mysql_num_rows' służy do zwracania ilości pól znajdujących się we wskazanym zbiorze wyników, stosujemy ją według następującego schematu:

$zapytanie = "SELECT nazwy_pol FROM nazwa_tabeli WHERE warunek";
$wykonaj = mysql_query($zaptanie);
$znaleziono = mysql_num_rows($wykonaj);

Po wykonaniu tej operacji zmienna '$znaleziono' będzie zawierać ilość wyszukiwanych elementów. Teraz możesz wydrukować na górze strony ile razy znaleziono poszukiwane wyrażenie, a potem znalezione elementy. Przykład:

$zapytanie = "SELECT * FROM nba WHERE imie LIKE \"%a%\" ORDER BY DESC";
$wykonaj = mysql_query($zaptanie);
$znaleziono = mysql_num_rows($wykonaj);
if ($znaleziono == "0")
{
print "Nie znaleziono żadnego zawodnika z literą 'a' w imieniu";
}
else
{
print "Znaleziono $znaleziono zawodników z literą 'a' w imieniu.<br><br>";
}
while ($row = mysql_fetch_array($dzialaj))
{
print "Imie zwodnika: <b>".$row['imie']." </b><br>Lata gry: <b>".$row['imie']." </b>";
}

Przy okazji pokazałem, co zrobić żeby pokazywał się napis informujący o braku szukanych rekordów. Po wykonaniu tych kilku linijek powinna nam się ukazać piękna lista z zawodnikami, których imię zawiera literę 'a' lub komunikat o braku takich zawodników w bazie danych.

Kasowanie baz danych

Czasami zdarzy się tak, że zrezygnujemy nie tylko z kilku informacji zawartych w danej bazie, ale z całej bazy, wtedy kasujemy ją przy użyciu komendy mysql_drop_db. Robimy to w następujący sposób:

mysql_drop_db (nazwa_bazy)

W odniesieniu do naszego przykładu, skasowanie całej bazy danych z zawodnikami wyglądałoby tak:

mysql_drob_db ("zawodnicy");

Inny sposób na wyświetlenie zawartości bazy danych

Jak na razie poznaliście tylko jedną metodę wydobywania informacji z bazy danych - za pomocą komendy 'mysql_fetch_arry', druga metoda , którą za chwile poznacie jest prawie taka sama. Komenda 'mysql_fetch_row' różni się tym od 'mysql_fetch_arry', że wyniki przedstawiane są w postaci tablicy indeksowanej liczbami (nie nazwami pól z bazy danych!). Rozpatrzmy przykład:

$zapytanie = "SELECT * FROM nba";
$dzialaj=mysql_query($zapytanie);
while ($pole = mysql_fetch_row($dzialaj))
{
print "$pole[1], ";
}
W ten sposób zostaną wyświetlone imiona wszystkich zawodników oddzielone od siebie przecinkami. Przypominam tylko, że imiona zawodników były drugim polem w naszej bazie danych.

Informacje o tabeli

Jeśli mamy dostęp do jakiejś tabeli, którą nie my stworzyliśmy to zapewne chcemy cos o niej wiedzieć np. nazwy kolumn czy jaka jest maksymalna długość znaków w danym polu.

Nazwy pól możemy wydobyć za pomocą komendy 'mysql_field_name' wg schematu:

mysql_field_name(id_wyników, indeks_pola)

Rozpatrzmy to na przykładzie:

$zapytanie = "SELECT * FROM nba";
$dzialaj=mysql_query($zapytanie);
$n_pola = mysql_field_name($dzialaj, 0);
Po wykonaniu tej operacji zmienna $n_pola będzie zawierać nazwę pierwszego pola (indeks 0) w tym przypadku zmienna zwróci wartość 'id' (takie jest pierwsze pole w tabeli nba).

Maksymalną ilość znaków danego pola wyciągamy komendą 'mysql_field_len' według następującego schematu:

mysql_field_len(id_wyników, indeks_pola)

Przykład:

$zapytanie = "SELECT * FROM nba";
$dzialaj=mysql_query($zapytanie);
$d_pola = mysql_field_len($dzialaj, 0);
Zmienna '$d_pola' będzie zawierać liczbę '10', ponieważ pierwsze pole 'id' ograniczyliśmy długością 10 znaków.

Nazwę tabeli, z której pochodzi wskazane pole możemy uzyskać korzystając z komendę ' mysql_field_table' w następujący sposób:

mysql_field_table(id_wyników, indeks_pola)

Przykład:

$zapytanie = "SELECT * FROM nba";
$dzialaj=mysql_query($zapytanie);
$n_tabeli = mysql_field_table($dzialaj, 0);

Zmienna '$n_tabeli' zwróci 'nba' czyli nazwę tabeli, z której pochodzi pole o indeksie zerowym.

Aby dowiedzieć się, jakiego typu jest dane pole trzeba użyć komendy 'mysql_field_type' analogicznie jak poprzednie komendy tego typu:

mysql_field_type(id_wyników, indeks_pola)

========= KONIEC ==========
Źródło: webhelp.pl Piotr Kuźmiński 2002