- Połączenie z relacyjną bazą danych.
Połączenie z bazą danych zostanie zrealizowane poprzez interfejs PDO dostępny w PHP od wersji 5.0.
W wcześniejszych wersjach PHP była to dołączana zewnętrzna biblioteka. Interfejs PDO umożliwia podłączenie
do większości relacyjnych baz danych i udostępnia ten sam zestaw funkcji (metod) do obsługi zapytań i poleceń SQL.
W czasie połączenia z bazą danych i powodzenia operacji tworzony jest obiekt klasy PDO.
Kod źródłowy skryptu przedstawiający polecenia tworzące połączenie z bazą danych zawarto w dwóch skryptach.
- Test połączenia z bazą danych - plik konfiguracyjny conf.php.
W celu łatwiejszego zarządzania aplikacją, utworzymy plik z danymi konfiguracyjnymi realizującymi połączenie
z bazą danych, który będziemy dołączać do tworzonych przez nas skryptów przetwarzających dane z bazy danych. Plik ten będzie dołączany poprzez
instrukcję include() do kolejnych skryptów realizowanych w ramach zadania. Dane konfiguracyjne tworzące
połączenie z bazą danych ElephantSQL przedstawiono w pliku conf.php.
Skrypt conf.php ( [listing dokumentu]
[link do dokumentu]
<?php
/*
Plik: conf.php
*/
/*
$host = 'qdjjtnkv.db.elephantsql.com'; // adres serwera bazodanowego
$port = '5432'; // port dostepu do bazy danych - domyślnie postgresql 5432
$username = 'haxyqkyi'; // konto uzytkownika
$password = '****************'; // haslo do bazy danych
$database = 'haxyqkyi'; // nazwa bazy danych
*/
$host = 'pascal.fis.agh.edu.pl'; // adres serwera bazodanowego
$port = '5432'; // port dostepu do bazy danych - domyślnie postgresql 5432
$username = '--users--'; // konto uzytkownika
$password = '****************'; // haslo do bazy danych
$database = '--user--'; // nazwa bazy danych
?>
- Test połączenia z bazą danych - skrypt test2.php.
Interfejs PDO umożliwia wyświetlenie informacji o błędach. W ramach języka php dostępna jest struktura poleceń
try {} oraz catch {} umożliwiająca przechwycenie błędów (w tym wypadku wyjątków). Informacja ta może
zostać przekazana do użytkownika lub zapisana w odpowiednim pliku. Kod źródłowy skryptu umożliwiającego połączenie
z bazą danych i przekazującego użytkownikowi informacji o błędzie interfejsu PDO przedstawiono w skrypcie test2.php.
Skrypt test2.php ( [listing dokumentu]
[link do dokumentu] )
<?php
/*
Plik test2.php
*/
include ('conf.php');
try {
$pdo = new PDO('pgsql:host='.$host.'; dbname='.$database.'; port='.$port, $username, $password );
echo 'Połączenie poprawnie nawiązane! ';
} catch(PDOException $e){
echo 'Połączenie nie zostało utworzone. '.$e->getMessage().'<br />';
}
?>
- Wydruk listy czytelników - skrypt test3.php.
Interfejs PDO umożliwia wysłanie zapytań SQL – SELECT do bazy danych na dwa sposoby. Przy użyciu:
- metody – query(),
- zestawu metod – prepare() i execute().
Druga forma przetwarzania zapytań jest bezpieczniejsza i dlatego zostanie ona poniżej omówiona i zastosowana.
Po poprawnym połączeniu z bazą danych posiadamy obiekt połączenia klasy PDO. Poprzez metodę prepare() przygotujemy
zapytanie do bazy danych. Kolejna metoda execute() wykona to polecenie tworząc obiekt zawierający zwrócone rekordy
(lub pojedyncze wartości) z bazy danych.
Do przeglądnięcia rekordów wykorzystamy pętlę foreach. Pętla foreach będzie pobierała kolejne rekordy z
zbioru wynikowego $stmt i umieszczała w tabeli asocjacyjnej $row[]. Kluczami w tej tabeli są nazwy atrybutów
tabeli bazodanowej. Wartości atrybutów są zgodne z wartościami w tabeli bazodanowej.
Istotną metodą wykorzystaną w skrypcie jest metoda closeCursor() zamykająca zbiór wyników.
Przedstawiony poniżej skrypt test3.php realizuje odczyt wyników dla przykładowego zapytania SQL do tabeli CZYTELNIK
przez omówione powyżej metody.
Skrypt test3.php ( [listing dokumentu]
[link do dokumentu] )
<?php
/*
Plik: test3.php
*/
include ('conf.php');
try {
$pdo = new PDO('pgsql:host='.$host.'; dbname='.$database.'; port='.$port, $username, $password );
echo 'Połączenie poprawnie nawiązane! ';
$stmt = $pdo -> prepare('SELECT nazwisko, imie FROM bibl.czytelnik');
$stmt -> execute() ;
echo '<ul>';
foreach($stmt as $row)
{
echo '<li>'.$row['nazwisko'].': '.$row['imie'].'</li>';
}
$stmt -> closeCursor();
echo '</ul>';
} catch(PDOException $e){
echo 'Połączenie nie zostało utworzone. '.$e->getMessage().'<br />';
}
?>
- Wydruk listy czytelników w tabeli html - skrypt test4.php.
W ramach tego punktu opracujemy skrypt formatujący wyniki zapytania
do tabeli HTML. Przy pomocy polecenia echo tworzymy zbiór wynikowy formatując dane wynikowe przy pomocy
odpowiednich elementów HTML ( <table>, <tr>, <th> i <td>).
Zadanie zrealizowano w skrypcie test4.php dla tabeli CZYTELNIK.
Skrypt test4.php ( [listing dokumentu]
[link do dokumentu] )
<?php
/*
Plik: test4.php
*/
include ('conf.php');
try {
$pdo = new PDO('pgsql:host='.$host.'; dbname='.$database.'; port='.$port, $username, $password );
echo 'Połączenie poprawnie nawiązane! ';
$stmt = $pdo -> prepare('SELECT nazwisko, imie FROM bibl.czytelnik');
$stmt -> execute() ;
echo '<table border=1 >';
// drukowanie wiersza naglowkowego tabeli
echo "<tr><th>Nazwisko</th><th>Imie</th></tr>\n";
echo '</tr>';
// drukowanie danych
foreach ($stmt as $row){
echo '<tr><td>'.$row['nazwisko'].'</td><td>'.$row['imie'].'</td></tr>';
}
// zamkniecie tabeli
echo '</table>';
$stmt -> closeCursor();
}catch(PDOException $e){
echo 'Połączenie nie zostało utworzone. '.$e->getMessage().'<br />';
}
?>
- Uniwersalny skrypt generujcy tabelę html dla dowolnych danych - zapytanie.php.
W poprzednim zadaniu otrzymaliśmy wynik zapytania w postaci tabeli HTML. Jednak opracowany skrypt daleki jest od uniwersalności.
Przy zmianie zapytania SELECT musimy każdorazowo modyfikować ilość kolumn w tabeli, jak również wiersz nagłówkowy zawierający nazwy kolumn.
Kolejną modyfikacją naszego skryptu będzie automatyzacja tworzenia wiersza nagłówkowego oraz liczby kolumn w tworzonej tabeli.
Informacja o nazwach kolumn i ich liczbie jest zapisana w tabeli wynikowej zwracanej przez bazę danych. Pozostaje nam tylko odczytanie
tej informacji i właściwe opracowanie skryptu wykorzystującego tę informację.
Kod źródłowy realizujący zadanie został przedstawiony w skrypcie zapytanie.php.
Zrealizujemy to tworząc funkcję, którą każdorazowo będziemy powoływać do realizacji zapytania. Parametrem funkcji jest kod zapytanie SQL.
W ramach skryptu wykorzystamy dwie nowe metody: setFetchMode() (linia kodu 15) oraz fetchAll() (linia kodu 17).
Metoda setFetchMode() - informuje interfejs o przesłaniu wynikowych danych jako tablicy asocjacyjnej o budowie ”nazwa atrybutu”:”wartość atrybutu”,
natomiast fetchAll() - pobiera wyniki zapytania do lokalnej tabeli $result[].
Wykorzystując polecenia foreach przetwarzamy tabelę $result[] dwukrotnie. Pierwszy raz w celu odczytania nazw atrybutów
i umieszczeniu ich w wierszu nagłówkowym (linie kodu 22 i 23). W zmiennej $key znajduje się nazwa kolumny (atrybutu bazodanowego).
Pętla foreach będzie czytała po kolei wszystkie kolumny. Drugi raz czytamy tablicę $result[] pobierając kolejne rekordy
danych (linia kodu 27). Pętla foreach pobiera kolejne rekordy wynikowe tworząc tabelę asocjacyjną $row[]. Wykorzystując kolejny
raz polecenie foreach (linia kodu 29) odczytujemy kolejne elementy tablicy $row[] umieszczając wartości w zbiorze wynikowym.
Skrypt zapytanie.php ( [listing dokumentu]
[link do dokumentu] )
<?php
/*
Plik: zapytanie.php
*/
function ZapytanieHTML($sql,$lp)
{
include ('conf.php') ;
try {
$pdo = new PDO("pgsql:host=".$host."; dbname=".$database.";
port=".$port, $username, $password );
echo "Połączenie poprawnie nawiązane! ";
$stmt = $pdo -> prepare($sql);
$stmt -> setFetchMode(PDO::FETCH_ASSOC);
$stmt -> execute() ;
$result = $stmt -> fetchAll() ;
// print_r($result);
echo "<table border=1 >\n";
// drukowanie wiersza naglowkowego tabeli
echo "<tr>\n" ;
foreach ($result[0] as $key => $useless){
print "<th>$key</th>";
}
echo "</tr>\n";
// drukowanie danych
foreach ($result as $row){
echo "<tr>";
foreach ($row as $key => $val){
print "<td>$val</td>"; }
echo "</tr>\n";
}
// zamkniecie tabeli
echo "</table>\n";
$stmt -> closeCursor();
}catch(PDOException $e){
echo "Połączenie nie zostało utworzone. ".$e->getMessage()."<br />";
}
}
- Sprawdzenie poprawności skryptu "zapytanie.php" - skrypt test5.php.
Skrypt sprawdzający poprawność opracowanej funkcji ZapytanieHTML() do prezenatacji wyników zapytań.
Skrypt test5.php ( [listing dokumentu]
[link do dokumentu] )
<?php
/*
Plik: test5.php
*/
include ('zapytanie.php');
$sql = "SELECT imie, nazwisko FROM bibl.czytelnik";
echo "<h3>Lista czytelnikow biblioteki</h3>" ;
ZapytanieHTML($sql) ;
?>
- Zapytanie parametryczne - skrypt zapytanie_par.php.
Kolejnym etapem rozbudowy naszej aplikacji jest opracowanie skryptu, dzięki któremu możemy zadawać zapytania parametryczne.
Pytanie parametryczne jest to zapytanie do bazy danych w którym użytkownik może zmieniać wartości warunku WHERE – jest to parametr.
Przykładowe zapytanie parametryczne przedstawiono poniżej:
SELECT * FROM czytelnik WHERE nazwisko LIKE ‘xxx’ ;
gdzie wartość ‘xxx’ jest dowolną wartością podawaną przez użytkownika.
W ramach interfejsu PDO udostępniono możliwość tworzenia zapytań parametrycznych. Realizujemy to przez odpowiednie przygotowanie
zapytania dla metody prepare(), a następnie przekazanie wartości parametrów w ramach metody execute() lub poprzez metodę bindValue().
Realizację w skrypcie PHP zapytania paramaterycznego SQL z wykorzystaniem metody bindValue() przedstawia poniższy kod.
$par = "K%";
$sql = "SELECT imie, nazwisko FROM czytelnik WHERE nazwisko LIKE :par ";
$stmt = $pdo -> prepare($sql);
$stmt -> bindValue(":par",$par,PDO::PARAM_STR);
$stmt -> setFetchMode(PDO::FETCH_ASSOC);
$stmt -> execute() ;
Przekazanie wartości parametru przez metodę execute() przedstawiono w poniższym kodzie.
$par = "K%";
$sql = "SELECT imie, nazwisko FROM czytelnik WHERE nazwisko LIKE :par ";
$stmt = $pdo -> prepare($sql);
$stmt -> setFetchMode(PDO::FETCH_ASSOC);
$stmt -> execute( array(":par" => $par) ) ;
W ramach realizacji zadania obsługę zapytań parametrycznych zrealizujemy wykorzystując technikę z metodą execute()
jako bardziej uniwersalną. W ramach poniższego skryptu przedstawiono funkcję obsługującą dowolne zapytania sparametryzowane.
Skrypt zapytanie_par.php ( [listing dokumentu]
[link do dokumentu] )
<?php
/*
Plik: zapytanie_par.php
*/
function ZapytanieParHTML($sql,$par,$lp)
{
include ("conf.php") ;
try {
$pdo = new PDO("pgsql:host=".$host."; dbname=".$database.";
port=".$port, $username, $password );
// echo "Połączenie poprawnie nawiązane! ";
$stmt = $pdo -> prepare($sql);
$stmt -> setFetchMode(PDO::FETCH_ASSOC);
$stmt -> execute($par) ;
$result = $stmt -> fetchAll() ;
// print_r($result);
echo "<table border=1 >\n";
// drukowanie wiersza naglowkowego tabeli
echo "<tr>\n" ;
foreach ($result[0] as $key => $useless){
print "<th>$key</th>";
}
echo "</tr>\n";
// drukowanie danych
foreach ($result as $row){
echo "<tr>";
foreach ($row as $key => $val){
print "<td>$val</td>"; }
echo "</tr>\n";
}
// zamkniecie tabeli
echo "</table>\n";
$stmt -> closeCursor();
}catch(PDOException $e){
echo "Połączenie nie zostało utworzone. ".$e->getMessage()."<br />";
}
}
?>
- Formularz do realizacji zapytania parametrycznego - skrypt test7f.php
W celu wykorzytsania pytania parametrycznego należy zrealizaowć kilka zadań, dla przyskładu - wyszukanie książek wypożyczonych przez konkretnego czytelnika:
- należy przygotować odpowiedni formularz, przy pomocy którego wybierzemy tego czytelnika
- możemy podać identyfikator czytelnika lub jego nazwisko w formularzu, ale lepiej wybrać czytelnika z listy
- w przypadku listy należy opracować formularz umożliwiający wybór czytelnika z listy (wykorzystujemy element <select> z formularza HTML).
- zwrócony identyfikator czytelnika wykorzystać do wyszukania książek
- wykorzystać utworzoną uniwersalną funkcję tworzącą tabele HTML dla zapytań parametrycznych
Skrypt przedstawiony w tym punkcie realizuje powyższe punkty.
Skrypt test7f.php ( [listing dokumentu]
[link do dokumentu] )
<?php
/*
Plik: test7f.php
*/
include ('conf.php');
try {
$pdo = new PDO("pgsql:host=".$host."; dbname=".$database.";
port=".$port, $username, $password );
// echo "Połączenie poprawnie nawiązane! ";
$sql = "SELECT id_czytelnik, nazwisko, imie FROM bibl.czytelnik ORDER BY nazwisko";
$stmt = $pdo -> prepare($sql);
$stmt -> setFetchMode(PDO::FETCH_ASSOC);
$stmt -> execute() ;
$result = $stmt -> fetchAll() ;
echo "<form name=\"form\" action=\"test7.php\" method=\"post\" >";
echo "<select name=\"id\" >";
foreach ($result as $row){
echo "<option value='".$row[id_czytelnik]."' >".$row[nazwisko]." ".$row[imie]."</option>";
}
echo "</select>";
$stmt -> closeCursor();
echo "<input type=\"submit\" value=\"Wyslij\" >";
echo "</form>";
}catch(PDOException $e){
echo "Połączenie nie zostało utworzone. ".$e->getMessage()."<br />";
}
?>
- Realizacja zapytania parametrycznego - skrypt test7.php
W ramach tego punktu przedstawiono skrypt realizujący funkcjonalność formularza przedstawionego w punkcie 9.
Skrypt test7.php ( [listing dokumentu]
[link do dokumentu] )
<?php
/*
Plik: test7.php
*/
include ("zapytanie_par.php");
$val = $_POST["id"] ;
$sql = "SELECT k.tytul, k.autor FROM bibl.ksiazka k JOIN bibl.egzemplarz e ON k.id_ksiazka = e.id_ksiazka
JOIN bibl.wypozyczenie w ON e.id_egzemplarz=w.id_egzemplarz WHERE w.id_czytelnik = :id ";
$par = array( ":id" => $val ) ;
echo "<h3>Lista ksiazek wypozyczonych przez czytelnika</h3>" ;
ZapytanieParHTML($sql,$par) ;
?>
- Wprowadzanie czytelnika do bazy danych - formularz test8.html
Realizacja przykładowego skryptu wprowadzającego dane do bazy danych - formularz HTML umożliwiający wprowadzenie danych czytelnika.
Skrypt test8.html ( [listing dokumentu]
[link do dokumentu] )
<!--
Plik: test8.html
-->
<form name="czytelnik" action="test8.php" method="post" >
<table>
<tr><td>Nazwisko: </td><td><input type="text" name="nazwisko" id="nazwisko" /></td></tr>
<tr><td>Imie: </td><td><input type="text" name="imie" id="imie" /></td></tr>
<tr><td>Kod pocztowy:</td><td><input type="text" name="kod" id="kod" /></td></tr>
<tr><td>Miasto: </td><td><input type="text" name="miasto" id="miasto" /></td></tr>
<tr><td>Adres: </td><td><input type="text" name="adres" id="adres" /></td></tr>
<tr><td>E-mail: </td><td><input type="text" name="email" id="email" /></td></tr>
<tr><td> </td><td><input type="submit" value="Wyslij dane" /></td></tr>
</table>
</form>
- Skrypt wprowadzający czytelnika do bazy - formularz test8.php
Skrypt realizujący funkcjonalność formularza przedstawionego w punkcie 11.
Skrypt test8.php ( [listing dokumentu]
[link do dokumentu] )
<?php
/*
Plik: test8.php
*/
// odczyt danych przeslanych z formularza test8.html
$nazwisko = $_POST['nazwisko'] ;
$imie = $_POST['imie'] ;
$kod = $_POST['kod'] ;
$miasto = $_POST['miasto'] ;
$adres = $_POST['adres'] ;
$email = $_POST['email'] ;
// Zapytanie SQL zapisujące dane do bazy danych
$sql = "INSERT INTO bibl.czytelnik (nazwisko, imie, kod_pocztowy, miasto, adres, email) VALUES ( ?, ?, ?, ?, ?, ?)" ;
$dane = array($nazwisko,$imie,$kod,$miasto,$adres,$email) ;
include ('conf.php') ;
try {
$pdo = new PDO('pgsql:host='.$host.'; dbname='.$database.'; port='.$port, $username, $password );
//echo 'Polaczenie poprawnie nawiazane! ';
$stmt = $pdo -> prepare($sql);
$stmt -> execute($dane) ;
$count = $stmt -> rowCount();
if ($count !== false) { echo "Do bazy zostal dodany nowy rekord." ; }
else { echo "Blad podczas wprowadzania danych." ; }
}catch(PDOException $e){
echo 'Połączenie nie zostało utworzone. '.$e->getMessage().'<br />';
}
?>
- Wypożyczenie - formularz wybierający czytelnika i książkę - formularz test9f.php
W ramach tego skryptu wybieramy czytelnika oraz książkę, którą czytelnik pożycza. W obu przypadkach tworzymy listy na
podstawie tabel bazodanowych CZYTELNIK i EGZEMPLARZ, z którycych wybieramy
interesujące nas rekordy. Dodatkowo w czasie wyboru książki sprawdzamy czy jest dostępna do wypożyczenia.
Po odczytaniu identyfikatorów wysyłamy odpowiednie dane do skryptu, który zapiszę wypożyczoną pozycję w odpowiednich tabelach w bazie danych.
Skrypt test9f.php ( [listing dokumentu]
[link do dokumentu] )
<?php
/*
Plik: test9f.php
*/
include ('conf.php');
try {
$pdo = new PDO("pgsql:host=".$host."; dbname=".$database.";
port=".$port, $username, $password );
// echo "Połączenie poprawnie nawiązane! ";
echo "<form name=\"form\" action=\"test9.php\" method=\"get\" >";
// czytelnik
$sql = "SELECT id_czytelnik, nazwisko, imie FROM bibl.czytelnik ORDER BY nazwisko";
$stmt = $pdo -> prepare($sql);
$stmt -> setFetchMode(PDO::FETCH_ASSOC);
$stmt -> execute() ;
$result = $stmt -> fetchAll() ;
echo "<select name=\"id_czytelnik\" >";
foreach ($result as $row){
echo "<option value='".$row[id_czytelnik]."' >".$row[nazwisko]." ".$row[imie]."</option>";
}
echo "</select><br />";
$stmt -> closeCursor();
// ksiazka
$sql = "SELECT e.id_egzemplarz, k.tytul, k.autor FROM bibl.egzemplarz e JOIN bibl.ksiazka k ON e.id_ksiazka = k.id_ksiazka WHERE e.status=0 ORDER BY tytul";
$stmt = $pdo -> prepare($sql);
$stmt -> setFetchMode(PDO::FETCH_ASSOC);
$stmt -> execute() ;
$result = $stmt -> fetchAll() ;
echo "<select name=\"id_egzemplarz\" >";
foreach ($result as $row){
echo "<option value='".$row[id_egzemplarz]."' >".$row[tytul]." : ".$row[autor]."</option>";
}
echo "</select><br />";
$stmt -> closeCursor();
echo "<input type=\"submit\" value=\"Wyslij\" >";
echo "</form>";
}catch(PDOException $e){
echo "Połączenie nie zostało utworzone. ".$e->getMessage()."<br />";
}
?>
- Wypożyczenie - zapisanie do bazy danych - formularz test9.php
Skrypt realizuje zapis do bazy danych faktu wypożyczenia książki, dane przesłane są z skryptu z punktu 13.
Skrypt test9.php ( [listing dokumentu]
[link do dokumentu] )
<?php
/*
Plik: test9.php
*/
include ("conf.php");
$id_cz = $_GET["id_czytelnik"] ;
$id_eg = $_GET["id_egzemplarz"] ;
$date = "2013-2-8" ;
$sql1 = "UPDATE bibl.egzemplarz SET status=1 WHERE id_egzemplarz= ? AND status=0" ;
$par1 = array( $id_eg ) ;
$sql2 = "INSERT INTO bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES ( ?, ?, ?, ?)" ;
$par2 = array( $id_cz, $id_eg, $date, 1 ) ;
try {
$pdo = new PDO('pgsql:host='.$host.'; dbname='.$database.'; port='.$port, $username, $password );
//echo 'Polaczenie poprawnie nawiazane! ';
try {
$pdo -> beginTransaction();
$stmt1 = $pdo -> prepare($sql1);
$stmt1 -> execute($par1) ;
if ( $stmt1 -> rowCount() == 0 ) $pdo -> rollBack() ;
$stmt2 = $pdo -> prepare($sql2);
$stmt2 -> execute($par2) ;
$pdo -> commit();
//if ($count !== false) { echo "Do bazy zostal dodany nowy rekord." ; }
//else { echo "Blad podczas wprowadzania danych." ; }
echo "Ksiazka zostala pozyczona.";
} catch (Exception $e) {
// If something raised an exception in our transaction block of statements,
// roll back any work performed in the transaction
print '<p>Niemozliwe zrealizowanie wypozyczenia !</p>';
$pdo -> rollBack();
}
}catch(PDOException $e){
echo 'Polaczenie nie zostało utworzone. '.$e->getMessage().'<br />';
}
?>
