PHP EDASIJÕUDNUD RAAMAT (0)

01 - PHP ja MySQL - Sissejuhatus
Teemad

• Sissejuhatus
  
• Mis on MySQL
  
• Mis on SQL
  
• Andmebaasi haldamine
  

Sissejuhatus
Millega ma nüüd jälle hakkama sain? Nimelt otsustasin vana php mooduli lüüa vähemalt kaheks ning kirjeldada iga teema täpsemalt lahti. Esimene osa peaks olema php põhikursus, kus õpime aluseid ning selles teemas nihutame latti kõrgemale ja omandame keerulisemaid asju. Näiteks õpime kuidas siduda php andmebaasiga, kuidas saada paremini läbi vormidega, mida hakata peale sessioonidega jne. 
Alustamegi kohe andmebaasi tutvustamisega, milleks meil seda vaja on ja kuidas andmebaasi hallata.
Mis on MySQL?
Niisiis , php alused mooduli alguses paigaldasime arvutisse WAMP serveri, mis paigaldas meie arvutisse  Apache veebiserveri , MySQL andmebaasi ja Php mooduli. Kuigi tihti öeldakse MySQL kohta lihtsalt andmebaas , siis on tegemist tegelikult andmebaasihalduriga või siis kaandmebaasimootoriga. See sisaldab endas:

• andmebaasi serverit
  
• klientprogrammi andmebaaside päringuteks
  
• programme administreerimiseks
  

Andmebaase on meil vaja andmete hoidmiseks kindlas vormingus. Oluline on selle juures see, et tabeleid saaks omavahel siduda ning seepärast kutsutakse neid ka kena nimega - realtsioonandmebaas (RDBMS).
Üks populaarsemaid andmebaasimootoreid ongi MySQL. Seda arvatavasti sellepärast, et see on avatud lähtekoodiga, kiire ja töökindel. Suurimad konkurendid on sellele näiteks Oracle , PostreSQL, Microsoft SQL jt. 
Kõik need kasutavad SQL päringukeelt. Selle keele abil saavad andmebaasivälised rakendusprogrammid , nagu näiteks PHP, andmebaasiga suhelda. SQL tuleb inglisekeelsetest sõnadest Structured Query Language , mis meie keeles võiks kutsuda struktuurpäringukeeleks. Vaatame mõningaid näiteid.
Vaatab andmebaase:
?
1
SHOW DATABASES ;
Selekteerib kõik read tabelist nimega uudised:
?
1
SELECT * FROM uudised;
Andmebaasi tabeli struktuur
Kui andmebaase kirjeldada, siis kõige sarnasem on sellele Microsoft Exceli töövihik , kus andmeid hoitakse samuti  tabelites . Ning tabeli moodustavad tulbad ja read. Andmebaasi ridu nimetatakse ka kirjeteks (records). Tabelilahtreid nimetame aga andmeväljadeks (fields).
Reeglina varustatakse iga tabeli esimene veerg  unikaalse indikaatoriga (id), mida kutsutakse primaarvõtmeks. Igaks juhuks rõhutan üle, et unikaalne tähendab, et samasugust võtit tabelis enam olla ei tohi! Võtmed on just need väljad, mille abil seotakse erinevad andmetabelid. 
 
Andmete hoidmine eraldi tabelites, muudab need üksteisest sõltumatuks, võimaldades hõlpsamini teha näiteks muudatusi andmebaasi struktuuris või kasutada samu andmeid teise tabeliga sidumisel.
Andmebaasi haldus
Esimest korda andmebaasidega tööle hakkamisel šokeerib noori veebitreialeid MySQL haldamine käsurealt (command line). See tähendab, et kõik SQL laused tuleb "käsitsi" sisestada. Linuxi kasutajad kasutavad selleks Bash 'i ja Windowsi fännid Command Prompt'i.
WAMP serveri puhul on kiirem viis klikkida ikoonil ja valida sealt MySQL>MySQL console.
See avab konsooli akna paludes sisestada parool (Enter password).
Kuna WAMP on mõeldud ainult kasutaja arvutis töötamiseks, siis andmebaasi serveri root kasutajal parooli pole - vajuta Enter. Kui kõik õnnestus peaksid nägema samasugust pilti.
Anname mõned käsud ka.
?
1
SHOW DATABASES;
See näitab kõiki andmebaase.
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
+--------------------+
| Database           |
+--------------------+
| information_schema |
| a12                |
| kuulutused         |
| lasteaed           |
| leht               |
| mysql              |
| performance_schema |
| php_edasijounud    |
| probleemid         |
| teated2            |
| teha               |
| test               |
| wordpress          |
+--------------------+
13 rows in set (0.04 sec)
 Valime andmebaasi 'php_edasijounud' ning piiluma millised tabelid seal on.
?
1
2
USE php_edasijounud;
SHOW TABLES;
Tulemus
?
1
2
3
4
5
6
7
8
9
10
mysql> use php_edasijounud;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_php_edasijounud |
+---------------------------+
| kliendid                  |
| veebilehe_kasutajad       |
+---------------------------+
2 rows in set (0.04 sec)
 Konsoolist väljumiseks kirjuta EXIT ;
Andmebaasi graafiline kasutajaliides
Nendele, kel meeldib rohkem kõike visuaalselt näha, on mõeldud graafilised kasutajaliidesed:

• phpMyAdmin
  
• Navicat
  
• Webyog
  
• jt. ( http://www.webdesigneronline.co.uk/10-great-alternatives-to-phpmyadmin )
  

Kuna WAMP serveriga tuleb phpMyAdmin kenasti kaasa, siis seda me kasutamegi. Programmi käivitamiseks lisa aadress  http://localhost/phpmyadmin . Kuna parooli endiselt ei eksisteeri, siis seda ka ei küsita.
Vasakul tabelis näeme kenasti olemasolevaid andmebaase, millele klikkides kuvatakse vastavas andmebaasis olevad tabelid.
 
 
 
02 - PHP ja MySQL - Andmebaasi kasutaja ja andmebaasi loomine (Ülesanne 1)
Teemad

• andmebaasi kasutaja
  
• kasutaja õigused
  
• andmebaasi loomine
  
• tabelite loomine
  
• andmeväljatüübid
  

Eesmärk
Selles peatükis loome andmebaasi kasutaja ning edasiseks õppimiseks esialgse andmebaasi. Kasutaja loomisel vaatame, kuidas määrata erinevaid õigusi ning andmebaasi loomisel, millised on  võimalikud andmebtüübid.
Andmebaasi kasutaja loomine
Ava  http://localhost/phpmyadmin  ning kliki  Kasutajad menüüs.
 
 Avanenud menüüs peaksid nägema juba lisatud kasutajaid. Uue kasutaja lisamiseks kliki Uus kasutaja lingil.
Avanenud aknas on uue kasutaja loomine jagatud kolmeks. Esimesena tuleb kirja panna 'Sisselogimise teave':

• Kasutajanimi - väldi täpitähti ja tühikuid 
  
• Host  - ligipääsuserver, lisa siia localhost. (Kui soovid andmebaasile ligipääsu väljastpoolt serverit, siis lisa vastav IP. Lisades %, siis saab ligi igast masinast)
  
• Parool - hea parool sisaldab suuri ja väikesi tähti ning numbreid
  
• Tipi uuesti - korda parooli
  
• Genereeri parool - kasuta seda võimalust, kui endal puudub idee parooliks
  

Harjutamise eesmärgil, tegin kasutajale sama parooli, mis on kasutajanimi (juhan, juhan). Järgmine samm, 'Andmebaas kasutajale', tunneb huvi, kas soovime luua kasutajale andmebaasi. Jätame selle valikuks 'Puudub' ja vaatame õigusi - ' Globaalsed õigused':

• Andmed - määrab ära põhilised õigused andmebaasiga töötamiseks. Näiteks andmete sisestamine, väljastamine, uuendamine jne-
  
• Struktuur - lubab kasutajal teha andmebaasis struktuurseid muudatuso
  
• Administreerimine - administraatori õigused teha nö. kõike
  
• Ressursi piirangud - tegutsemise piirangud, näiteks päringute arv tunnis jms.
  

Kuna endiselt on tegemist serveriga ainult minu arvutis, siis annan julgelt kõik õigused. 
 Kasutaja loomiseks vajuta Lisa kasutaja nupule ning kõigi õigustega kasutaja peaks ilmuma nimekirja.
Andmebaasi loomine
Andmebaasi loomiseks kliki menüüs  Andmebaasid , lisa andmebaasile nimi ja kliki nupul Loo. Andmebaasi nime ja edaspidi ka tabeli ja veergude puhul, lähtu seni õpitud põhimõtetest - väldi täpitähti ja tühikuid.
Tabelite loomine
Kuna andmebaas on tühi, siis andmebaasi nimele klikkides antakse võimalus lisada esimene tabel. Lisame nime tabeli nime 'albumid' ja valime veergude arvu '5'. Kui oled sellega hakkama saanud, vajuta nupul Mine. 
 See kuvab meile lahtrid , kuhu tuleb lisada veeru nimi, tüüp ja soovitavalt ka andmeväljad suurim pikkus. Kohe allpool vaatame millised andmetüübid on võimalikud, aga tahan juhtida tähelepanu esimsele lahtrile 'id'. Nimelt on tegemist tabeli primaarvõtmega (PRIMARY), mis PEAB olema unikaalne ja sellepärast lisan valiku A_I (auto increment), mis hakkab automaatselt arvu ühe võrra suurendama . Teised andmed vaata pildilt .
Kui valmis, vajuta nuppu  Salvesta !
Andmevälja tüübid
Nagu eelmisest tegevusest võisite aru saada, on meil kindlasti vaja määrata andmevälja tüüp. See tähenda, et kui oled lahtrisse lubanud ainult numbrid , siis sinna teksti salvestada ei saa jne. Andmevälja tüübid jagunevad kolmeks grupiks :

• tekst
  
• arvud
  
• kuupäev ja aeg
  

Tekstitüübid

• CHAR - kindla pikkusega, näiteks isikukood; kuni 255 märki (1bait märgi kohta)
  
• VARCHAR - muutuva pikkusega, näiteks nimed; kuni  65535 märki
  
• TEXT - pikkade tekstide jaoks, näiteks uudised, artiklid jne; kuni 65535 märki
  
• BINARY - lubab lisada kõiki andmetüüpe ja andmeid ei tõlgita (nt tekstiks või numbriks), kuni 255 märki
  
• VARBINARY - kuni 65535 märki
  
• BLOB - binaarse info hoidmiseks, näiteks pildid; 65535  baiti / 64kB
  
• ENUM ja SET - on omavahel peaaegu sarnased, lubades kindlat piirangut; näiteks ENUM lubab ainult JAH ja EI või MEES ja NAINE jne; SET puhul saab salvestada rohkem väärtusi; mõlemad saavad hakkama 65535 märgiga
  

Arvtüübid

• INT - täisarv vahemikus –2147483648 kuni 2147483647. Kui andmevälja atribuut on tähistatud UNSIGNED, siis on vahemikuks 0 kuni 4294967295
  
• DECIMAL - reaalarvu salvestamiseks; lisades pikkuse 5,2 siis 3 arvu komast jääb vasakule ja 2 paremale. Väärtusi ei ümmardata. Kasuta rahaväärtuste salvestamiseks.
  
• FLOAT - reaalarvu salvestamiseks; lisades pikkuse 5,2 siis lubatakse 5 arvu komast vasakule ja 2 paremale. Väärtused ümardatakse.
  
• DOUBLE ja REAL - reaalarvude salvestamiseks; sarnanevad FLOAT tüübile
  
• BIT - hoiab maksimaalselt ühte baiti, seega väärtused saavad olla 0 (või NULL) ja 1
  
• BOOLEAN - loogilised väärtused TRUE või FALSE
  
• SERIAL - sama mis BIGINT
  

Kuupäev ja aeg

• DATE - kuupäev 1 päevase täpsusega; andmeid hoitakse kujul YYYY-MM-DD. Võimalikud väärtused on vahemikus 1000-01-01 kuni 9999-12-31
  
• DATETIME - kuupäev ja kellaaeg täpsus 3 millisekundit; andmeid hoitakse kujul YYYY-MM-DD HH:MM:SS. Võimalikud väärtused on vahemikus 1000-01-01 00:00:00 kuni 9999-12-31 23:59:59.
  
• TIMESTAMP - sarnane DATETIME funktsioonile, kuid võimaldab edastada väärtusi vahemikus 1970 kuni 2038
  
• TIME - kellaaeg 100 nanosekundilise täpsusega; hoiab aega HH:MM:SS formaadis ja vahemikus -838:59:59 kuni 838:59:59
  
• YEAR - kahekohalised aastad võivad olla vahemikus 1970 kuni 2069. Neljakohalised 1901 kuni 2155
  

 Lisaks eelpool näidatule on mõningaid andmetüüpe veel aga neid vaatate vajadusel iseseisvalt. Üldselt peaksime hakkama saama nelja tüübiga:

• INT
  
• DECIMAL
  
• DATETIME
  
• VARCHAR
  

Andmete lisamine andmebaasi
Andmete lisamiseks tabelisse kliki soovitud tabeli nimel. Minu näite puhul tuleb jälgda, et olen endiselt andmebaasis 'muusikapood' ja seejärel klikin tabelil 'albumid'. Nüüd muutuks aktiivseks Lisa menüü , millele klikkides kuvatakse tühjad lahtrid.
id lahter jäta täitmata, sest see täidab ennast ise aga teistesse lisa mõned artistide albumid ja hind. Hinna puhul jälgi, et komakoht tähistatakse punktiga . Salvestamine saab olla edukas, kui klikid nupul Mine. Lisatud albumeid näed, kui klikid menüüs Vaata.
 
See on ka vaade, kus saad teha muudatusi andmetes või vajadusel kustutada . 
 
Ülesanne 1

• Loo andmebaasi oma kasutaja
  
• Loo andmebaas muusikapood
  
• Lisa 5 väljaga tabel - id, artist, album, aasta, hind
  
• Täida tabel andmetega (5tk)
  

03 - PHP ja MySQL - PHP sidumine MySQL andmebaasiga
Teemad

• mysqli_connect
  
• mysqli_query
  
• mysqli_fetch_array
  
• mysqli_fetch_row
  
• mysqli_fetch_assoc
  
• mysqli_free_result
  
• mysqli_close
  

Sissejuhatus
Selles peatükis ühendame ära PHP andmebaasiga. Ühendusviise on mitu aga põhimõte jääb samams. Ja et veenduda oma edukuses, teeme ühe lihtsa päringu juba olemasolevasse andmebaasi.
PHP ühendamine andmebaasiga
PHP võimaldab meile kolme viisi MySQL andmebaasiga ühendamiseks :

• MySQL Connect
  
• MySQL Improved Connect (MySQLi)
  
• PHP Data Objects (PDO)
  

Nendest  PHP moodulitest esimene on kõige vanem võimalus ning hetkel seda enam ei arendata. Kuna peagi võetakse see üldse rajalt maha, siis selle peale aega ei raiska. Selle asemel suuna oma kogu tähelepanu MySQLi kaudu ühendamise peale.
Kolmas võimalus, PDO, tasuks ka kaalumist ja seda just siis, kui sul on kasutamiseks näiteks Microsoft SQL Server , Oracle vms. Pean tunnistama, et pole ise seda praktikas nagu kasutanud, aga selle eesmärgiks peaks olema võimalus kirjutada SQL päringuid platvormist sõltumata. Kel suurem huvi selle vastu, siis hetkel tundub parim link olevat vahvel.net foorumis. 
Pöördume MySQLi mooduli juurde tagasi. Uue arendusga muudeti see turvalisemaks ning võimaldab omakorda kahte meetodit ühendamiseks:

• protseduuriline
  
• objektorienteeritud
  

Protseduuriline ühendamine andmebaasiga
Mistahes viisil ühendusse astumisel andmebaasiga on meil reeglina neli sammu:

ühendamine

päring

tulemuse väljastamine

ühenduse sulgemine
Kindlasti mõjub tervisele hästi, kui kohe alustame ka mõningate konrollidega - näiteks kas ühendus toimus ja kas päring oli edukas.
Ühendamine
Ühendamisel tuleb ära määrata andmebaasi server, andmebaasi nimi, kasutaja ja salasõna.
?
1
2
3
4
5
6
7
Nüüd oleme valmis mysqli_connect() abil ühendust looma. Ja lisan ka kohe kontrolli, mis teatab ebaõnnestunud kontaktist andmebaasiga.
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
Kui kood käivitada, siis eduka ühenduse puhul näeme tühja valget dokumenti. Seejärel saame teha päringu andmebaasi, näiteks 'SELECT * FROM albumid', mis valib kõik veerud (*) tabelist albumid. Päringu tegemisel ei tehta suurtel ja väikestel tähtedel vahet, kuigi hea tava järgi kirjutatakse mysql käsud suurte tähtedega. Kui päring on muutujasse lisatud, peame selle andmebaasi poole teele saatma . Teeme seda mysql_query() funktsiooniga.
?
1
2
3
//päring
$paring = 'SELECT * FROM albumid';
$valjund = mysqli_query($yhendus, $paring);
Nonii, oleme saanud ühenduse ning oma soovid teele saatnud. Vaikimisi saadab andmebaas meile ainult esimese rea vastuseks . Aga meie soovime kõiki ridu ja kasutame selleks  while () tsüklit, mis väljastab iga rea, kuni on ka viimane väljastatud.
?
1
2
3
while($rida = mysqli_fetch_row($valjund)){
    var_dump($rida);
Kasutasin väljundis  var_dump() funktsiooni, et näidata, et iga rida on massiiv .
See aga annab meil väljundit kenasti soovitud kujul vormindada .
?
1
2
3
4
5
6
while($rida = mysqli_fetch_row($valjund)){
    //var_dump($rida);
     echo 'Album: '.$rida[1].' - '.$rida[2].'
     echo 'Aasta: '.$rida[3].'
    echo ' Žanr : '.$rida[4].'
Ja lõpus on oluline päring ja ühendus antud andmebaasiga katkestada. Ja seda mysqli_free_result()ja mysqli_close() funktsioonidega.
?
1
2
mysqli_free_result($valjund);
mysqli_close($yhendus);
Ja nüüd kogu protseduuriline koos:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Päringu väljastamine
Enne kui vaatame järgmist ühendamise võimalust, tahan tähelepanu juhtida viisist, kuidas andmebaasist andmeid väljastatakse. Hetkel kasutasime funktsiooni mysqli_fetch_row() ja nagu nägime, siis väljastatakse meile tavaline massiiv, kus elemendid nummerdatakse 0-4. Teine võimalus, mida päris tihti kasutatakse on mysqli_fetch_assoc(). See on sama tubli funktsioon, kui eelmine , ainult, et väärtustele antakse veeru nimed.
See meetod on isegi mugavam, sest näiteks artisti nime saamiseks pidite te teadma, kus see rida on - näiteks $rida[1]. Aga selle viisi puhul kasutate veeru nimi ning kood on loetavam - näiteks $rida['artist'].
Kolmas viis on väljastada mõlemal viisil, nii indeks kui nimeline. Kasuta selleksmysqli_fetch_array().
 
Sellisel viisil jäävad kasutajal vabad käed nii ühe kui teise meetodi kasutamiseks.
Objektorienteeritud ühendamine andmebaasiga
Objektorienteeritud lähenemine andmebaasiga suhtesse astumisel on väga sarnane protseduurilisele.
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
OOP lähenemist veebirakenduste loomisel vaatame mõnes hilisemas peatükis, kuid kui koodi vaadata, siis sarnandeb see väga eelmisele.
Andmebaasi seadete hoidmine eraldi failis
Selleks, et ei sa ei pilluks oma kasutajanime ja paroole mööda dokumenti laiali, on hea mõte hoida neid eraldi failis. Tavaliselt on selle faili nimeks näiteks config.php, config.inc vms. Ja  include ()funktsiooniga saad selle oma dokumendile külge pookida.
?
1
2
3
4
5
6
7
8
9
10
11
12
 
04 - PHP ja MySQL - SQL päringud: andmete lugemine; SELECT (Ülesanne 2)
Teemad

• USE
  
• SHOW
  
• SELECT
  
• WHERE
  
• ORDER BY
  
• agregaatfunktsioonid
  

Sissejuhatus
SQL päringulaused saab laias laastus jagada kaheks grupiks:

• DML (Data Manipulation Language) - andmete manipuleerimiskeel. See osa teostab erinevaid päringuid andmebaasile:
  
  • SELECT - loeb
    
  • UPDATE - uuendab
    
  • DELETE - kustutab
    
  • INSERT INTO - lisab 
    
• DDL (Data Definition Language) - andmete defineerimiskeel. See osa võimaldab manipuleerida andmebaasi endaga:
  
  • CREATE DATABASE - loob uue andmebaasi
    
  • ALTER DATABASE - andmebaasi muutmine
    
  • CREATE TABLE - tabeli loomine
    
  • ALTER TABLE - tabeli muutmine
    
  • DROP TABLE - kustutab tabeli
    
  • CREATE INDEX - loob unikaalse indeksi
    
  • DROP INDEX - eemaldab indeksi
    

Lisaks nendele põhilistele lausetele on terve hunnik toetavaid klausleid, mis lubavad seada erinevaid tingimusi, sorteerida ja muuta andmebaase turvalisemaks. Järgmised peatükid ongi eelkõige DML suunal ehk siis vaatame kuidas saame andmed kätte, kuidas lisada, kustutada ning uuendada . 
MySQL Console
Enne kui asume koodirünnakut PHP dokumendis, on hea mõte oma andmebaasi päringud konsooliaknas läbi proovida. See vähendab tekkinud vigade otsimiseks kuluvat aega. Selleks on WAMP'is vähamalt kaks võimalust. Esimene neist on MySQL Console (leiad WAMP>MySQL ikoonile klikkides). Nagu mäletate, siis vaikimisi parooli pole ja vajuta Enter. Nüüd peaks see kasutamiseks valmis olema.
Vaatame millised on meie andmebaasid.
?
1
mysql> SHOW DATABASES;
Andmebaasi aktiveerimiseks ja tabelite nägemiseks kirjuta järgmised read. Kumbki lause kirjuta eraldi.
?
1
2
mysql> USE muusikapood;
mysql> SHOW tables;
Nagu näha on andmebaasis 'muusikapood' üks andmete tabel nimega 'albumid'. Nii, see andmebaas on meie loomkatsetusteks valmis.
phpMyAdmin SQL Console
See osa on nüüd neile, kes tahavad graafilist osa näha. Ehk ava localhost/phpmyadmin, selekteeri soovitud andmebaas.
 
Ja seejärel kliki SQL nupul.

Andmete väljastamine SELECT abil
Andmete kättesaamiseks kasutatakse päringulauset SELECT. Kirjutame lause, mis selekteerib kõik (*) veerud tabelist 'albumid'.
?
1
SELECT * FROM albumid;
Kirjutades päringu phpMyAdmin SQL konsooli on esilagu seda infot natuke liiga palju (hiljem harjub ära:) ). phpMyAdmin lisab päringule automaatselt piirangu LIMIT 0,30. Hetkel ära tee sellest välja.
Kõikide veergude selekteerimine pole alati hea mõte, eriti kui on väga suur tabel ja reaalselt ei ole sul kõiki andmeid vaja kuvada. Sel juhul tuleb teil täpsustada, milliseid veergusid vaja on, nimed eralda komaga . Valime näiteks ainult artistid ja albumite pealkirjad .
?
1
SELECT artist,album FROM albumid;
Mõningad reeglid päringute kirjutamisel :

• suurtel ja väikestel tähtedel vahet ei tehta
  
• kokkuleppeliselt kirjutatakse päringulaused suurte tähtedega
  
• lause lõpetab semikoolon(;), see tähendab, et lihtsamaks arusaamiseks võib koodi kirjutada mitmele reale, mida näeme allpool
  
• tabeli ja veerunimede puhul ei tohiks tühikuid kasutada, aga kui seda on ikkagi tehtud, siis tuleb see nimi panna kantsulude vahele N: [eesti artistid]
  

Andmete filtreerimine
Filtreerimise abil saame päringule anda täpsustavaid parameetreid, näiteks soovime andmebaasist saada ainult 2009 aasta albumeid. Kasutame selleks WHERE klauslit.
?
1
SELECT * FROM albumid WHERE aasta=2009;
Kasutatavad võrdlusoperaatorid on täpselt samad, mis igal pool ning tekstilised väärtused tuleb ikka jutumärkidega lisada.
Siinkohal on SQL keel täiendatud kolme operaatoriga:

• BETWEEN - vahemik
  
• NOT BETWEEN - vahemikust väljas
  
• LIKE - sarnased
  
• NOT LIKE - ei ole sarnased
  
• IN - mitme väärtuse lisamiseks
  

 Võtame näiteks kõik kirjed , mis jäävad aastate 2010-2013 vahele.
?
1
2
3
4
SELECT *
FROM albumid
WHERE aasta
BETWEEN 2010 AND 2013;
Või võtame välja näiteks kaks artisti - 50 Cent ja Jamelia.
?
1
2
3
4
SELECT *
FROM albumid
WHERE artist
IN('50 Cent','Jamelia');
Kui filtreid on mitu, siis võid ka julgelt juba PHP'st tuntud loogikaoperaatoreid AND ja OR kasutada. Näiteks kõik 2009 albumid JA mille hind on suurem-või-võrdne 15€.
?
1
2
3
SELECT *
FROM albumid
WHERE aasta=2009 AND hind>=15;
Andmete sorteerimine
Andmete sorteerimiseks kasutame ORDER BY klauslit ja mistahes veeru nime tabelist. Sorteerime albumid näiteks hinna järgi.
?
1
SELECT artist,album FROM albumid ORDER BY hind;
 
Vaikimisi sorteeritakse kõik kasvavalt (ASC). Selleks, et andmed kuvataks kahanevalt, kasuta  DESC  parameetrit.
?
1
SELECT artist,album FROM albumid ORDER BY hind DESC;
Mitme veeru järgi sorteerides, tuleb need eraldada komaga. Järgmises näites sorteerime alguses aasta ja siis albumi järgi.
Loodud näites üritasin näidata, et kuidas võiks üha pikemaid päringuid mitmele reale kirja panna. Uuele reale liikumiseks vajuta lihtsalt Enter klahvi ning kui eesmärk kirjas, lisa semikoolon.
 SQL agregaatfunktsioonid
Ei taha esimest kokkupuudet andmebaasidega väga keeruliseks ajada aga pressin siia lõppu veel SQL agregaatfunktsioonid, mille abil saad hõlpsasti võtta keskmist, lugeda kokku, leida suurimat jne. 

• AVG() - aritmeetiline keskmine
  
• COUNT () - ridade arv
  
• FIRST () - esimene kirje
  
• LAST() - viimane kirje
  
• MAX() - suurim väärtus
  
• MIN() - kõige väiksem väärtus
  
• SUM() - arvude summa
  

Panen hindade keskmise, kõige kallima ja loen kokku kõik read.
?
1
2
SELECT AVG(hind), MAX(hind), COUNT(*)
FROM albumid;
 
Eespool hirmutasin korraks pikkade päringutega ja soovitasin iga käsku alustada uue rea algusest. Teine nn. optimeerimise võimalus on anda veergudele lühikesed või loogilised nimed ja seda AS abil.
?
1
2
3
4
SELECT
  AVG(hind) AS 'Keskmine hind',
  COUNT(*) AS  'Albumeid kokku'
FROM albumid;
SQL päringute lisamine PHP dokumenti
Kuigi me eelmises peatükis vaatasime kuidas ja kuhu me SQL päringu php's kirjutasime, siis antud hetkel ei tee kordamine paha. Väljastame näiteks viimase õpitud päringu. 
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
//päring
$paring = '
    SELECT
    AVG(hind) AS "Keskmine hind",
    COUNT(*) AS "Albumeid kokku"
    FROM albumid
//mysql käsu saatmine andmebaasile
$valjund = mysqli_query($yhendus, $paring);    
//väljastamine
while($rida = mysqli_fetch_assoc($valjund)){
    printf("Keskmine hind: %0.2f eur
", $rida['Keskmine hind']);
    printf("Albumeid kokku: %d tk
", $rida['Albumeid kokku']);
Tulemuseks järgmised kaks rida:
?
1
2
Keskmine hind: 15.15 eur
Albumeid kokku: 5 tk
Kasutaja dünaamilised päringud
Eelmised näited tegid täpselt seda, mida sina "palusid" teha. Aga muudame päringud sõltuvaks kasutajast. Teeme näiteks otsingukasti, kuhu saab sisestada sõna ning vastavalt sellele tehakse päring. Loome ühe lihtsa vormi ühe tekstivälja ja nupuga.
?
1
2
3
4
    Otsing
    
Edasi oleks vaja kontrollida, kas kasutaja on enne nupule vajutamist midagi lisanud. Kasutame selleks !empty() funktsiooni, mis käivitub siis, kui kasutaja on midagi sisestanud . Kui sa pole kindel, kas vorm töötab, siis võid $otsi tulemuse vahepeal väljastada.
?
1
2
3
4
5
Poogime külge andmebaasi ühenduse ning loome päringu, mis otsib vastust 'artist' veerust.
?
1
2
3
4
5
6
7
8
9
10
Siit on juba asi tuttav, tuleb päring saata MySQL serverile ja tulemus väljastada.
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
    Otsing
    
Sisestades lahtrisse artisiti nime ja see on andmebaasis olemas, peaksime nägema tulemust.
Hetkel toimub otsing üks-ühele ehk otsitakse täpselt seda, mida kasutaja kirjutas. Me küll jätsime vahele sellise toreda asja nagu metamärgid (wildcards), mis lubavad defineerida, kuidas sõna hakkab, lõppeb jne. Ning need töötavad LIKE operaatoriga. Näiteks %-märk. Lisades mi%, siis otsitakse sõnu, mis hakkavad mi'ga - mina, miil , miks jne. Samas ei leita sõnu nagu lumi ja mai. Meie lisame näites nii, ette kui järgi, mis lubab otsida tähemärke ka sõna seest.
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
    Otsing
    
Nüüd saan sisestada otsingukasti tähe o ja leitakse kõik artistid, kelle nime sees leidub antud täht.
Turvalised vormid
Me pole siiani vaadanud, kuidas kaitsta end pahade ja rumalate inimeste eest. Mõtlen siinkohal seda, et kaitsmata vormi saab kirjutada ükskõik mida. Ja see omakorda tähendab, et kasutaja saab sinu lehele postitada näiteks "pahasid" pilte, kolada ringi sinu failides, teha andmebaasiga mida iganes hing ihkab jne. Kõige lihtsam on näiteks sisestada pildi kood.
Kõigepealt võiks puhastada kasutaja otsingusõna ülearustest tühikutest.
?
1
$otsi = trim ($otsi);
Ega tühikute kärpimine , turvalisust väga vähendagi, vaid muudab sõna "ilusaks". Siit edasi keelame igasugused HTML märgendid ja seda htmlspecialchars() funktsiooni abil, mis muudab need erimärkideks:

• '&' muudetakse '&'
  
• '"' muudetakse '& quot ;'
  
• "'" muudetakse'''
  
• '' muudetakse '>'
  

Kui nüüd sisestada sama pildi kood, siis tekitatakse sellest järgmine rida:
?
1
http://www.ilumaailm.ee/pics/main/Pilt_ytleb7_092057.jpg"> ;
 Pisike täiendus
Pisike täiendus veel :) Ehk, kui otsitavale sõnale vastet ei leita, siis võiks sellest ikka märku anda. Mõte on kasutada mysqli_num_rows() funktsiooni, mis loeb kokku näidatud päringu vastused. Ning kui see on 0, siis väljastame vastava lause.
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
    Otsing
    
Ülesanne 2
Sinu ülesandeks on teostada erinevaid andmebaasi päringuid ning väljastada need php's. Kõik päringud teeme 'albumid' tabelis ning varusta iga tabel vastava pealkirjaga. Soovitan päringud enne konsoolis läbi teha

• Koosta php dokument, mis poogib ennast andmebaasi 'muusikapood' külge. NB! Andmebaasi ühendus hoia eraldifailis config.php
  
• Väljasta kogu 'albumid' sisu ridade kaupa
  
• Väljasta tabelist ainult artist ja album read, kusjuures sorteeri kasvavalt artisti järgi
  
• Väljasta tabelist ainult artist ja album read, mille aasta on 2010 ja uuemad
  
• Väljasta kui palju erinevaid albumeid on andmebaasis, mis on nende keskmine hind ja koguväärtus (summa)
  
• Väljasta kõige vanema albumi nimed
  
• Väljasta albumid, mille hind on kogu keskmisest suurem
  
• Loo otsingukast, mis lubab valida, kas otsing toimub artistide või albumite veerust.
  

 
05 - PHP ja MySQL - SQL päringud: andmete lisamine; INSERT INTO
Teemad

• INSERT INTO
  
• mysqli_affected_rows
  

Sissejuhatus
Selles peatükis õpime kuidas lisada uusi kirjeid andmebaasi, kasutades selleks INSERT INTO päringulauset. Vaatame nii ühe kui mitme rea korraga salvestamist andmebaasi.
Ühe kirje lisamine
Alustame jällegi loomulikult konsoolist. Kui seal asjad töötavad, siis saame need ka php's tööle. Põhimõte siis selline, et kõigepealt tuleb valida tabel ja siis soovitud väärtused.
?
1
2
INSERT INTO albumid
VALUES (NULL, 'Sodom', 'Epitome of Torture ', 2013, 15.99);
Väärtused lisame sulgude vahele ja eraldame teineteisest komadega . Kuna tabeli esimene ID on meil automaatne, siis selle väärtuse lisame NULL ehk mitte midagi.
Siinkohal saaks ka niimoodi , et selekteerime kindlad veerud, kuhu soovime andmeid lisada.
?
1
2
INSERT INTO albumid(artist,album,aasta,hind)
VALUES (' Bill Withers', ' Original Album Classics', 2013, 17.99);
Mitme kirje lisamine
Kindlasti ei pea kirjeid lisaama ükshaaval.
?
1
2
3
4
INSERT INTO albumid(artist,album,aasta,hind)
VALUES ('Coolio','The Collection ',2012,6.99),
('Taio Cruz','Rokstarr',2010,8.95),
('Cypress Hill',' Rise Up',2010,9);
Vormi kaudu andmebaasi lisamine
Vaatame, kuidas läbi HTML vormi ja PHP abil salvestada päringud andmebaasi. Tekitame kõigepealt ühe vormi.
?
1
2
3
4
5
6
7
8
9
10

Uue albumi lisamine

    Artist:
    Album:
    Aasta:
    Hind:
    
 Antud koodiga tegime vormi, kus kõik väljad peavad olema täidetud ning sisu protsessib sama fail ise.
Kuigi HTML kontrollib väljade täitmist ise, siis ei saa me sellele lootma jääda ja alustame php koodi väljade kontrolliga . Kui kasutaja on ikka kõik väljad kenasti täitnud, siis eemaldame kasutaja päringust ülearused tühikud ja html märgendid.
 
?
1
2
3
4
5
6
7
8
9
10
11
12
13
Kui kasutaja andmed on turvaliselt muutujates, siis saame teha SQL päringu andmebaasi lisamiseks. Jälgi, kuidas on lisatud väljade väärtused!
?
1
2
3
//päring
    $paring = "INSERT INTO albumid(artist,album,aasta,hind) VALUES ('".$artist."','".$album."','".$aasta."','".$hind."')";
    $valjund = mysqli_query($yhendus, $paring);
Nonii, kui päring tehtud ja andmebaasi poole saadetud , siis võiks väljastada teate, kas andmete lisamine õnnestus. Kasutame funktsiooni mysqli_affected_rows(), mis kontrollib mitu pärigut tehti. Kuna me lisame ühe rea korraga, siis saamegi kontrollida kas üks rida lisati või mitte.
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
//päringu vastuste arv
    $tulemus = mysqli_affected_rows($yhendus);
    if ($tulemus == 1) elseKõik kood koos:
Selle koodi tulemusena peaksid nägema samasugust tabelit, kus lingiks on rea unikaalne ID.
Kuna link on suunatud samasse faili, nimega 03_kustutamine.php, siis failinime muutmisel see enam ei töötaks. Selleks on hea kasutada $_SERVER['PHP_SELF'], mis on aktiivse faili nimi.
?
1
2
3
4
5
6
7
8
...
echo '
                '.$rida['artist'].'
                '.$rida['album'].'
                '.$rida['aasta'].'
                kustuta
            ';
...
Nüüd kui lingile vajutada , siis ID väärtus saadetakse GET meetodiga brauseri aadressiribale. Sealt edasi saame selle kätte juba õitud viisil ning lisame selle väärtuse kustutamise päringusse.
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Pärast rea kustutamist ei taha tabel end kohe uuendada. Seepärast võiks eduka kustutamise lahtrisse lisada automaatse värskenduse. Mille ma juba eelmises koodinäites lisasin.
?
1
echo '';
 
 
07 - PHP ja MySQL - SQL päringud: andmete uuendamine; UPDATE (Ülesanne 3)
Teemad

• UPDATE
  

Sissejuhatus
Andmebaasi kirjed oskame nüüdseks kuvada, lisada ja kustutada. Üle jääb viimane - andmete muutmine. Seda kõike kirjeldatakse mõistega CRUD ehk

• Create (INSERT INTO)
  
• Read (SELECT)
  
• Update (UPDATE)
  
• Delete (DELETE FROM)
  

Andmete uuendamine
Nagu ikka alustame konsoolist. Mul on hetkeks andmebaasi jäänud 10 kirjet.
Soovime muuta näiteks viimase kirje aastaarvu. Selleks tuleb valida tabel ja seejärel soovitud veerg. Oluline on täpsustada milliseid kirjed tuleb muuta. Kui see ära jätta, siis muudetakse ära kogu veerg.
?
1
UPDATE albumid SET aasta=2011 WHERE id=10;
Selle tulemusena on muudetud viimase kirje aasta.
Mitme veeru muutmiseks, eralda need lihtsalt komaga.
?
1
2
3
UPDATE albumid
SET artist='New Coolio', album=' Best of 2012'
WHERE id=8;
Andmebaasi kirjete uuendamine läbi PHP
Kasutame eelmises peatükis koostatud andmete kustutamise tabelit ja täiendame seda uuendamise linkidega. Seekord suuname muutmise uude dokumenti ja siis kui muudetud, suuname kasutaja tagasi (iseseivalt võiksite kustutamise koodi ka uude dokumenti viia).
?
1
2
3
4
5
6
7
8
9
while($rida = mysqli_fetch_assoc($valjund)){
    echo '
            '.$rida['artist'].'
            '.$rida['album'].'
            '.$rida['aasta'].'
            kustuta
            muuda
        ';
 
Nüüd on olemas link, mis avab uue dokumendi ja saadab aadressiribale muutuja id vastava väärtusega. Loome siia sama vormi, mille tegime lisamise osas ning haarame väljade väärtused andmebaasist, et kasutajal oleks lihtsam muudatusi teha.
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25