Tietokannat ja PHP - Luento 11

Luentotaltiointi
PHP Data Objects (PDO)
Tietokantayhteys
Kyselyt
- Kyselyn suorittamisen vaiheet
Dynaamiset kyselyt sidotuilla muuttujilla
Tietojen lisääminen, poistaminen ja muuttaminen
Transaktiot
PHP:n vanhat versiot ja tietokannat (MySQL)
SQLite
Luentoesimerkki
Lisätietoa

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

Luentotaltiointi

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:/home3/368/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 $row['nimi']."\t";
  echo $row['asukasluku']."\t";
  echo $row['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.

Tietojen lisääminen, poistaminen ja muuttaminen

// 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"
$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. Transaktio varmistaa, että keskeneräisen transaktion vaikutukset tietokantaan eivät heijastu muille tietokannan käyttäjille kuin vasta transaktion hyväksymisen jälkeen. Alapuolella on esimerkki transaktioiden käytöstä.

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

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

$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();

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ä.

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_close() - sulkee tietokantayhteyden (normaalisti yhteyttä ei kuitenkaan tarvitse sulkea itse).

SQLite

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

Luentoesimerkki

Luennolla tehtiin esimerkkejä PDO:n käytöstä.

Lähdekoodi

Lisätietoa

Käyttäjien kommentit

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

PHP ja tietokannat sekä 5. viikkotehtävä