Koodit: PHP, SQL: PDO - hyvä tapa käsitellä tietokantoja

CREATE TABLE useraccount (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    username TEXT NOT NULL
) ENGINE=InnoDB;

CREATE TABLE post (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    userId INT NOT NULL,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    posttime TIMESTAMP NOT NULL DEFAULT current_timestamp,
    FOREIGN KEY (userId) REFERENCES useraccount (id)
) ENGINE=InnoDB;

-- Testidataa:
INSERT INTO useraccount (username) VALUES ('jussi'); -- saa id:n 1
INSERT INTO useraccount (username) VALUES ('jukka'); -- saa id:n 2

INSERT INTO post (userId, title, content, posttime)
VALUES (1, 'mielipiteitä', 'höpöhöpö, höpönlöpön', TIMESTAMP(NOW(), '01:00'));
INSERT INTO post (userId, title, content, posttime)
VALUES (1, 'lisää mielipiteitä', 'päläpälä, käläkälä', TIMESTAMP(NOW(), '02:00'));
INSERT INTO post (userId, title, content, posttime)
VALUES (2, 'php-opas', '<?php echo .....', TIMESTAMP(NOW(), '03:00'));

<?php
require('init.php');

/*
 * Oletetaan esimerkin vuoksi, että tälle skriptille
 * on lähetetty lomakkeesta kenttä 'uid', jonka tulisi olla
 * käyttäjätunnus:
 */
$_REQUEST['uid'] = '1';


// Kysellään, montako viestiä tämä käyttäjä on lähettänyt

// Aluksi luodaan valmisteltu lause ("prepared statement") siten, että parametrien
// kohdalle kirjoitetaan kysymysmerkki.
$stmt = $db->prepare('SELECT COUNT(*) AS n FROM post WHERE userId = ?');

// Seuraavaksi annetaan jokaiselle kysymysmerkille parametrin arvo
// kutsumalla valmistellun lausekkeen metodia
// bindValue(kysymysmerkin_numero, arvo);
//
// Tämä lisää tarvittavat lainausmerkit ja kenoviivat,
// mikä estää SQL-injektiot. Toisin sanoen, tämä ajaa saman asian kuin
// mysql-rajapinnan mysql_real_escape_string.
$stmt->bindValue(1, $_REQUEST['uid']);

// Suoritetaan kysely.
$stmt->execute();

// Luetaan tulos.
// Tässä tapauksessa tuloksia on tasan yksi, koska kyselimme COUNT(*):n.
$result = $stmt->fetchObject();

// Tuloksemme on tulosolion kentässä "n", koska kyselyssämme lukee AS n.
echo $result->n . "\n";

<?php
require('init.php');

// Oletetaan taas, että skripti sai lomakkeesta parametrin 'uid'.
$_REQUEST['uid'] = '1';

// Kysellään kaikki pyydetyn käyttäjän lähettämät viestit lähetysjärjestyksessä
$stmt = $db->prepare('SELECT * FROM post ' .
                     'WHERE userId = ? ' .
                     'ORDER BY posttime DESC');
/*
 * Sen sijaan, että sanotaan erikseen
 * $stmt->bindValue(1, $_REQUEST['uid']) ja $stmt->execute(),
 * voidaan sanoa:
 */
$stmt->execute(array($_REQUEST['uid']));

while ($result = $stmt->fetchObject()) {
    echo $result->title . "\n";
    echo $result->content . "\n";
    echo "-----\n";
}

<?php
// Tiedosto: MyPDO.php

class MyPDO extends PDO {

    public function __construct(/*...*/) {
        // (älä huolestu, jos et ymmärrä seuraavaa kahta koodiriviä)
        // Haetaan tämän metodin kaikki parametrit taulukkoon
        $args = func_get_args();
        // Kutsutaan yläluokan konstruktoria kaikilla parametreilla
        call_user_func_array(array($this, 'parent::__construct'), $args);

        // Asetetaan poikkeusten heittäminen oletusarvoksi
        $this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }

    /*
     * Tekee kyselyn ja palauttaa kaikki tulosoliot taulukossa.
     */
    public function queryAll($sql, array $params) {
        $stmt = $this->prepare($sql);
        $stmt->execute($params);
        $resArr = array();
        while ($res = $stmt->fetchObject()) {
            $resArr[] = $res;
        }
        return $resArr;

        // Harjoitus 1: toteuta tämä tehokkaammin $stmt->fetchAll():lla
        // Harjoitus 2: muuta funktiota siten, että $params-taulukon
        //              sijaan sille voi antaa argumentit suoraan
        //              esim. $db->queryAll('SELECT ...', $a, $b, $c);
        //              (eikä $db->queryAll('SELECT ...', array($a, $b, $c));)
    }

    /*
     * Tekee kyselyn ja palauttaa ensimmäisen tulosolion.
     * Palauttaa false, jos tulosta ei ole.
     */
    public function queryOneRow($sql, array $params) {
        $stmt = $this->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchObject();
    }

    /*
     * Tekee kyselyn ja palauttaa ensimmäisen tulosrivin ensimmäisen sarakkeen.
     * Palauttaa false, jos tulosta ei ole.
     */
    public function queryOneCol($sql, array $params) {
        $stmt = $this->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchColumn();
    }
}

<?php
require('MyPDO.php');

$db = new MyPDO('mysql:host=localhost;dbname=test',
                'kayttaja',
                'salasana');

$_REQUEST['uid'] = '1';

$posts = $db->queryAll('SELECT * FROM post ' .
                       'WHERE userId = ? ' .
                       'ORDER BY posttime DESC',
                       array($_REQUEST['uid']));

foreach ($posts as $post) {
    echo $post->title . "\n";
    echo $post->content . "\n";
    echo "-----\n";
}

$numUserPosts = count($posts);
$numAllPosts = $db->queryOneCol('SELECT COUNT(*) FROM post', array());
echo "Käyttäjän viestit: $numUserPosts / $numAllPosts\n";