Kirjautuminen

Haku

Tehtävät

Opasarkisto: Käytännön PHP-opas: Osa 8 - Tietokannat (MySQL)

  1. Osa 1 - Johdanto
  2. Osa 2 - Muuttujat ja taulukot
  3. Osa 3 - Komentorakenteet
  4. Osa 4 - PHP ja Internet-sivut
  5. Osa 5 - Tiedostojen käsittely
  6. Osa 6 - Funktiot
  7. Osa 7 - Evästeet ja istunnot
  8. Osa 8 - Tietokannat (MySQL)
  9. Osa 9 - Vinkkejä ja vihjeitä

Kirjoittaja: Antti Laaksonen. Vuosi: 2003.

Huomio! Tämä opas on vanhentunut. Oppaan sisältöön ei voi enää luottaa. Opas on säilytetty vain sen historiallisen arvon vuoksi.

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:

nimi TEXThinta INTmaara INT
laskin1010
kamera1205
televisio9505

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.

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: SELECTDISTINCTFROMWHEREGROUP BYORDER BYLIMIT.

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

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

nimihintamäärä
laskin10 €10 kpl
kamera120 €5 kpl
televisio950 €5 kpl

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


Kommentit

Gwaur [30.07.2003 05:22:11]

Lainaa #

Seuraavasta osasta tulee vissiin aika iso :) kun on jo näin kauan kestänyt...

Sir McDuck [10.08.2003 17:42:34]

Lainaa #

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

Kai muuttujia voi käyttää SELECT/WHERE-lauseessa? En selviä ilman niitä :|

NiLon [30.09.2003 00:43:45]

Lainaa #

Tuo eka komento, jonka suoritin eli:
CREATE TABLE varasto (nimi TEXT, hinta INT (6), maara INT (4));
antoi seuraavan virheen: ERROR 1046: No Database Selected
joten miten tuon kannan voi valita?

Sir McDuck [30.09.2003 18:37:27]

Lainaa #

Mihinkäs väliin BETWEEN sijoitetaan, kun sitä ei ole tuolla mainittu? En jostain syystä saa sitä toimimaan.

Heikki [23.11.2003 10:14:44]

Lainaa #

"Seuraava SQL-kysely luo kuvauksen mukaisen taulun:
CREATE TABLE varasto (nimi TEXT, hinta INT (6), maara INT (4));"

Miten tuo tehdään PHP:llä? Ainakaan tuollainen rivi ei toiminut...

NiLon [30.11.2003 06:01:01]

Lainaa #

suoritin sen suoraan myslin komentokehotteessa :)

k-mikko [04.01.2004 02:01:46]

Lainaa #

lainaus:

"Seuraava SQL-kysely luo kuvauksen mukaisen taulun:
CREATE TABLE varasto (nimi TEXT, hinta INT (6), maara INT (4));"
Miten tuo tehdään PHP:llä? Ainakaan tuollainen rivi ei toiminut...

Voit tehdä sen php:llä näin:

<?php
     mysql_query("CREATE TABLE varasto (nimi TEXT, hinta INT(6), maara INT(4))");

?>

Huomaa: Ei puolipistettä SQL-komennon loppuun.

k-mikko [04.01.2004 02:02:30]

Lainaa #

mysql_query()-funktiolla voidaan ajaa tietokantakomentoja.

juhana [25.02.2004 21:33:21]

Lainaa #

Miten tuon COUNT() -funktion tuloksen sais näytettyä PHP:llä?

Olga [09.03.2004 01:39:26]

Lainaa #

Vaikkapa seuraavasti (pelkistetty esimerkki):

<?php

$kysely = mysql_query("SELECT COUNT(*) FROM varasto");
$rivi = mysql_fetch_row($kysely);
$lkm = $rivi[0];

?>

irwinace [15.05.2004 13:48:33]

Lainaa #

voiko tietokannan päivittämiseen käyttää tämmöstä komentoa:
<?php
mysql_query("UPDATE maara (nimi, hinta, maara) VALUES('uusinimi', 'uusihinta', 'uusimaara') WHERE nimi = 'radio'");
?>

irwinace [09.08.2004 17:02:42]

Lainaa #

mulla ei toiminu yhteys omaa palvelimeen mysql_connect()-tavalla, mutta pääsin tällee:
mysql_connect("localhost", "root", "");

litra [10.09.2004 09:56:42]

Lainaa #

irwinace, no sulta puuttuu tosta updatesta ainakin taulu, mihin muutoksen teet..

Puhveli [29.01.2005 22:24:47]

Lainaa #

Mitenkäs tommonen TABLE sitten tapetaan? Minä kun innoissani tein niitä vähän liikaa ja vääränlaisia =p
edit: HAA se on "DROP TABLE nimi"! hassua...

TatuSalin [18.10.2005 23:03:52]

Lainaa #

Testasin tuossa tuota tietokannasta hakua ja jotenkin en vain tiedä mistä mättää, kun aina tulee vain se kirjoittamani tiedosto selaimeen eikä se luo mitään html-taulukkoa, kuten pitäisi. Eli johtuuko tämä siitä, etten ole onnistunut avaamaan yhteyttä tietokantaan? Vaan tulee jotain
<?php
echo "<html><body>";
//haetaan kaikki tavarat
$kysely = "SELECT * FROM varasto";
etc etc...

Vulcan [06.11.2005 19:09:01]

Lainaa #

SELECT * FROM varasto ORDER BY hinta DESC

Miten tuo saadaan niin että se ottaa kaksi lukua huomioon? Kun tilanne on nyt se että on luvut 2, 5 ja 10 nii se heittää 5 ensimmäiseksi sitten 2 ja viimeseks 10? eli miten saan 10 ekaks?

Tuura [06.01.2006 19:51:29]

Lainaa #

Vulcan: kokeiles seuraavaa: SELECT * FROM varasto ORDER BY hinta ASC

Olga [22.01.2006 18:33:47]

Lainaa #

Vulcan, sulla on kenttätyyppinä joku tekstityyppi tms, vaikka kentän pitäis olla numeraalinen.

Tontsa-san [03.08.2006 12:56:13]

Lainaa #

INSERT INTO varasto(nimi, hinta, maara) VALUES ('radio', 175, 15)
Lisää tauluun uuden tietueen annetuilla arvoilla.

Eikös tässä pitäisi olla väli tuon "varasto"-tekstin jälkeen:

INSERT INTO varasto (nimi, hinta, maara) VALUES ('radio', 175, 15)
Lisää tauluun uuden tietueen annetuilla arvoilla.

Teuro [28.08.2006 13:39:31]

Lainaa #

TatuSalin:

Sulla on siis asennettuna PHP koneelle? ja otat selaimella yhteyden (kotikoneella usein localhost) ja katsot tulkattua koodia näin. Ethän siis yritä avata tiedostoa suoraan ilman tulkkia?

aarnis [28.01.2007 09:24:44]

Lainaa #

Hei miten saisi sellatis että kun rekisteröityis foorumille niin pääsisi samoilla käyttäjätunnuksilla kirjautumaan sivulle minulla on phpBB2 foorumi ja eikös se mysql onnistu?

-Claw- [27.03.2007 22:24:46]

Lainaa #

aarnis, otat yhteyden sinne phpBB2 tietokantaan josta haet login-tiedot ja vertaat mitä käyttäjä syötti.

siansaksamies [20.04.2007 14:42:01]

Lainaa #

Tämmönen koodi ois, missä vika?:

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

//valitaan tietokanta "testi"
mysql_select_db("testi", $yhteys) or die("Tietokantaa ei löytynyt!");
$kysely="INSERT INTO tiedot (nimi, salasana) VALUES ('asd', 'dsa')";
mysql_query($kysely,$yhteys);

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

Ja kyseinen taulu nimi ja salasana-sarakkeilla on kannassa ja molemmat muotoa TEXT.

Hugo [28.04.2007 17:44:20]

Lainaa #

EDIT: Joo, ei mitään, puhuin lämpimikseni. :D

moptim [03.06.2007 19:07:53]

Lainaa #

Heh, jos MBNetissäki toimis mysli... Ois aika helppoa se elämä :)

bartfast [26.07.2007 21:53:31]

Lainaa #

Oli paljon kysymyksiä ilman vastauksia. Suurimpaan osaan löytyy vastaus osoitteesta http://www.w3schools.com/sql/default.asp . Säästyn vaivalta kirjottaa samat asiat tänne.

prakka [15.05.2008 17:48:55]

Lainaa #

Pakko sanoa että kiitos, tästä ollut aikoinaan todella paljon hyötyä :)

Viitapiru [24.08.2008 01:25:28]

Lainaa #

Kiitoksia paljon loistavasta oppaasta. Löytyisipä samankaltaista materiaalia useamminkin :)

jtapio [10.03.2009 16:55:31]

Lainaa #

Hei!

Miten saisin seuraavanlaisesta taulukosta haettua länpötilatiedot jotta saisin jpgraphilla net piirrettyä, array sen pitää kuitenkin olla

table temps
ID temp time
0121202 24 11:00
5453453 4 11:00
3423432 10 11:00
0121202 24 12:00
5453453 6 12:00
3423432 11 12:00
0121202 24 13:00
5453453 5 13:00
3423432 12 13:00
0121202 24 14:00

ankzilla [25.03.2009 22:05:55]

Lainaa #

jtapio kirjoitti:

Hei!

Miten saisin seuraavanlaisesta taulukosta haettua länpötilatiedot jotta saisin jpgraphilla net piirrettyä, array sen pitää kuitenkin olla

table temps
ID temp time
0121202 24 11:00
5453453 4 11:00
3423432 10 11:00
0121202 24 12:00
5453453 6 12:00
3423432 11 12:00
0121202 24 13:00
5453453 5 13:00
3423432 12 13:00
0121202 24 14:00

Explodella väännät tekemään joka kolmannelle jotain jollain funktiolla.

mitja [06.09.2009 11:39:56]

Lainaa #

hyvä opas!
pääse hyvin alkuun.

simppeli [22.09.2009 16:07:59]

Lainaa #

<?php
$e = "laskin";
$yhteys = mysql_connect("localhost","root","") or die("ei toimi O_o!");
mysql_select_db("varasto", $yhteys) or die("Virhe, taulua varasto ei löytynyt");
$tieto = mysql_query("SELECT (nimi, $e) FROM varasto ") or die("Error, kysely ei onnistunut!");
echo $tieto;
mysql_close($yhteys);
?>

Miks tää antaa ton "error, kysely ei onnistunut" kaikki nuo taulut ja salasanat asetettu oikein, onko koodissa vikaa?

Macro [08.01.2010 21:24:45]

Lainaa #

Kenties kuitenkaan haettavia kenttiä ei kuulu laittaa sulkeisiin.

Kirjoita kommentti

Huomio! Kommentoi tässä ainoastaan tämän oppaan hyviä ja huonoja puolia. Älä kirjoita muita kysymyksiä tähän. Jos koodisi ei toimi tai tarvitset muuten vain apua ohjelmoinnissa, lähetä viesti keskusteluun.

Muista lukea kirjoitusohjeet.
Tietoa sivustosta