PDO binding example

Updated pritaeas 3 1K Views

The following snippet shows how you can use binding in your queries when using PDO. For starters, here's the table structure I've used:

CREATE TABLE `mytable` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `level` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

The snippet shows how to use binding for an INSERT and an UPDATE query. The same method can be used for a SELECT. Note that you can only use it to bind values to a query, you cannot use this to dynamically insert a table- or column name.

Comments and questions appreciated, as always.

<?php
// this example is an addition to using-pdo.php
// the table structure used is in sql/using-pdo-binding.sql

// the obvious lines will not be commented, as they are
// already explained in the other code snippet.

try {
    $pdo = new PDO('mysql:dbname=mydatabase;host=localhost', 'myuser', 'mypassword');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // these are the variables we are going to insert
    $name = 'pritaeas';
    $email = 'pritaeas@example.com';
    $dob = '2013-08-30';
    $level = 0;

    // insert a record
    // to use binding, use a colon followed by an identifier where you would normally insert a value
    $query = 'INSERT INTO `mytable` (`name`, `email`, `dob`, `level`) VALUES (:name, :email, :dob, :level)';

    // prepare the query for binding
    $statement = $pdo->prepare($query);

    // now bind the actual values to variables (see it as replacing the identifiers)
    $statement->bindValue('name', $name, PDO::PARAM_STR);
    $statement->bindValue('email', $email, PDO::PARAM_STR);
    $statement->bindValue('dob', $dob, PDO::PARAM_STR);
    $statement->bindValue('level', $level, PDO::PARAM_INT);

    // execute the query
    $result = $statement->execute();
    if ($result)
    {
        $lastId = $pdo->lastInsertId();
        echo '<p>Rows inserted: ' . $statement->rowCount() . '</p>';
        echo '<p>Last ID: ' . $lastId . '</p>';
    }
    else
    {
        echo '<p>Insert failed</p>';
    }

    // update the last record.
    // the same method works for update queries too.

    $level++;

    $query = 'UPDATE `mytable` SET `level` = :level WHERE `id` = :id';

    $statement = $pdo->prepare($query);

    // binding two integers now,
    $statement->bindValue('level', $level, PDO::PARAM_INT);
    $statement->bindValue('id', $lastId, PDO::PARAM_INT);

    $result = $statement->execute();
    if ($result)
    {
        echo '<p>Rows updated: ' . $statement->rowCount() . '</p>';
    }
    else
    {
        echo '<p>Update failed</p>';
    }

    unset($pdo);
}
catch (PDOException $exception) {
    echo '<p>There was an error connecting to the database!</p>';
    echo $exception->getMessage();
}
?>
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Edit Preview