PHP Study Notes - PDO

PHP Data Objects (PDO) Interface

official documentation

The PDO extension defines a lightweight, consistent interface for accessing databases in PHP. Each database driver that implements the PDO interface can expose database-specific features as regular extension functions. It provides a data-access abstracton layer, regardless of which database you’re using, you use the same functions to issue queries and fetch data.

1. Connection to database

MySql PDO Driver
Since we cannot perform any database functions using the PDO extention by itself; a database-specific PDO driver is required to access a database. In php configuration file php.ini, we can do the following settings to let the driver extension loaded automatically.

extension=mysqli
extension=pdo_mysql
extension=pdo_sqlite

Connections

By creating instances of the PDO base class, connections can be established. It doesn’t matter which driver is used; the PDO class name is always used. The constructor accepts parameters for specifying the database source (DSN), username, password. For example,

<?php
$dsn = 'mysql:host=' . $db_host . ';dbname=' . $db_name . ';charset=utf8';

$db = new PDO($dsn, $db_user, $db_pass);

If the connection errors occur, a PDOException object will be thrown. To handle the error condition, a try-catch block can be used. For example,

<?php
$dsn = 'mysql:localhost=' . $db_host . '; dbname=' . $db_name . '; charset=utf8';

try {

    $db = new PDO($dsn, $db_user, $db_pass);

} catch (PDOException $e) {

    echo $e->getMessage();
    exit;

}

2. Prepared statements and stored procedures

To avoid SQL injectionoccurs, the prepared statements need to be used. They can be thought of as kind of complied template for the SQL that an applicaion wants to run, that can be customized using variable parameters.

The prepared statements bring two major benefits:

• The query only needs to be prepared once, but can be executed multiple times with the same or different parameters.
• No SQL injection will occur.

When using the prepared statments, it follows prepare -> bindValue -> execute procedure. For example,

<?php

$sql = "SELECT * 
        FROM user
        WHERE username = :username"

$stmt = $conn->prepare($sql);

$stmt->bindValue(':username', $username, PDO::PARAM_STR);

$stmt->execute();

Repeated inserts using prepared statements
The following example shows how to repeat inserts using prepared statements. The ? can be used as the placeholder.

<?php

public function setCategories ($conn, $ids) {
    $sql = "INSERT IGNORE INTO article_category (article_id, category_id VALUES "; // be attention to the space in the end

    $values = [];

    foreach ($ids as $ad) {
        $values[] = "({$this->id}, ?})";
    }

    $sql .= implode(", ", $values); // implode - join array elements with a gluestring. Syntax: implode(string $glue, array $pieces): string

    $stmt = $conn->prepare($sql);

    foreach ($ids as $i => $id) {
        $stmt->bindValue($i + 1, $id, PDO::PARAM_INT);
    }

    $stmt->execute();
}

Fetch data using prepared statements
The following example shows how to fetch data using prepared statements. The user input has automatically quoted, so there is no risk of a SQL injection attack.

<?php

$sql = "SELECT * FROM article where id = ?";

$stmt = $db->prepare($sql);

$stmt->bindValue(1, $id, PDO::PARAM_INT);

$stmt->setFetchMode(PDO::FETCH_CLASS, 'Article');

if ($stmt->execute()) {

    return $stmt->fetch();
}

If we need to get data from user input, we can also add validations like isset($_GET['id']) and is_numeric($_GET['id'])

Call a stored procedure with an output parameter

3. PDO class

Represents a connection between PHP and a database server.

Commonly used methods:

• PDO::__construct ( string $dsn [, string $username [, string $passwd [, array $options ]]] ) : Creates a PDO instance to represent a connection to the requested database.
• PDO::prepare ( string $statement [, array $driver_options = array() ] ) : PDOStatement
• PDO::query ( string $statement ) : PDOStatement. E.g.

  $sql = "SELECT *
        FROM category
        ORDER BY name";
  

$result = $conn->query($sql);

return $result->fetchAll(PDO::FETCH_ASSOC);

- <font color="MEDIUMSEAGREEN">`PDO::query ( string $statement , int $fetch_style = PDO::FETCH_COLUMN , int $colno ) : PDOStatement`</font>. E.g.
``` php
return $conn->query("SELECT COUNT(*) FROM article")->fetchColumn();

• PDO::lastInsertId ([ string $name = NULL ] ) : string : returns the ID of the last inserted row or sequence value.
  • If a sequence name was not specified for the name parameter, PDO::lastInsertId() returns a string representing the row ID of the last row that was inserted into the database.
  • If a sequence name was specified for the name parameter, PDO::lastInsertId() returns a string representing the last value retrieved from the specified sequence object. E.g.
    EXAMPLE

public function create($conn)
{
    if ($this->validate()) {

        $sql = "INSERT INTO article (title, content, published_at)
                VALUES (:title, :content, :published_at)";

        $stmt = $conn->prepare($sql);

        $stmt->bindValue(':title', $this->title, PDO::PARAM_STR);
        $stmt->bindValue(':content', $this->content, PDO::PARAM_STR);

        if ($this->published_at == '') {
            $stmt->bindValue(':published_at', null, PDO::PARAM_NULL);
        } else {
            $stmt->bindValue(':published_at', $this->published_at, PDO::PARAM_STR);
        }

        if ($stmt->execute()) {
            $this->id = $conn->lastInsertId();
            return true;
        }

    } else {
        return false;
    }
}

4. PDOStatement

Represents a prepared statement and, after the statement is executed, an associated result set.

Commonly used methods:

• PDOStatement::bindColumn ( mixed $column , mixed &$param [, int $type [, int $maxlen [, mixed $driverdata ]]] ) : bool : bind a column to a PHP available. E.g.

EXAMPLE

<? php
function readData($conn) 
{
    $sql = 'SELECT name, colour, calories FROM fruit';

    try {

        $stmt = $conn->prepare($sql);
        $stmt->execute();

        /* Bind by column number */
        $stmt->bindColumn(1, $name);
        $stmt->bindColumn(2, $color);

        /* Bind by column name */
        $stmt->bindColumn('calories', $cals);

        while ($row = $stmt->fetch(PDO::FETCH_BOUND)) {
            $data = $name . " " . $color . " " . $cals . "<br>";
            echo $data;
        }

    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}

• PDOStatement::bindParam ( mixed $parameter , mixed &$variable [, int $data_type = PDO::PARAM_STR [, int $length [, mixed $driver_options ]]] ) : bool : bind a parameter to the specified variable name. E.g.

  $calories = 150;
  $colour = 'red';
  

/ Execute a prepared statement with named placeholder /

$sql = ‘SELECT name, colour, calories
FROM fruit
WHERE calories < :calories AND colour = :colour’;
$stmt = $conn->prepare($sql);
$stmt->bindParam(‘:calories’, $calories, PDO::PARAM_INT);
$stmt->bindParam(‘:colour’, $colour, PDO::PARAM_STR, 12);
$stmt->execute();

/ Execute a prepared statement with question mark placeholders /
$stmt = $dbh->prepare(‘SELECT name, colour, calories
FROM fruit
WHERE calories < ? AND colour = ?’);
$stmt->bindParam(1, $calories, PDO::PARAM_INT);
$stmt->bindParam(2, $colour, PDO::PARAM_STR, 12);
$stmt->execute();

- <font color="MEDIUMSEAGREEN">`PDOStatement::bindValue ( mixed $parameter , mixed $value [, int $data_type = PDO::PARAM_STR ] ) : bool`</font> : bind a value to a parameter

<font color="tomato">**Difference between `bindParam` and `bindValue`**</font>


1. <font color="tomato">`bindParam`: only *variables* can be passed in; `bindValue`: both *variables* and *values* can be passed in.</font> 
2. <font color="tomato">`bindParam`: the variable is bound as a reference and will only be evaluated at the time that `PDOStatement:: execute()` is called.</font>

``` php
$value = 'foo';
$stmt = $conn->prepare('SELECT name FROM bar WHERE baz = :baz');
$stmt->bindParam(':baz', $value); // use bindParam to bind the variable
$value = 'foobarbaz';
$s->execute(); // executed with WHERE baz = 'foobarbaz'

$value = 'foo';
$stmt = $conn->prepare('SELECT name FROM bar WHERE baz = :baz');
$stmt->bindValue(':baz', $value); // use bindValue to bind the variable's value
$value = 'foobarbaz';
$s->execute(); // executed with WHERE baz = 'foo'

• PDOStatement::fetch ([ int $fetch_style [, int $cursor_orientation = PDO::FETCH_ORI_NEXT [, int $cursor_offset = 0 ]]] ) : mixed : fetch the next row from a result set. fetch_style includes:
  • PDO::FETCH_ASSOC: returns an array indexed by column name as returned in result set.
  • PDO::FETCH_BOUND: returns TRUE and assigns the values of the columns in result set to the PHP variables to which they were bound with the PDOStatement::bindColumn() method.
  • PDO::FETCH_CLASS: returns a new instance of the requested class, mapping the columns of the result set to named properties in the class, and calling the constructor afterwards.

    $stmt->setFetchMode(PDO::FETCH_CLASS, 'Article');
    $stmt->fetch();

    Examples

$sql = "SELECT name, color FROM fruit";
$stmt = $conn->prepare($sql);
$stmt->execute();

/* 1. PDO::FETCH_ASSOC */
$result = $stmt->fetch(PDO::FETCH_ASSOC);
/*
Array
(
    [name] => apple
    [color] => red
)
*/

/* 2. PDO::FETCH_BOTH */
$result = $stmt->fetch(PDO::FETCH_BOTH);
/*
Array
(
    [name] => banana
    [0] => banana
    [color] => yellow
    [1] => yellow
)
*/

• PDOStatement::fetchAll ([ int $fetch_style [, mixed $fetch_argument [, array $ctor_args = array() ]]] ) : array : return an array containing all of the result set rows.
  Examples

$sql = "SELECT name, color FROM fruit";
$stmt = $conn->prepare($sql);
$stmt->execute();

/* 1. Fetch all remaining rows in a result set*/

$result = $stmt->fetchAll();
/* $result: 
Array
(
    [0] => Array 
        (
            [name] => apple
            [0] => apple
            [color] => red
            [1] => red
        )
    [1] => ....

)

*/

/* 2. Fetch all values of a single column from a result set*/

$result = $stmt->fetchAll(PDO::FETCH_COLUMN, 0);
var_dump($result);

/* $result: 
Array(3)
(
    [0] => string(5) => apple
    [1] => string(4) => pear
    [2] => string(5) => grape
)

*/

/* 3. Group all values by a single column */
$insert = $conn->prepare("INSERT INTO fruit(name, colour) VALUES (?, ?)");
$insert->execute(array('apple', 'green'));
$insert->execute(array('pear', 'yellow'));

$stmt = $conn->prepare("SELECT name, colour FROM fruit");
$stmt->execute();

/* Group values by the first column */
$result = $stmt->fetchAll(PDO::FETCH_COLUMN|PDO::FETCH_GROUP);

/* $result:
array(3) {
  ["apple"]=>
  array(2) {
    [0] => string(5) "green"
    [1] => string(3) "red"
  }
  ["pear"]=>
  array(2) {
    [0] => string(5) "green"
    [1] => string(6) "yellow"
  }
  ["watermelon"]=>
  array(1) {
    [0] => string(5) "pink"
  }
}

*/

/* 4. Instantiating a class for each result */

class fruit
{
    public $name;
    public $color;
}

$stmt = $conn->prepare("SELECT name, color FROM fruit");
$stmt->execute();

$result = $stmt->fetchAll(PDO::FETCH_CLASS, "fruit");

/* $result
array(3) {
  [0]=>
  object(fruit)#1 (2) {
    ["name"]=>string(5) "apple"
    ["colour"]=>string(5) "green"
  }
  [1]=>
  object(fruit)#2 (2) {
    ...
  }
  ...
}

*/

• PDOStatement::fetchColumn ([ int $column_number = 0 ] ) : mixed : return a single column from the next row of a result set or FALSE if there are no more rows. Note:
  PDOStatement::fetchColumn() should not be used to retrieve boolean columns, as it is impossible to distinguish a value of FALSE from there being no more rows to retrieve. Use PDOStatement::fetch() instead.

Examples

$sql = "SELECT name, color FROM fruit";
$stmt = $conn->prepare($sql);
$stmt->execute();

$result = $stmt->fetchColumn(); /* first col of first row : name = lemon * /
$result = $stmt->fetchColumn(1); /\* second col of second row : color = red \*/

• PDOStatement::setFetchMode ( int $mode ) : bool : set the default fetch mode for this statement.
• PDOStatement::rowCount ( void ) : int : return the number of rows affected by the last SQL statement.

Examples

$del = $dbh->prepare('DELETE FROM fruit');
$del->execute();

/* Return number of rows that were deleted \*/
$count = $del->rowCount(); /\* 9 \*/