PHPPamokos.lt


15. Darbas su MySQL duomenų baze

Kaip turbūt galima numanyti, ne viskas yra saugoma PHP skriptuose ir kintamuosiuose. Antraip kiltų klausimas - o kur saugomi vartotojų duomenys, kai jie atsijungia nuo interneto? Būtent tam yra skirtos duomenų bazės arba, kalbant moksliškiau, duomenų bazių valdymo sistemos. Viena iš populiariausių tokių sistemų yra MySQL, su kuria dažniausiai ir siejamas programavimas PHP kalba. Būtent šią sistemą nagrinėsime šiame skyriuje. Jei jums įdomios ir kitos duomenų bazių valdymo sistemos, tai paminėsiu populiaresnius pavadinimus, galėsite pasigilinti patys: Oracle, PostgreSQL, Microsoft SQL Server, Sybase.

Kas yra duomenų bazė?

Šis terminas yra vartojamas dažnai kaip programuotojų, taip ir žmonių, nesusijusių su IT. Ir visgi: kas tai yra?

Duomenų bazes galima palyginti su visiems pažįstamomis Excel lentelėmis, kuriose saugomi duomenys pagal tam tikrus stulpelius ar laukus.

Duomenų bazė skirtą duomenų saugojimui, apdorojimui ir tvarkymui. Kitais žodžiais, duomenų bazėje yra saugomi jūsų tinklalapio duomenys. Tam skirtos specialios struktūros - lentelės, kurios savo ruožtu susideda iš lentelės laukų. Konkretus pavyzdys - viršuje esančio Excel failas pavertimas į tinklalapio vartotojų duomenų bazės lentelę:

Vartotojai
ID
Vardas
Slaptažodis
El.paštas
Prisijungimo laikas

Viršutinėje dalyje yra lentelės pavadinimas, o po juo išvardinti visi tos lentelės laukai: vartotojo ID numeris, vardas, slaptažodis, elektroninio pašto adresas ir paskutinio prisijungimo laikas.

Kitas pavyzdys - sakykime, jūsų tinklalapyje yra saugomas produktų katalogas. Tai tokia duomenų bazės lentelė galėtų atrodyti taip:

Produktai
ID
Pavadinimas
Kategorija
Aprašymas
Kaina

Ir taip lentelė po lentelės gaunama visa duomenų bazė, kurioje saugoma informacija apie jūsų projekto vartotojus, straipsnius, katalogus, naujienas ir viską, su kuo susijęs jūsų konkretus tinklalapis.

Šiuose pavyzdžiuose laukų pavadinimus pateikiau kaip lietuviškus, kad būtų suprantamiau, bet "realybėje" juos reikia daryti lotyniškomis raidėmis ir be tarpų - atskiriant žodžius pabraukimo ženklais:

Users
id
username
password
email
login_time

Kiekvienas laukas turi turėti savo tipą - juk reikia, kad duomenų bazė suprastų, ar tai skaičius, ar tekstas, ar failas, ar dar kas nors. Dar daugiau - galime nurodyti maksimalų ilgį prie konkretaus tipo, jeigu iš anksto žinome, kokio ilgio bus reikšmės - tokiu būdu sutaupysime vietos duomenims diske.

Populiariausi DB laukų tipai yra šie:

  • VARCHAR(X) - teksto eilutė, su maksimaliu ilgiu X simbolių
  • INT(X) - skaičius, su maksimaliu skaitmenų skaičiumi X
  • TEXT - ilgesnė tekstinė eilutė, paprastai neribojama, tiksliau ribojama iki kelių gigabaitų
  • DATETIME ir TIMESTAMP - tipai, skirti laikui saugoti

Taigi, žinodami tipus, pertvarkome mūsų lentelę į rimtesnį pavidalą:

Users
id: INT(10)
username: VARCHAR(50)
password: VARCHAR(50)
email: VARCHAR(50)
login_time: TIMESTAMP
 

Išoriniai raktai

Be abejo, lentelėmis ir jų struktūra viskas nesibaigia.

Turbūt pastebėjote, kad abiejose viršuje esančiose lentelėse yra laukas ID. Kam jis reikalingas? Vienas iš pritaikymų - kad būtent tą eilutės unikalų ID galima būtų naudoti kitose duomenų bazės lentelėse.

Sakykime, jums reikia ne tik vartotojų lentelės, bet ir atskiros lentelės, saugančios kiekvieną vartotojo prisijungimą. Kaip toje lentelėje identifikuoti vartotoją? Teisingai, pagal vartotojo ID. Pavyzdys:

Sessions
id
user_id
login_time
logout_time
ip_address

Lentelėje Sessions laukas user_id sutaps su lentelės users lauku id, ir tada mes galėsime atsekti kiekvieno vartotojo prisijungimus pagal jo ID. Toks dalykas vadinamas išoriniu raktu (foreign key). Dažniausiai laukas, kuris veikia kaip išorinis raktas, yra pavadinamas pagal rišančios lentelės pavadinimą, tada seka pabraukimas ir "id". Pvz: user_id, session_id, group_id ir pan.

 

SQL kalba

Praktiškai visas darbas su duomenų baze vyksta su SQL kalbos užklausų pagalba. Šios kalbos detalus nagrinėjimas neįeina į šio kurso temas, tad tuo reikėtų jums pasidomėti atskirai, bet paprasčiausias ir dažniausiai naudojamas užklausas, be abejo, panaudosime ir mūsų pavyzdžiuose. Su SQL kalba galima, pavyzdžiui:

  • Sukurti lentelę;
  • Pridėti naują lauką prie lentelės;
  • Įrašyti duomenis į lentelę arba juos pakoreguoti/pašalinti;
  • Ištraukti iš lentelės reikalingus duomenis;
  • Ištraukti iš lentelės reikalingus duomenis pagal tam tikrą paieškos filtrą;
  • Ištraukti agreguotus duomenis iš kelių lentelių pagal lentelių sąryšius.

Ir taip toliau...

Keletas konkrečių užklausų pavyzdžių, priskirtų prie PHP kintamųjų:

$query = "select * from users where id = 123";
// grąžina vartotojo duomenis su ID 123
$query = "insert into users
(username, password, email, login_time)
values
('Petriukas', 'Petr123', 'petriukas@gmail.com', '2014-03-07')";
// įterpia eilutę į vartotojų lentelę
$query = "delete from users where id < 100";
// ištrina visus vartotojų įrašus su ID iki 100
 

Darbas su duomenų MySQL baze: įrankis PhpMyAdmin

Nesibaiminkite - kuriant duomenų bazę, jums nereikės rašyti SQL kalbos užklausų. Yra sukurti įrankiai, padedantys automatizuoti veiksmus ir juos atlikti paprasčiau. Labiausiai paplitusi sistema darbui su MySQL yra PhpMyAdmin, kai užsakote vietą serveryje - teiraukitės, ar yra priėjimas prie PhpMyAdmin, ir kaip būtent prie tos sistemos prieiti.

Jei gausite prieigą, galėsite patogiai kurti duomenų bazes ir jas redaguoti.

Štai kaip atrodo lentelės sukūrimo langas su phpMyAdmin:

Kaip kurti duomenų bazę su phpMyAdmin - čia irgi ne šio kurso tema, pasiskaitykite apie tai internete. Toliau darysime prielaidą, kad jūs jau turite duomenų bazę, ir jums reikia su ja dirbti iš PHP kalbos skriptų. Paaiškinsime, kaip iš PHP prisijungti ir valdyti savo duomenis.

 

PHP ir MySQL: prisijungimas ir užklausų vykdymas

Šiek tiek nukrypimas į šoną, bet labai svarbus. Kažkur prieš penkerius metus PHP pasaulyje įvyko pasikeitimas, kalbant apie darbą su duomenų baze. Prieš tai visi naudojo funkcijas, prasidedančias nuo mysql_ (pvz. mysql_connect(), mysql_query() ir pan.). Tad jei analizuosite kieno nors seną parašytą kodą ir pamatysite šias funkcijas - jos vis dar veikia, ir greičiausiai veiks dar ilgai. Bet jos labai nerekomenduojamos dėl daugelio priežasčių - daugiausiai dėl saugumo problemų ir dėl nelankstumo.

Atsižvelgiant į mysql_ funkcijų "galiojimo laiko pabaigą", šiuo metu yra du būdai dirbti su duomenų baze iš PHP: tai yra PDO ir MySQLi. Šiame kurse papasakosime apie PDO - tai dažniausiai naudojamas būdas.

Pirmas dalykas, kurį mes turime padaryti iš PHP pusės yra prisijungti prie duomenų bazės serverio. Tam sukuriame naują objektą PDO su parametrais.

$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');

PDO sukūrimas turi tris parametrus: prisijungimo eilutę, vartotoją ir slaptažodį. O ir pati prisijungimo eilutė, kaip matote, susideda iš tam tikros struktūros. Realiai ką reikia žinoti tai į ką pakeisti localhost jei reikia (dažniausiai nereikia nes duomenų bazė yra tame pačiame kompiuteryje kaip ir PHP sistema) ir duomenų bazės pavadinimą - šiuo atveju testdb.

Vartotojas ir slaptažodis nustatomi iš serverio pusės, prie serverio administravimo įrankių turi būti galimybę sukurti DB vartotoją. Funkcija "new PDO" grąžina prisijungimo identifikatorių, kurį vėliau galima naudoti kitose DB funkcijose.

Viskas, prisijungėme prie duomenų bazės - dabar jau galima rašyti ir vykdyti konkrečias SQL užklausas, susijusias su pasirinkta duomenų baze. Tam yra skirta funkcija exec(), kurios rezultatas - SQL užklausos įvykdymas.

Pavyzdys 1. Lentelės užpildymas duomenimis:

$query = "insert into users (user, password, email, login_time) values
('Petriukas', 'Petr123', 'petriukas@gmail.com', '2014-03-07')";
$db->exec($query);

Funkcijai exec() paduodamas eilutės kintamasis, kuriame aprašyta SQL užklausa. Taip pat kartais pravartu sužinoti ką tik įterptos DB eilutės lauką ID:

$insertId = $db->lastInsertId();

Pavyzdys 2. Lentelės duomenų atnaujinimas:

$query = "update users set login_time = '2014-03-10' where id = 123";
$db->exec($query);

Pavyzdys beveik analogiškas, tik vietoje SQL komandos INSERT naudojama komanda UPDATE.

Pavyzdys 3. Duomenų ištraukimas iš lentelės:

$stmt = $db->query('SELECT * FROM users');
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  echo $row['id'].' '.$row['user'];
}

Čia jau viskas sudėtingiau, reikia paaiškinimų. Duomenų ištraukimui ir panaudojimui reikalingos dar kelios PHP funkcijos.

Funkcija query() įvykdo užklausą ir rezultatą įrašo į kintamąjį, kurį vėliau galima naudoti ištrauktų eilučių apdorojimui.

Tada tampa svarbi funkcija fetch(), kurios rezultatas - grąžinama viena EINAMOJI eilutė iš viso eilučių sąrašo, vėlgi pagal tą patį užklausos identifikatorių. žodis "einamoji" reiškia tai, kad jeigu pirmą kartą iškviečiame fetch(), grąžinama pirma eilutė, antrą kartą - antra eilutė, ir taip toliau iki tol, kol eilučių nebebus, ir tada ta funkcija grąžins klaidą.

Taigi, mūsų atveju yra užsukamas ciklas, ir kiekvienu jo vykdymo metu ištraukiame po vieną duomenų bazės rezultato eilutę. Ta eilutė yra įrašoma kaip masyvas su tekstinio formato elementais (už tai atsako parametras PDO::FETCH_ASSOC - yra ir kitų ištraukimo būdų, bet kol kas nesigilinkite), ir vėliau mes to masyvo elementus galime panaudoti, pvz. išvesti į ekraną, kaip parodyta aukščiau.

Yra ir du kitokie būdai apdoroti funkcijos query() rezultatą, t.y. pereiti per rezultatų eilutes.

foreach($stmt as $row) {
  echo $row['id'].' '.$row['user'];
}

Arba tiesiog grąžinti visas eilutes iš karto į vieną masyvą:

$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

Taip pat yra ir funkcija, grąžinanti rezultato eilučių skaičių:

$row_count = $stmt->rowCount();
 

Užklausos su parametrais

Viena iš priežasčių, kodėl PDO yra geras sprendimas, dirbant su DB, tai yra parametrų naudojimas. Sakykime, jūs nežinote, koks kintamasis turi būti panaudotas užklausoje, ir jį turėsite tik kaip kintamąjį. Sakykime, select * from users where id = ?, kur po klaustuku slepiasi kintamasis, ateinantis pvz iš sesijos ($_SESSION['user_id']).

Kaip tai atrodo per PDO:

$stmt = $db->prepare("SELECT * FROM table WHERE id=? AND name=?");
$stmt->execute(array($id, $name));

Paaiškinu. Funkcija prepare dar nevykdo konkrečios užklausos, bet ją "paruošia" kintamųjų įterpimui, o realų vykdymą atlieka funkcija execute(), kuriai paduodame kintamųjų sąrašą.

Taip pat galimas ir kitas kintamųjų surišimo variantas - pagal pavadinimus, prieš juos padedant dvitaškį.

$stmt = $db->prepare("INSERT INTO table (field1, field2) VALUES (:field1, :field2)");
$stmt->execute(array(':field1' => $field1, ':field2' => $field2));
 

Darbo su duomenų baze užbaigimas

Korektiškai dirbant su duomenų baze, reikia uždaryti kiekvieną prisijungimą prie jos. Kitaip tariant, išvalyti šiukšles.

Tam mūsų prisijungimo kintamajam tiesiog priskiriama reikšmę null.

$dbh = null;
 

Saugumas, dirbant su DB

Atskira tema, dirbant su duomenų baze, yra saugumas. Esmė - reikia neleisti prie duomenų bazės prieiti tiems, kam nereikia. Ir kalba eina ne tik apie INSERT ir UPDATE užklausų vykdymą, bet ir kitas saugumo spragas, kurios dažniausiai susiveda į parametrų patikrinimą. Auksinė taisyklė: jeigu užklausoje yra parametrų-kintamųjų, reikia būtinai-būtinai patikrinti, kad tie parametrai yra teisingi ir nėra bandymas "nulaužti" sistemą.

Konkretūs pavyzdžiai:

  • Jeigu reikia, kad būtų įvestas skaičius nuo 1 iki 100 tai turi būti patikrinama
  • Jeigu teksto eilutė turi būti ne ilgesnė nei 50 simbolių, tai turi būti patikrinama
  • Jeigu reikia, kad eilutėje nebūtų HTML kodo... na, supratote

Tai tuos patikrinimus kiek įmanoma, reikia padaryti iš PHP pusės, dar prieš kviečiant MySQL funkcijas. Bet ir pačios PDO funkcijos tuos patikrinimus kažkiek atlieka - tam ir reikia tuos PHP kintamuosius dėti į PDO būtent per parametrus. Svarbiausia nedaryti tiesioginių užklausų, kaip tokia:

$db->exec("insert into users (user, password) values ('$user', '$password')");

Paklausite, kuo tokia užklausa yra "bloga"? Tuo, kas $user ir $password gali būti bet kas. Gali būti bet kokios teksto eilutės. Ir pastačius tam tikras teksto eilutės į SQL užklausą, ta užklausa gali tapti labai kenksminga. Geriau pademonstruosiu su kitokiu pavyzdžiu:

$db->exec("select from users where id = $id");

Atrodo nekalta užklausa, ar ne? Dabar įstatykite į ją tokį kintamąjį:

$id = "id; drop table users;";

Ką gi gauname rezultate?

$db->exec("select from users where id = id; drop table users;");

Po tokios saugumo spragos galite netekti visos savo lentelės users. VISOS. Su duomenimis. Negrįžtamai (gal ir yra serveryje atsarginės kopijos, bet čia jau kitas klausimas kaip jas atkurti). Dėl to bendras patarimas dėl saugumo - jei norite į SQL užklausą dėti PHP kintamuosius, viską darykite per PDO parametrus.



(c) 2015-2018. Visais klausimais kreipkitės povilas@laraveldaily.com