Tietokannat ja PHP - Luento 11

Tällä luennolla käsitellään tietokantoja PHP:n näkökulmasta.

Luentotaltiointi

Yleistä tietokannoista

Tällä kurssilla oletetaan, että tietokannat ja niihin liittyvät SQL-lauseet ovat tuttuja jo esitietokursseilta. Jos kyseiset asiat eivät ole kovin hyvin hallussa, itseopiskelua voi harrastaa esimerkiksi ao. kurssien materiaaleilla.

Käsiteltäessä tietokantoja on aina hyvä pitää mielessä se perussääntö, että annetaan tietokannan tehdä eri operaatiot mahdollisimman pitkälle. Siispä esimerkiksi viite-eheydet, rajoitteet ja lajittelut kannattaa aina tehdä suoraan kannassa, jos mahdollista. Myös esimerkiksi haut kannattaa rajata mahdollisimman tarkkaan jo SQL-lausetta muodostaessa. Kannassa operoiminen on merkittävästi tehokkaampaa kuin tehdä vastaavat asiat vasta ohjelmakoodin puolella. Tehokkuuden lisäksi operaatioiden tekeminen ohjelmakoodissa on paljon virhealttiimpaa kuin että tietokanta tekisi vastaavat operaatiot itse.

Lisäksi kannattaa pitää mielessä se, että jokainen tietokantakutsu syö nopeutta sovellukselta (merkittävää siinä vaiheessa, kun tehdään isoja sovelluksia). Tietokantakutsut kannattaa siis miettiä tarkkaan siten, ettei kantaan tehdä turhia kutsuja (ei samoja kutsuja useaan kertaan samalla sivulla; ei päivitystä, jos ei ole päivitettävää jne.).

PHP Data Objects (PDO)

PHP:n pahimpia vikoja on ollut yhtenäisen tietokantaliittymän puute. Ts. jokaisella tietokantaohjelmistolla on ollut erilainen rajapinta.
PDO yhtenäistää tietokantarajapinnat.
Mahdollistaa helpomman vaihtamisen tietokantaohjelmistosta toiseen.
Tulee kuitenkin vakiona vasta PHP 5.1:n mukana.
Tukee tärkeimpia tietokantaohjelmistoja: PostgreSQL, Oracle, MySQL, Firebird, SQLite jne.
Jos mahdollista, niin kannattaa käyttää ADOdb- tai Pear DB-abstraktiokirjastoja.

Tietokantayhteys

Tietokantayhteys avataan PDO:lla seuraavasti:

// Connection string riippuu käytetystä tietokantaohjelmistosta
$dbh = new PDO("Connection string","käyttäjätunnus","salasana");

// SQLite:
$dbh = new PDO('sqlite:/home1/137/anjoekon/public_html/php/testi');

// MySQL:
$dbh = new PDO('mysql:host=palvelin;dbname=testi',"tunnus","salasana");

// PostgreSQL:
$dbh = new PDO('pgsql:host=palvelin;dbname=testi',"tunnus","salasana");

Tietokantayhteyden avaus ei kuitenkaan aina onnistu, joten:

try {
  $dbh = new PDO('mysql:host=palvelin;dbname=testi',"tunnus","salasana");
}
catch(PDOException $e) {
  die($e->getMessage());
}

Kyselyt

Tietokantayhteyden avaamisen jälkeen kantaan voidaan tehdä kyselyjä.

Kyselyn voisi tehdä suoraan query-metodilla:

$sql = 'SELECT nimi, asukasluku, valtio FROM osavaltio ORDER BY valtio';
foreach($dbh->query($sql) as $rivi) {
  echo $rivi['nimi']."\t";
  echo $rivi['asukasluku']."\t";
  echo $rivi['valtio']."\n";
}

Järkevämpää ja tehokkaampaa on kuitenkin käyttää hyväksi PDO:n Prepared Statements -metodeja:

// valmistellaan kysely ja pyydetään tulos assosiatiivisena taulukkona
$sql = $dbh->prepare('SELECT * FROM testi');

$ok = $sql->execute();

if(!$ok) { 
  print_r($sql->errorInfo());
}

while($row = $sql->fetch(PDO::FETCH_ASSOC)) {
  foreach($row as $key => $arvo) {
    echo $key." = ".$arvo."\n";
  }
}

Tietueet voi pyytää myös monessa muussa muodossa kuin assosiatiivisena taulukkona.

Kyselyn suorittamisen vaiheet

Kysely valmistellaan - valmisteltu kysely suoritetaan seuraavilla suorituskerroilla tehokkaammin (prepare).
Kysely suoritetaan (execute).
Kyselyn tulos käydään läpi (fetch).

Dynaamiset kyselyt sidotuilla muuttujilla

Dynaamisissa kyselyissä kyselyyn sidotaan mukaan muuttujia (bindParam).

$sql = $dbh->prepare('SELECT * FROM testi WHERE name = :name');

$name = "Foo";

// bindParam-metodille täytyy aina viedä toiseksi parametriksi muuttuja
// EI voi siis syöttää esim. $sql->bindParam(":name","Foo");
$sql->bindParam(":name",$name);

$ok = $sql->execute();

if(!$ok) {
  print_r($sql->errorInfo());
}

while($row = $sql->fetch(PDO::FETCH_ASSOC)) {
  foreach($row as $key => $arvo) {
    echo $key." = ".$arvo."\n";
  }
}

Muuttujia ei koskaan pidä sijoittaa suoraan SQL-lauseeseen.

Arvoja voisi sitoa kyselyihin myös bindValue-metodilla. Keskeisin ero metodeilla on se, että bindParam sitoo muuttujaan kyselyyn viitteenä, kun taas bindValue suoraan arvona.

Tietojen lisääminen, poistaminen ja muuttaminen

Myös tietojen lisääminen, poistaminen ja muuttaminen onnistuu samalla idealla kuin kyselyjen tekeminen. Käytetään siis samaa prepare-execute-menetelmää (ja tarvittaessa muuttujan sitomista kyselyyn) sopivilla SQL-lauseilla. Alla olevat esimerkit selventävät asiaa.

// poistaminen
$sql = $dbh->prepare('DELETE FROM testi WHERE avain = :avain');

$avain = "123";

$sql->bindParam(":avain",$avain);

$ok = $sql->execute();

if(!$ok) {
  print_r($sql->errorInfo());
} 

print $sql->rowCount();

// muuttaminen
$sql = $dbh->prepare('UPDATE testi SET avain = :avain WHERE avain = :vanha');

$avain = "111";
$vanha = "123";

$sql->bindParam(":avain",$avain);
$sql->bindParam(":vanha",$vanha);

$ok = $sql->execute();

if(!$ok) {
  print_r($sql->errorInfo());
} 

print $sql->rowCount();

// lisääminen
$sql = $dbh->prepare('INSERT INTO testi (avain, name) VALUES (:avain, :name)');

$avain = "100";
$name = "Foofoo";

$sql->bindParam(":avain",$avain);
$sql->bindParam(":name",$name);

$ok = $sql->execute();

if(!$ok) {
  print_r($sql->errorInfo());
} 

print $sql->rowCount();

// lisätään useita "kerralla"
// käytetään edellisessä kohdassa preparoitua kyselyä
// bindParamia ei tarvitse kutsua enää ollenkaan silmukan sisällä
$tiedot = array(1 => "barbar", 2 => "foobar", 3 => "barfoo");

foreach($tiedot as $key => $value) {
  $avain = $key;
  $name = $value;
  $ok = $sql->execute();
  if(!$ok) {
    print_r($sql->errorInfo());
  } 
}

print $sql->rowCount();

Esimerkkien uusia metodeja:

Transaktiot

PDO tukee myös transaktioita. Transaktioiden avulla pystytään hallitsemaan tietokantaoperaatiokokonaisuuksia. Esimerkiksi, jos tietokantaan pitää syöttää useita tietueita peräkkäin ja jos yksikin lisäys epäonnistuu, niin kaikki lisäykset pitää pystyä peruuttamaan (vrt. verkkopankki). Transaktio varmistaa, etteivät keskeneräisen transaktion vaikutukset tietokantaan heijastu muille tietokannan käyttäjille kuin vasta transaktion hyväksymisen jälkeen. Alapuolella on esimerkki transaktioiden käytöstä.

$dbh->beginTransaction(); // aloittaa transaktion
$err = 0;
$sql = $dbh->prepare("INSERT INTO osavaltio (nimi, asukasluku, valtio) VALUES (?,?,?)");

$nimi = "Englanti";
$asukasluku = 70000000;
$valtio = "GB";

// huomaa uusi tapa bindParamin käytössä, muuttujat "sidotaan" kysymysmerkkien kohdalle
// siinä järjestyksessä, kun "sitominen" tehdään (mille numerolle "sidotaan")
$sql->bindParam(1,$nimi);
$sql->bindParam(2,$asukasluku);
$sql->bindParam(3,$valtio);

$ok = $sql->execute();

if(!$ok) {
  $err++;
  $dbh->rollBack(); // peruuttaisi lisäyksen
  exit();
} else {
  $dbh->commit(); // hyväksytään lisäys
}

$id = $dbh->lastInsertID();
/* - lastInsertID palauttaa viimeisen tietokantaan lisätyn avaimen (primary key).
   - Kätevä keino saada selville lisätty ID silloin, kun tietokannassa on 
     autoincrement-määritys ID:lle (primary keylle).
   - Jos primary key (ID) ei ole kokonaisluku, niin SQLite palauttaisi sisäisen 
     rivinumeron, jota vastaava varsinainen avain pitäisi erikseen hakea SELECT-lauseella: 
     SELECT avain FROM taulu WHERE ROWID = :last_insert_rowid;
   - Tämä on tietokantakohtaisesti muuttuva toiminto. */

print $id."\n";

Esimerkkien uusia metodeja:

PHP:n vanhat versiot ja tietokannat (MySQL)

Jos käytössä on vanhempi PHP-tulkki kuin 5.1, ei PDO:ta voida hyödyntää. Silloin täytyy käyttää PHP:n "tietokantasidottuja" funktioita. Tässä esitellään lyhyesti MySQL-tietokantaan liittyviä toimenpiteitä. Myös muihin tietokantoihin löytyy vastaavia funktioita. Jos PDO:ta voidaan kuitenkin käyttää, niin tietokantasidottuja funktioita ei kannata koskaan käyttää!

PHP.net: MySQL

Tietokantayhteyden avaaminen

Yhteys MySQL-tietokantaan muodostetaan PHP:ssä mysql_connect()-funktiolla. Se muodostaa ainoastaan TCP-yhteyden MySQL-palvelimelle. Funktiolle annetaan tavallisesti parametreina MySQL-palvelimen URL, käyttäjätunnus ja salasana. Se palauttaa onnistuessaan kahvan avattuun tietokantayhteyteen ja epäonnistuessaan false. Alla on esimerkki funktion kutsusta.

$yhteys = mysql_connect("palvelin","tunnus","salasana");

Tietokannan valitseminen

Kun tietokantapalvelimelle on saatu yhteys, valitaan tietokanta, jota halutaan käyttää. Tämä tapahtuu mysql_select_db()-funktiolla alla olevan esimerkin mukaisesti.

$yhteys = mysql_connect("palvelin","tunnus","salasana") 
  or die("Kantaan ei saatu yhteyttä: ".mysql_error());
mysql_select_db("kannan_nimi",$yhteys)
  or die("Kantaa ei saatu valittua: ".mysql_error());

Esimerkin mukaisesti mysql_select_db()-funktiolle annetaan parametreinä käytettävän tietokannan nimi ja aiemmin avatun tietokantayhteyden kahva.

Edellisissä esimerkeissä on käytetty myös mysql_error()-funktiota. Se palauttaa aina merkkijonon edellisestä MySQL-palvelimen lähettämästä virheilmoituksesta. Sen avulla voidaan siis tunnistaa tietokantayhteydessä tapahtuneet virheet. Lopullisessa ohjelmaversiossa on kuitenkin kyseenalaista näyttää käyttäjälle minkäänlaisia virheilmoituksia. Ne voivat usein sisältää tietoturvan kannalta riskialtista tietoa.

Kyselyn tekeminen

Kysely MySQL-tietokantaan toteutetaan mysql_query()-funktiolla. Itse asiassa PHP:ssä useimmat muutkin tietokantaoperaatiot toteutetaan samalla funktiolla. Funktiolle annetaan parametreina SQL-lause ja kahva tietokantayhteyteen. Alla on esimerkki tavallisesta valintalauseesta.

$tulos = mysql_query("SELECT * FROM osavaltio WHERE valtio LIKE 'GB'",$yhteys);

Niissä tapauksissa, joissa mysql_query()-funktion avulla haetaan tietokannasta jotain tietoa, se palauttaa onnistuessaan linkin tulosjoukkoon. Kaikissa muissa tapauksissa palautusarvo on suorituksen onnistumisen mukaan true tai false.

Kyselyn tulos on aina suorituksen onnistuessa linkki tulosjoukkoon. Vaikka tietokannasta haettu tieto olisi yksi luku, on muistettava, että mysql_query()-funktion palauttama arvo ei ole luku, eikä sitä voida käsitellä kuten lukua. Tulosjoukkoa täytyy aina käsitellä mysql-funktioiden avulla. Alla on esimerkki siitä, miten tulosjoukko voidaan käydä läpi.

$yhteys = mysql_connect("palvelin","tunnus","salasana") 
  or die("Kantaan ei saatu yhteyttä: ".mysql_error());
mysql_select_db("kannan_nimi",$yhteys)
  or die("Kantaa ei saatu valittua: ".mysql_error());
$tulos = mysql_query("SELECT * FROM osavaltio WHERE valtio LIKE 'GB'",$yhteys);
while($rivi = mysql_fetch_array($tulos)) {
  echo $rivi['nimi'].", ".$rivi['asukasluku'].", ".$rivi['valtio']."\n";
}

Esimerkissä on käyty kyselyn palauttama tulosjoukko läpi while-silmukassa. Silmukan ehdossa on käytetty mysql_fetch_array()-funktiota tiedon saamiseksi tulosjoukosta. Tulosjoukossa on sisäinen osoitin, joka alkutilanteessa osoittaa tulosjoukon ensimmäiselle riville. Funktio mysql_fetch_array() palauttaa assosiatiivisena taulukkona rivin, johon tulosjoukon osoitin osoittaa ja siirtää samalla osoittimen seuraavalle riville. while-silmukassa kutsutaan sitä joka kierroksella niin monta kertaa, että tulosjoukosta loppuu rivit ja funktio palauttaa arvon false. mysql_fetch_array()-funktion palauttaman taulukon soluihin viitataan tietokantataulun vastaavien kenttien nimillä. Yhtä hyvin taulukon soluihin voidaan viitata numeeristen indeksien mukaan.

Rivin lisääminen tietokantaan

Rivin lisääminen tietokantaan tapahtuu PHP:ssä myös mysql_query()-funktion avulla. Poikkeuksena kyselyn tekemiseen on ainoastaan se, että funktio ei palauta tulosjoukkoa, vaan ainoastaan tiedon SQL-lauseen onnistumisesta.

SQL-syntaksissahan rivin lisäys tietokantatauluun tapahtuu INSERT-lauseella. Alla on esimerkki rivin lisäämisestä kantaan PHP-koodissa. Esimerkissä oletetaan, että tietokantayhteys kantaan on jo avattu.

mysql_query("INSERT INTO osavaltio VALUES('Englanti', 70000000, 'Iso-Britannia')")
  or die("Lisäys epäonnistui: ".mysql_error()."</div></body></html>");

Esimerkissä oletetaan, että tietokantataulussa on täsmälleen nuo kolme kenttää. Jos johonkin kenttään ei haluta syöttää arvoa, voi sen tilalla SQL-syntaksissa antaa arvon NULL.

Rivin poistaminen tietokannasta

Rivin poistaminen tietokannasta toimii myös mysql_query()-funktion avulla:

mysql_query("DELETE FROM osavaltio WHERE nimi = 'Englanti'")
  or die("Poisto epäonnistui: ".mysql_error()."</div></body></html>");

Tietojen päivittäminen tietokannassa

Rivin tietojen päivittäminen tietokannassa toimii myös mysql_query()-funktion avulla:

mysql_query("UPDATE osavaltio SET valtio = 'USA' WHERE nimi = 'Wales'")
  or die("Päivitys epäonnistui: ".mysql_error()."</div></body></html>");

Muita hyödyllisiä `mysql`-funktioita

mysql_num_rows() - palauttaa tulostaulukon rivien määrän.
mysql_affected_rows() - palauttaa kyselyä koskeneiden rivien määrän (vrt. PDO:n rowCount()).
mysql_close() - sulkee tietokantayhteyden (normaalisti yhteyttä ei kuitenkaan tarvitse sulkea itse).

SQLite

Ohjeita SQLite-tietokannan käyttöön SQLite-ohjeista ja demosta 6.

SQLite-ohjeista kannattaa tutustua ainakin virheilmoituksiin ja kohtaan Viite-eheyttä ei tueta. Viite-eheyksien toteuttaminen onnistuu siis triggereiden avulla.

Luentoesimerkki

Luennolla tehdään lyhyitä esimerkkejä PDO:n käyttöön liittyen, jos ehditään. Enemmän esimerkkejä tehdään seuraavalla luennolla.

Esimerkin lähdekoodi

Lisätietoa

Käyttäjien kommentit

Evästeet, sessiot ja tiedostot sekä 4. viikkotehtävä

PHP ja tietokannat sekä 5. viikkotehtävä