PHP ja MySQL

1. PHP ja MySQL Verkkotekniikan jatkokurssi Kevät 2003 Ville Seppänen (rissepp@cc.jyu.fi)

2. MySQL - yleistä • Yleisimmin käytetty avoimen lähdekoodin relaatiotietokantapalvelin, www.mysql.com • Neljä jakelumuotoa • MySQL Standard: Perusversio, nykyisin varustettu ACID-transaktioita tukevalla InnoDB-moottorilla • MySQL Max: Uusimmilla ja laajimmilla ominaisuuksilla varustettu jakelu, sisältää mm. Berkeley-moottorin, SSL-tuen, tauluen jaon useaan tiedostoon • MySQL Pro: Standard-jakelu kaupallisella lisenssillä • MySQL Classic: Karsittu versio ilman InnoDB:tä

3. MySQL - yleistä • Lukuisille järjestelmäalustoille, mm. Linux, Microsoft Windows, Novell NetWare, sekä useimmat vapaat ja kaupalliset Unixit • Alusta lähtien keskittynyt suorituskykyyn ja nopeuteen • Edellisestä johtuen aiemmat versiot ovat tarjonneet epätäydellisen SQL-tuen (osittainen ANSI SQL 99- tuki; mm. ei mahdollisuutta käyttää alikyselyjä) • ja puutteellisen transaktiotuen (oletustaulutyyppi MyISAM ei tue transaktioita • eivätkä mahdollistaneet viiteavainten käyttöä

4. MySQL - yleistä • Versiossa 4. SQL-tukea parannettu (mm. UNION) ja InnoDB mahdollistaan transaktiot (tosin vain rivitason lukituksella) sekä viiteavaimet • Ohjelmointikielille toteutettuja ajureita mm. MySQL Connector/J (Java), MySQL Connector/C++, MySQL Connector ODBC • Myös useimmissa skriptikielissä tuki MySQL-tietokantojen käsittelylle

5. Relaatiorakenne • Relaatioskeema R(A1, A2, …, An), jossa R on relaatio ja attribuutti Ai on kuvattavaan asiaan liitetty ominaisuus • Relaatio ilmenee joukkona skeeman mukaisia attribuutti-arvo-ryhmiä • Yleisimmin relaatio esitetään taulukkomuodossa, jossa attribuutit sarakkeina (columns) ja kohteet riveinä (tuples) • Jokainen rivi tulee voidayksilöidä jonkin attrib.perusteella (avain)

6. SQL • SQL (Structured Query Language) on kieli relaatiomuotoisen tiedon käsittelyyn sekä tietokannan rakenteen määrittelyyn • Periaatteessa standardi, mutta useimmissa tietokantatuotteissa mukana epästandardeja valmistajakohtaisia laajennuksia • SQL:n operaatiot voidaan jakaa mutating (tietosisältöä muuttavat, esim. insert, update, delete) ja non-mutating (sisältöä muuttamattomat, esim. select) operaatioihin

7. MySQL ja PHP • Yleinen toteutusalusta dynaamisille websovelluksille • PHP sisältää suuren joukon MySQL-tietokantojen käsittelyyn tarkoitettuja funktioita http://www.php.net/manual/en/ref.mysql.php • MySQL-kantoja mahdollista käyttää myös tietokantaliittymän abstrahoivan dbx-moduulin kautta, http://www.php.net/manual/en/ref.dbx.php

8. 1. Pyytää dokumenttia, jokasisältää PHP-skriptiä 2. Suorittaa skriptin ja lähettää tietokantaoperaatiot tietokantapalvelimen suoritettavaksi Selain WWW-palvelin people.cc.jyu.fi MySQL-palvelin elli.it.jyu.fi 4. Purkaa resurssitunnisteen, muotoileetuoloksen ja palauttaa HTML-dokumentin 3. Suorittaa tietokantaoper. ja palauttaatuloksen resurssitunnisteena MySQL ja PHP • Toimintaperiaate

9. Tietokantayhteyden muodost. • Ennen kannan käyttämistä, tietokantayhteys täytyy avata skriptissä:$db_a = mysql_connect(“palvelin”, “kayttunnus”, “salasana”); • Onnistuessaan palauttaa linkkitunnisteen, jota voidaan käyttää tietokantayhteyteen viitattessa. Epäonnistuessaan palauttaa FALSE:$db_b = mysql_connect(“localhost”, “kayttunnus”, “salasana”) or die(mysql_error());

10. Tietokantayhteyden muodost. • Tyypillisesti samalla palvelimella on useita tietokantoja. Seuraavaksi valitaan käytettävä kanta: mysql_select_db(“omakanta”, $db_a) or die(mysql_error()); • Tietokantayhteys suljetaan automaattisesti kun skriptin suorittaminen loppuu. Se voidaan sulkea myös erikseen käyttämällä:mysql_close($db_b);

11. Tietokantaoperaatioiden suorit. • mysql_query() lähettää SQL-lauseen tietokantapalvelimelle • SELECT,SHOW,EXPLAIN ja DESCRIBE operaatioilla onnistunut kysely palauttaa resurssitunnisteen ja muun tyyppisillä kyselyillä TRUE • Linkkitunnistetta voidaan käyttää ohjaamaan kysely haluttuun kantaan. Mikäli kysely suoritetaan ilman tunnistetta, käytetään viimeksi avattua tietokantayhteyttä

12. Tietokantaoperaatioiden suorit. $query_a = “SELECT * FROM oma_taulu”; $result = mysql_query($query_a, $db_b) or die(mysql_error()); $query_b = “DELETE FROM oma_taulu WHERE nimi LIKE ‘T%’”; mysql_query($query_b, $db_a) or die(mysql_error());

13. Tulosten käsitteleminen • mysql_result() palautaa yhden solun arvon: optionaalinen mysql_result($resurssitunn, rivinumero, attribnimi); • mysql_fetch_row() poimii tulosjoukosta yhden rivin ja muodostaa siitä numeerisia tunnisteita käyttävän taulukon: $no_of_fields = mysql_num_fields($result);while($row = mysql_fetch_array($result)){ for($i=0; $i<=$no_of_fields; $i++) { echo “$row[$i]<br />”; }}

14. Tulosten käsitteleminen • mysql_fetch_array() poimii tulosjoukosta yhden rivin ja muodostaa taulukon, jossa sekä numeeriset että attribuuttien nimien mukaan nimetyt tunnisteet:while($row = mysql_fetch_array($result)){ echo “$row[Nimi], $row[Väri]<br />”;}

15. Esimerkkejä SQL-kyselyistä SELECT etunimi, sukunimi FROM users WHERE kaupunki LIKE ‘Jyväskylä’ OR postinro = 40500; INSERT INTO users (etunimi, sukunimi, kaupunki, postinro) VALUES (‘Kauko’, ‘Putki’, ‘Jyväskylä’, 40500); UPDATE users SET status = ‘active’ WHERE id = ‘11’; INSERT ei salli WHERE:n käyttöä, joten se lisää aina uuden rivin. UPDATEa voidaan käyttää yksittäisten arvojen lisäämiseen, jolloin puuttuvat korvataan NULLilla. DELETE FROM users WHERE status = ‘inactive’;

16. Esimerkkejä SQL-kyselyistä • ORDER ja LIMIT SELECT * FROM products ORDER BY product_name ASC LIMIT 20;Valitaan 20 ensimmäistä riviä products taulusta, järjestetään product_namen perusteella nousevaan järjestykseen SELECT * FROM products LIMIT 50, 60;Valitaan rivit 50 - 60 • GROUP SELECT COUNT(*) FROM users GROUP BY status;Lasketaan rivien lukum. users taulussa, ryhmitellään statuksen mukaan

17. Esimerkkejä SQL-kyselyistä • Useamman taulun käyttely SELECT u.firstname, u.lastname, o.order_idFROM orders AS o, users AS uWHERE o.user_id = u.id AND u.id = 11; • Muuttujien käyttö SELECT COUNT(*) FROM users WHERE login = ‘$login’ AND password = ‘$password’;SELECT * FROM products WHERE $field = ‘$keyword’;

18. Käyttäjän syötteen tarkistaminen • Käyttäjältä tulevan syötteen päästäminen sellaisenaan tietokantakyselyihin on melkoinen riski. Helppo tapa suodattaa ei-toivotut merkit pois on addslashes()funktio, joka lisää \ -merkin ‘, ‘’, \ ja NULineteen. Lisätyt escape-merkit voidaan jälleen poistaa käyttämällä stripslashes() funktiota $string = addslashes($string);$string = stripslashes($string);

19. Käyttäjän syötteen tarkistaminen • HTML-tagit voidaan poistaa strip_tags() funktiolla. Lisäparametrina voidaan haluttaessa erikseen määritellä sallitut tagit$string = strip_tags($string, ‘<a><b><i>’); • Vaadittujen muuttujien tarkistaminenif(!$firstname || !$lastname) exit(“Täytä vaaditut kentät.”); • Muuttujan tyypin tarkistaminenif(gettype($age) != “interger”) exit(“Iän pitää olla kokonaisluku.”);

20. Joitain muita hyödyllisiä funktioita… • mysql_affected_rows() palauttaa rivimäärän joihin edellinen INSERT, UPDATE tai DELETE kysely vaikutti • mysql_num_rows() palauttaa rivien määrän SELECT kyselyn palauttamassa tulosjoukossa • mysql_insert_id() palauttaa viimeisen INSERT kyselyn tuottaman avaimen arvon • Täydellinen lista http://www.php.net/manual/en/red.mysql.php • MySQL dokumentaatiohttp://www.mysql.com/doc

21. VARCHAR TINYINT TEXT DATE SMALLINT MEDIUMINT INT BIGINT FLOAT DOUBLE DECIMAL DATETIME TIMESTAMP TIME YEAR CHAR TINYBLOB TINYTEXT BLOB MEDIUMBLOB LONGTEXT ENUM SET http://www.mysql.com/doc/en/Column_types.html MySQL-tietotyypit