Opasarkisto: PHP 4 -opas: Osa 8 - Tietokannat (MySQL)

Tämä opas kertoo, kuinka MySQL-tietokantaa käytetään PHP-skriptin kautta. Oppaassa on myös jonkin verran perustietoa tietokannoista ja SQL-kielestä. Ohjelmointiputkan opassarja MySQL ja PHP käsittelee perusteellisesti MySQL-tietokannan käyttöä PHP:llä.

Johdanto

Tietokanta on tehokas tapa tallentaa suuri (ja myös pieni) määrä tietoa. Tallennetusta tiedosta on helppo hakea monimutkaisinkin ehdoin. Tietokanta koostuu tauluista, jotka taas sisältävät joukon tietueita, joilla on ennalta määritetty rakenne. Yhdessä taulussa voi olla esimerkiksi varaston tavaroiden tiedot, jolloin jokaista tavaraa vastaa yksi tietue. Erilaisia tietokantamuotoja on lukematon määrä, mutta yleensä niiden perustana on SQL-kyselykieli.

Tämän oppaan tietokannaksi on valittu MySQL, koska se on ilmainen, toimii useilla järjestelmillä ja on varsin usein mukana huokeissakin sivustopaketeissa. Tietokannan dokumentaatio sekä kopioitavat versiot eri järjestelmille ovat osoitteessa http://www.mysql.com/. MySQL sisältää oman komentorivipohjaisen hallintaohjelman, mutta graafinen käyttöliittymä, kuten Windowsissa toimiva MySQL-Front, helpottaa tietokannan hallintaa ja auttaa ymmärtämään sen rakenteen.

Esimerkkitaulu

Oppaan esimerkeissä käytetään varasto-nimistä taulua, joka sisältää varastossa olevien tavaroiden nimet, hinnat ja kappalemäärät. Taulu sisältää kolme kenttää: merkkijono nimi, kokonaisluku hinta ja kokonaisluku maara. Seuraava SQL-kysely luo kuvauksen mukaisen taulun:

CREATE TABLE varasto (nimi TEXT, hinta INT, maara INT);

Tauluun lisätään valmiiksi kolme tietuetta, joiden nimet ovat laskin, kamera ja televisio.

INSERT INTO varasto (nimi, hinta, maara) VALUES ('laskin', '10', '10');
INSERT INTO varasto (nimi, hinta, maara) VALUES ('kamera', '120', '5');
INSERT INTO varasto (nimi, hinta, maara) VALUES ('televisio', '950', '5');

Näin ollen taulu sisältää kolme tietuetta ja näyttää kokonaisuudessaan seuraavalta:

Yhteyden muodostaminen

Yhteys tietokantapalvelimeen muodostetaan mysql_connect-funktiolla. Funktion parametrit ovat palvelimen nimi, käyttäjätunnus ja salasana. Jos parametreja ei anneta, yhteys yritetään muodostaa omalle palvelimelle (localhost) ilman salasanaa. Funktion palauttama yhteyden tunniste on syytä ottaa talteen, sillä sitä tarvitaan myöhemmin.

//yhteys omalle palvelimelle
$yhteys = mysql_connect();
//yhteys ulkoiselle palvelimelle
$yhteys = mysql_connect("db.palvelin.fi", "käyttäjä", "salasana");

Tämän jälkeen valitaan käytettävä tietokanta mysql_select_db-funktiolla. Funktion parametrit ovat tietokannan nimi ja yhteyden tunniste. Jos tunnistetta ei anneta, käytetään viimeksi luotua yhteyttä.

//valitaan tietokanta "testi"
mysql_select_db("testi", $yhteys);

Nyt yhteys palvelimelle on kunnossa, ja tietokanta on valittu, joten tietokannan tauluihin voidaan tehdä tarvittavat kyselyt (lisätietoa seuraavissa kappaleissa). Lopuksi yhteys suljetaan mysql_close-funktiolla. Ilman parametreja funktio sulkee viimeksi luodun yhteyden.

//suljetaan yhteys
mysql_close($yhteys);

Seuraava esimerkkiskripti sisältää pohjan palvelimen tietokantaa hyödyntävälle skriptille. Jos yhteyttä palvelimeen ei jostain syystä saada, tai tietokantaa ei löydy, skriptin suoritus lopetetaan virheilmoituksen kera.

<?php
//muodostetaan yhteys tietokantapalvelimeen
$yhteys = mysql_connect() or die("Yhdistäminen ei onnistunut!");

//valitaan tietokanta "testi"
mysql_select_db("testi", $yhteys) or die("Tietokantaa ei löytynyt!");

//tähän tulevat tietokantakyselyt!

//suljetaan yhteys
mysql_close($yhteys);
?>

Tietokantakysely

Tietokantakyselyjen kautta paitsi haetaan tietoa tietokannasta myös lisätään, muokataan ja poistetaan sitä. Seuraavassa on joitakin esimerkkejä SELECT-, INSERT-, UPDATE- ja DELETE-kyselyistä, joita varasto-tauluun voitaisiin kohdistaa. Tietokannasta hakeva SELECT-kysely on monimutkaisin ja siitä onkin koko joukko esimerkkejä. Suluissa olevat arvot kuvaavat haun tuloksia.

• SELECT * FROM varasto
  Hakee kaikki kentät taulun tietueista.
• SELECT nimi, maara FROM varasto
  Hakee taulun tietueiden nimet ja määrät.
• SELECT * FROM varasto WHERE nimi = 'laskin'
  Hakee tietueet, joiden nimi on 'laskin'.
• SELECT * FROM varasto WHERE hinta < 500 AND maara > 5
  Hakee tietueet, joiden hinta on pienempi kuin 500 euroa ja määrä suurempi kuin 5.
• SELECT * FROM varasto WHERE nimi LIKE '%a%'
  Hakee tietueet, joiden nimessä on kirjain 'a'.
• SELECT * FROM varasto ORDER BY nimi
  Järjestelee tietueet nimen mukaan pienimmästä suurimpaan.
• SELECT * FROM varasto ORDER BY hinta DESC
  Järjestelee tietueet hinnan mukaan suurimmasta pienimpään.
• SELECT * FROM varasto LIMIT 2
  Hakee taulun 2 ensimmäistä tietuetta.
• SELECT COUNT(*) FROM varasto
  Hakee taulun tietueiden määrän (3).
• SELECT MAX(hinta) FROM varasto
  Hakee taulun korkeimman hinnan (950 euroa).
• SELECT SUM(maara) FROM varasto
  Hakee kaikkien tietueiden määrien yhteissumman (20).
• SELECT ROUND(hinta * 5.94573, 2) FROM varasto
  Hakee hinnat markkoina kahden desimaalin tarkkuudelle pyöristettyinä.
• SELECT LEFT(nimi, 2) FROM varasto
  Hakee nimien kaksi ensimmäistä kirjainta.
• SELECT DISTINCT maara FROM varasto
  Hakee tietueiden määrien eri arvot (10 ja 5).
• SELECT COUNT(*), maara FROM varasto GROUP BY maara
  Hakee tietueiden määrien eri arvot ja kunkin esiintymiskerrat (kerran 10 ja 2 kertaa 5).

Tähti (*) kuvaa kaikkia tietueen kenttiä. Merkkijonot on ympäröitävä aina heittomerkeillä; luvut voi kirjoittaa ilmankin. WHERE-ehto voi sisältää vertailuoperaattoreita =, <>, <, >, <= ja >=, ja vertailut voidaan yhdistää AND- ja OR-operaattoreilla. Peruslaskutoimitukset tarvittaessa sulkumerkein varustettuna ja funktiot ovat käytössä SELECT- ja WHERE-lausekkeissa.

Kaikkia edellä kuvattuja kyselyitä on mahdollista yhdistää toisiinsa, kunhan avainsanojen järjestys on oikea: SELECT – DISTINCT – FROM – WHERE – GROUP BY – ORDER BY – LIMIT.

Seuraavassa on pari esimerkkiä tietueiden lisäämisestä (INSERT), muuttamisesta (UPDATE) ja poistamisesta (DELETE):

• INSERT INTO varasto(nimi, hinta, maara) VALUES ('radio', 175, 15)
  Lisää tauluun uuden tietueen annetuilla arvoilla.
• UPDATE varasto SET hinta = 185 WHERE nimi = 'radio'
  Muuttaa radioiden hinnaksi 185 euroa.
• UPDATE varasto SET maara = maara - 1 WHERE nimi = 'radio'
  Vähentää radioiden määrää yhdellä.
• DELETE FROM varasto WHERE nimi = 'radio'
  Poistaa tietueen, jonka nimi on 'radio'.

Tässä kappaleessa on esitelty vasta pieni osa MySQL:n ominaisuuksista ja mahdollisuuksista. Kuitenkin jonkinlainen yleiskuva SQL-kyselyjen luonteesta on varmaan muodostunut, ja nyt on aika katsoa, kuinka sama tapahtuu PHP:n puolella.

PHP ja MySQL-kyselyt

SQL-kysely tietokantaan suoritetaan mysql_query-funktion avulla. Toinen parametri, yhteyden tunniste, ei ole pakollinen, jos käytetään viimeksi avattua yhteyttä. SELECT palauttaa taulukon, kun taas esimerkiksi INSERT, UPDATE ja DELETE palauttavat pelkästään tiedon kyselyn onnistumisesta.

//haetaan kaikki tietueet
$kysely = "SELECT * FROM varasto";
//suoritetaan kysely
$haku = mysql_query($kysely, $yhteys);

Tämän kyselyn tulos olisi seuraavanlainen taulukko:

+-----------+-------+-------+
| nimi      | hinta | maara |
+-----------+-------+-------+
| laskin    |    10 |    10 |
| kamera    |   120 |     5 |
| televisio |   950 |     5 |
+-----------+-------+-------+

Kyselyn ollessa SELECT on haun tulokset jollakin tavalla käsiteltävä. Eräs keino on käyttää mysql_result-funktiota. Funktion ensimmäinen parametri on kyselyn tuloksena syntynyt taulukko, toinen parametri on rivin numero ja kolmas parametri on kentän numero tai kentän otsikko. Rivien ja kenttien laskeminen aloitetaan nollasta. Seuraavat funktiot palauttavat saman tuloksen, 'kamera':

//toisen rivin nimi-kenttä
echo mysql_result($haku, 1, "nimi");
//toisen rivin ensimmäinen kenttä
echo mysql_result($haku, 1, 0);

Funktio mysql_num_rows palauttaa taulukon rivien määrän ja mysql_num_fields palauttaa kenttien määrän. Tässä tapauksessa sekä rivien että kenttien määrä on 3:

//rivien määrä
echo mysql_num_rows($haku);
//kenttien määrä
echo mysql_num_fields($haku);

Seuraava esimerkkiskripti, yhdistettynä tietokantayhteyden avaamiseen ja sulkemiseen, hakee varaston kaikki tavarat täydellisine tietoineen ja muodostaa niistä HTML-taulukon:

<?php
echo "<html><body>";

//haetaan kaikki tavarat
$kysely = "SELECT * FROM varasto";
//suoritetaan kysely
$haku = mysql_query($kysely, $yhteys) or die("Virhe kyselyssä!");

echo "<table border>";
echo "<tr><td><b>nimi</b></td><td><b>hinta</b></td><td><b>määrä</b></td></tr>";
//käydään tavarat läpi
for ($i = 0; $i < mysql_num_rows($haku); $i++) {
   //haetaan nimi, hinta ja määrä muuttujiin
   $nimi = mysql_result($haku, $i, "nimi");
   $hinta = mysql_result($haku, $i, "hinta");
   $maara = mysql_result($haku, $i, "maara");
   //tulostetaan taulukon rivi
   echo "<tr><td>$nimi</td><td>$hinta €</td><td>$maara kpl</td></tr>";
}
echo "</table>";

echo "</body></html>";
?>

Skriptin tuloksena syntyvä taulukko näyttää tältä:

Muita toteutustapoja

Taulukon rivejä voi käsitellä myös mysql_fetch_array-funktiolla, joka palauttaa seuraavan rivin taulukkomuodossa. Taulukon indekseinä on kenttien nimet (MYSQL_ASSOC), kenttien numerot (MYSQL_NUM) tai kummatkin (MYSQL_BOTH).

<?php
echo "<table border>";
echo "<tr><td><b>nimi</b></td><td><b>hinta</b></td><td><b>määrä</b></td></tr>";
//käydään tavarat läpi
while ($rivi = mysql_fetch_array($haku, MYSQL_ASSOC)) {
    //haetaan nimi, hinta ja määrä muuttujiin
    $nimi = $rivi["nimi"];
    $hinta = $rivi["hinta"];
    $maara = $rivi["maara"];
    //tulostetaan taulukon rivi
    echo "<tr><td>$nimi</td><td>$hinta €</td><td>$maara kpl</td></tr>";
}
echo "</table>";
?>

Kolmas toteutustapa on käyttää mysql_fetch_object-funktiota, joka palauttaa taulukon seuraavan rivin objektina. Tällöin taulukon kenttiin viittaaminen on mahdollista ainoastaan nimen perusteella.

<?php
echo "<table border>";
echo "<tr><td><b>nimi</b></td><td><b>hinta</b></td><td><b>määrä</b></td></tr>";
//käydään tavarat läpi
while ($rivi = mysql_fetch_object($haku)) {
    //haetaan nimi, hinta ja määrä muuttujiin
    $nimi = $rivi->nimi;
    $hinta = $rivi->hinta;
    $maara = $rivi->maara;
    //tulostetaan taulukon rivi
    echo "<tr><td>$nimi</td><td>$hinta €</td><td>$maara kpl</td></tr>";
}
echo "</table>";
?>

Loppusanat

Edelleen hyödyllistä luettavaa ovat PHP:n ja MySQL:n dokumentaatio. Opassarjan seuraava osa on myös viimeinen, mutta enpä osaa vielä luvata, mitä siihen tulee. Ehdotuksia oppaan aiheista kuten myös kommentteja ja palautetta voi lähettää sähköpostilla.

Antti Laaksonen, 1.7.2003