PHP + MySQL

Programski jezik PHP že sam po sebi vsebuje odlično podporo za delo z MySQL podatkovnimi bazami. Poglejmo, kako dobro PHP in MySQL sodelujeta.

Povezava na strežnik in izbira baze podatkov

Za začetek se mora naš PHP program vedno povezati na MySQL strežnik in izbrati podatkovno bazo, s katero želimo operirati.

To storimo s funkcijo mysqli_connect():

$db = mysqli_connect("localhost", "uporabniško ime", "geslo", "ime baze");

V spremenljivko $db na ta način shranimo odprto povezavo do naše podatkovne baze.

V primeru, da naš MySQL strežnik teče na istem računalniku kot naš spletni strežnik (in PHP interpreter), lahko kot prvi argument za funkcijo mysqli_connect() uporabimo kar niz "localhost". V nasprotnem primeru pa moramo tukaj seveda navesti dejanski naslov MySQL strežnika.

MySQL poizvedbe

V prejšnjem poglavju smo spoznali nekaj osnovnih MySQL ukazov, ki jim pravimo tudi MySQL poizvedbe (ang. queries).

Poizvedbe pokličemo s funkcijo mysqli_query(), ki za prvi argument sprejme odprto povezavo do podatkovne baze, za drugi argument pa SQL stavek.

V primeru, da želimo izvesti poizvedbo tipa INSERT, UPDATE ali DELETE, lahko funkciji mysqli_query() kot drugi argument torej podamo ustrezen SQL stavek. Primer:

mysqli_query($db, "DELETE FROM izdelki WHERE ime = 'Tipkovnica';");

Če pa iz neke tabele želimo dobiti podatke (s stavkom SELECT), moramo vrednost, ki jo vrne funkcija mysqli_query(), shraniti v spremenljivko in jo nato še obdelati:

$rezultat = mysqli_query($db, "SELECT * FROM izdelki;");

V spremenljivki $rezultat je shranjen rezultat naše MySQL poizvedbe.

Če to spremenljivko podamo kot argument funkciji mysqli_num_rows(), dobimo število vrstic v rezultatu. Primer:

$rezultat = mysqli_query($db, "SELECT * FROM izdelki;");
$num = mysqli_num_rows($rezultat);
echo $num; // izpiše 3

Kako pa se sprehodimo čez vse vrnjene vrstice v rezultatu?

V ta namen uporabimo funkcijo mysqli_fetch_assoc(), ki za argument sprejme rezultat naše MySQL poizvedbe in vrne eno-dimenzionalno polje s trenutno vrstico rezultatov. V polju do posameznih stolpcev dostopamo preko asociativnih ključev.

Če to funkcijo postavimo v zanko while, bomo zaporedoma dobili eno-dimenzionalna polja za vsako posamezno vrstico iz tabele rezultatov.

Primer kode za izpis vseh rezultatov MySQL poizvedbe:

$rezultat = mysqli_query($db, "SELECT * FROM izdelki;");
while ($vrstica = mysqli_fetch_assoc($rezultat)) {
   echo $vrstica['ime'];
   echo $vrstica['cena'];
   echo $vrstica['stevilo'];
   echo "<br />";
}

Druge uporabne funkcije

• mysqli_error(povezava) — vrne niz, ki opisuje zadnjo napako, na katero je naletel MySQL strežnik
• mysqli_affected_rows(povezava) — vrne število vrstic, na katere je vplivala zadnja MySQL poizvedba
• mysqli_real_escape_string(povezava, niz) — izredno pomembna funkcija, s katero se prepričamo, da so vsi enojni narekovaji in drugi posebni znaki v nizu, ki ga želimo uporabiti v MySQL poizvedbi, ustrezno "escapani"

Varnost

Zakaj je ravnokar omenjena funkcija mysqli_real_escape_string(povezava, niz) tako izredno pomembna?

Poglejmo si primer, kjer preko spletnega obrazca pošljemo svoje ime in ga shranimo v podatkovno bazo:

$ime = $_POST['ime'];
$sql_ukaz = "INSERT INTO osebe (ime) VALUES ('{$ime}');";
mysqli_query($db, $sql_ukaz);

Zakaj je to tako zelo pomembno?

Predstavljajte si, da uporabnik v vnosno polje našega spletnega obrazca vtipka svoje ime: Janez

Potem klikne gumb za pošiljanje obrazca našemu PHP programu.

To pomeni, da se našemu programu v spremenljivki $_POST['ime'] pošlje niz Janez.

Naša koda posledično zgradi sledeči SQL ukaz:
"INSERT INTO osebe (ime) VALUES ('Janez');"

Ta ukaz z naslednjo vrstico kode pošljemo MySQL strežniku, kjer se ukaz izvede. V tabelo z osebami se doda nova oseba z imenom Janez. Vse lepo in prav.

Ampak — kako nam lahko zagode zlonameren uporabnik?

Zlonameren uporabnik lahko v vnosno polje našega spletnega obrazca vtipka sledeče besedilo: '); DROP TABLE osebe; --

Ta niz se potem pošlje našemu programu v spremenljivki $_POST['ime'], tako kot prej.

Naša koda ta niz vzame in posledično zgradi sledeči SQL ukaz:
"INSERT INTO osebe (ime) VALUES (''); DROP TABLE osebe; -- ');"

Ta ukaz tako kot prej z naslednjo vrstico kode pošljemo MySQL strežniku. Pa poglejmo, kaj se izvede na MySQL strežniku:

1. Najprej se izvede prvi del ukaza do podpičja: "INSERT INTO osebe (ime) VALUES ('');
   Rezultat: V tabelo z osebami dodamo osebo, ki nima imena.
2. Potem se izvede drugi del ukaza: DROP TABLE osebe;
   Rezultat: Ta ukaz pobriše našo celotno tabelo z osebami! Izgubimo vse podatke!
3. Preostali del ukaza izgleda takole: -- ');
   Ta del se zaradi začetnega niza -- prepozna kot MySQL komentar in se v celoti ignorira.

Avč. :(

Iz tega razloga ne smemo nikoli zaupati vrednostim (nizom), ki jih prejmemo od uporabnikov. Preden takšne vrednosti vstavimo v SQL ukaze, jih moramo nujno obdelati z omenjeno funkcijo mysqli_real_escape_string(povezava, niz).

Pravilno napisana koda, ki nima ranljivosti:

$ime = mysqli_real_escape_string($db, $_POST['ime']);
$sql_ukaz = "INSERT INTO osebe (ime) VALUES ('{$ime}');";
mysqli_query($db, $sql_ukaz);

Funkcija mysqli_real_escape_string(povezava, niz) namreč vsak niz obdela na način, da ga lahko potem varno vstavimo v SQL ukaz. V zgornjem primeru omenjena funkcija pred znak ' v prejetem zlonamernem nizu dopiše znak \, ki mu pravimo "ubežni znak" (ang. escape character). Posledično lahko MySQL strežnik ukaz pravilno izvede, torej ustvari novo osebo z zelo nenavadnim imenom (kot ga je vnesel zlonamerni uporabnik) in z delom zaključi. Naša tabela posledično ostane varna.

Takšnemu načinu napada na podatkovno bazo rečemo SQL Injection. Napad je relativno pogost — ampak zdaj veste, kako ga lahko enostavno preprečite.

phpMyAdmin

Za lažje ustvarjanje podatkovnih baz in tabel ter za lažje pregledovanje podatkov ter razhroščevanje naših aplikacij si lahko pomagamo z odličnim brezplačnim orodjem phpMyAdmin.

Če ste programski paket XAMPP namestili po naših navodilih iz prvega poglavja, vam je orodje phpMyAdmin na voljo na sledečem naslovu:

http://localhost/phpmyadmin/

Uporabniški vmesnik orodja phpMyAdmin izgleda takole:



V seznamu na levi vidimo obstoječe podatkovne baze, v našem primeru je to podatkovna baza php_tecaj.

Iz seznama na levi izberemo podatkovno bazo, nato pa si lahko na desni ogledamo seznam tabel v podatkovni bazi. V našem primeru sta to tabeli izdelki in zaposleni.

Pod seznamom tabel na desni strani lahko ustvarimo novo tabelo. Najprej vpišemo ime nove tabele, nato pa navedemo še število polj (stolpcev), ki naj jih ima.

Primer: Ustvarili bomo tabelo z imenom ocene, ki bo imela 4 stolpce.

Ta dva podatka (ime tabele in število stolpcev) vpišemo v ustrezni vnosni polji, potem pa kliknemo gumb "Go" oziroma "Izvedi".

Sedaj moramo vsa štiri polja poimenovati, jim določiti podatkovni tip, nastaviti maksimalno dolžino in polju id na koncu dodeliti še lastnost auto_increment ter ga nastaviti kot primarni ključ oz. primary key.

Poglejmo si postopek, s katerim bomo ustvarili prvo polje (stolpec). V prvo vnosno polje napišimo ime, podatkovni tip nastavimo na VARCHAR, dolžino pa nastavimo na 255. To je glede imena vse, kar moramo nastaviti.

Za naslednje polje (stolpec) isti postopek ponovimo še v drugi vrstici, le da naj bo tokrat ime polja priimek.

V tretji vrstici polje (stolpec) poimenujmo ocena, podatkovni tip nastavimo na INT in mu dolžino nastavimo na 1, ker bo to polje vsebovalo le enomestna števila (ocene).

V četrti vrstici polje (stolpec) poimenujmo id, podatkovni tip nastavimo na INT, dolžina naj bo tokrat 6. Dodajmo še kljukico pri možnosti "A_I", ki predstavlja lastnost auto_increment ter v spustnem seznamu "Index" izberimo možnost PRIMARY. S tem smo to polje nastavili za naš primarni ključ.

Sedaj lahko kliknemo gumb "Save" oziroma "Shrani" in si ogledamo našo novo tabelo ocene, ki je že pripravljena za vnašanje podatkov.


Druge uporabne možnosti orodja phpMyAdmin:

Kot smo videli zgoraj, lahko z orodjem phpMyAdmin ustvarimo nove tabele. Tabele lahko tudi urejamo in brišemo. Z istim orodjem lahko v tabele vstavljamo tudi podatke (vrstice), ki jih prav tako lahko naknadno urejamo in brišemo.

V orodju phpMyAdmin je izredno uporaben tudi zavihek "SQL" na vrhu desne polovice strani, s katerim lahko ročno poženemo poljubne SQL ukaze (poizvedbe). Uporaben je tudi zavihek "Search" oziroma "Iskanje", s katerim iščemo po naši podatkovni bazi.

Nenazadnje nam phpMyAdmin v zavihku "Export" oziroma "Izvoz" omogoča, da izvozimo celotno podatkovno bazo (ali pa posamezno tabelo) v .sql tekstovno datoteko. To nam pride zelo prav pri prenosu podatkovne baze na nov strežnik ter pri izdelavi varnostne kopije (backup) naše podatkovne baze.

V zavihku "Import" oziroma "Uvoz" pa lahko preko orodja phpMyAdmin v naš MySQL strežnik uvozimo podatkovno bazo iz .sql tekstovne datoteke.

•   Kliknite za prenos demo podatkovne baze, ki smo jo uporabljali tekom tečaja...

Praktični primeri

Preden se skupaj sprehodimo čez nekaj praktičnih primerov dela s podatkovno bazo, prenesite zgornjo datoteko php_tecaj.sql na svoj računalnik in jo z orodjem phpMyAdmin uvozite v svoj MySQL strežnik. Na ta način nam bodo namreč na voljo tabele, ki jih bo uporabljala naša PHP koda v nadaljevanju.

Ste uvozili našo demo podatkovno bazo? Super! Nadaljujmo...

V prvem praktičnem primeru se bomo povezali na našo podatkovno bazo z imenom php_tecaj ter iz tabele izdelki izpisali ime in ceno vseh izdelkov, ki stanejo vsaj 100 €.

Primer delujoče kode, ki jo lahko poženete na svojem računalniku:

<?php
   // povežemo se na podatkovno bazo
   $db = mysqli_connect("localhost", "root", "", "php_tecaj");

   // izberemo željene izdelke
   $rezultat = mysqli_query($db, "SELECT ime, cena FROM izdelki WHERE cena > 100;");
   while ($vrstica = mysqli_fetch_assoc($rezultat)) {
      // izpišemo podatke o posameznem izdelku ter preidemo v novo vrstico
      echo "{$vrstica['ime']} stane {$vrstica['cena']} evrov.<br />";
   }
?>

Zgornjo kodo skopirajte v svojo datoteko index.php in jo poženite s svojim spletnim brskalnikom.

Deluje? :)

Super! To pomeni, da vaša PHP koda uspešno komunicira z MySQL podatkovno bazo. S tem ste postali zares učinkovit spletni programer.

Zapišimo še kratek programček, s katerim bomo naredili sledeče:

• dodali bomo izdelek z imenom "Prenosnik",
• izbrisali bomo izdelek z imenom "Tiskalnik",
• ceno izdelka "Monitor" bomo spremenili iz 349.99 € na 329.99 €,
• izpisali bomo število izdelkov v tabeli,
• ugotovili bomo zaokroženo povprečno ceno izdelkov, ki so nam na voljo.

Primer delujoče kode, ki opravi vse našteto:

<?php
   // povežemo se na podatkovno bazo
   $db = mysqli_connect("localhost", "root", "", "php_tecaj");

   // dodamo izdelek: prenosnik
   $ime_izdelka = "Prenosnik";
   $ime_izdelka = mysqli_real_escape_string($db, $ime_izdelka);
   mysqli_query($db, "INSERT INTO izdelki (ime, cena, zaloga) VALUES ('{$ime_izdelka}', 1699.99, 25);");
   echo "Dodali smo izdelek: {$ime_izdelka}<br />";

   // izbrišemo izdelek: tiskalnik
   $ime_izdelka = "Tiskalnik";
   $ime_izdelka = mysqli_real_escape_string($db, $ime_izdelka);
   mysqli_query($db, "DELETE FROM izdelki WHERE ime = '{$ime_izdelka}';");
   echo "Izbrisali smo izdelek: {$ime_izdelka}<br />";

   // posodobimo ceno izdelka (monitor) na 329.99 evrov
   $ime_izdelka = "Monitor";
   $ime_izdelka = mysqli_real_escape_string($db, $ime_izdelka);
   mysqli_query($db, "UPDATE izdelki SET cena = 329.99 WHERE ime = '{$ime_izdelka}';");
   echo "Posodobili smo ceno izdelka: {$ime_izdelka}<br />";

   // izpišemo število izdelkov, ki jih imamo trenutno v tabeli
   $rezultat = mysqli_query($db, "SELECT * FROM izdelki;");
   $stevilo_izdelkov = mysqli_num_rows($rezultat);
   echo "Število izdelkov, ki so nam na voljo: {$stevilo_izdelkov}<br />";

   // ugotovimo in izpišemo zaokroženo povprečno ceno izdelka
   $rezultat = mysqli_query($db, "SELECT ROUND(AVG(cena)) AS povprecna_cena FROM izdelki;");
   $vrstica = mysqli_fetch_assoc($rezultat);
   echo "Povprečna cena izdelka znaša {$vrstica['povprecna_cena']} evrov.<br />";
?>

Vzemite si čas in preberite vso zgornjo kodo, vrstico po vrstico. Na tej točki najbrž razumete prav vsak ukaz, poznate namen vsake funkcije, razumete pomen posameznih argumentov in veste, kakšen bo končni rezultat celotnega programa. To je zares lep dosežek!

Ko boste pripravljeni, kodo skopirajte v datoteko index.php in jo poženite.

Deluje?


Bravo — s tem smo zaključili poglavje o navezi PHP in MySQL.


V naslednjem poglavju pa nas čakajo le še osnove jezika JavaScript...

Prejšnje poglavje
Osnove MySQL