A quick intro to using PHP PDO with MySQL

Post to Twitter

PHP PDO (PHP Data Objects) ship with PHP 5.1 and above and is very easy to work with. Today I’m going to show you one way of performing CRUD actions on a MySQL database using PDO. Keep in mind what I’ll show you is only one way of doing things and just a fraction of what PDO can do.


PDO supports numerous drivers but I’m going to choose MySQL today. Connecting to a MySQL database is as easy as this:

// Don't do this obviously, just for demo purposes
$user = "root";
$pass = "password";

// Connect
$db = new PDO("mysql:host=localhost;dbname=pdo-demo", $user, $pass);

// Disconnect
$db = null;

I’m going to setup a simple database called PDO-Demo and you can use the following script to generate a copy of the users table which will live in the PDO-Demo database:

CREATE TABLE  'pdo-demo'.'users' (
'id' INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
'firstname' VARCHAR( 30 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
'lastname' VARCHAR( 30 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
'email' VARCHAR( 160 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;

Now I’ll create a prepared statement which will be used to insert data in the users table. Prepared statements should be used when you plan to use the same SQL statement numerous times. In my demo later on I’m only calling insert twice so I don’t save much, but if I was making hundreds or thousands of calls then the prepared statement which is precompiled would shave off the time it took to complete the task.

Here is how my code for inserting looks using named placeholders:

$stmt = $db->prepare("INSERT INTO users (firstname, lastname, email) value (:firstname, :lastname, :email)");

$stmt->bindParam(':firstname', $firstname);
$stmt->bindParam(':lastname', $lastname);
$stmt->bindParam(':email', $email);

$firstname = "John";
$lastname = "Doe";
$email = "johndoe@nowhere123.com";
$stmt->execute();

If you want to get the new id of the record we just inserted you can call lastInsertId:

$db->lastInsertId()

Note: This will work with MySQL in my demo here but keep in mind that this might not work on all databases. The PHP website makes a special note about this: “This method may not return a meaningful or consistent result across different PDO drivers, because the underlying database may not even support the notion of auto-increment fields or sequences.”

To query and display the data I can do this:

// QUERY
$stmt = $db->query('SELECT * from users');

// Set the fetch mode
$stmt->setFetchMode(PDO::FETCH_ASSOC);

while($row = $stmt->fetch())
{
    echo "<p>" . $row['firstname'] . "&nbsp;" . $row['lastname'] . "</p>";
    echo "<p>" . $row['email'] . "</p><br />";
}

Updating the data is essentially the same steps as doing an insert (in this case again using named placeholders):

// UPDATE
$stmt = $db->prepare("UPDATE users set email = :email where lastname=:lastname");

$stmt->bindParam(':lastname', $lastname);
$stmt->bindParam(':email', $email);

$lastname = "Doe";
$email = "johndoe@somewhere444.com";
$stmt->execute();

Deleting can be done as easy as this:

$db->exec("DELETE FROM users");

You can of course use named placeholders if you want to.

My demo that makes use of everything shown here looks like this:

<?php
  try
  {
    // Don't do this obviously, just for demo purposes
    $user = "root";
    $pass = "password";
    
    $db = new PDO("mysql:host=localhost;dbname=pdo-demo", $user, $pass);

    // INSERT
    echo "***************** INSERT *****************<br />";
    $stmt = $db->prepare("INSERT INTO users (firstname, lastname, email) value (:firstname, :lastname, :email)");

    $firstname = "";
    $lastname = "";
    $email = "";

    $stmt->bindParam(':firstname', $firstname);
    $stmt->bindParam(':lastname', $lastname);
    $stmt->bindParam(':email', $email);

    $firstname = "John";
    $lastname = "Doe";
    $email = "johndoe@nowhere123.com";
    $stmt->execute();
    
    echo "<p>John Doe Insert Id: " . $db->lastInsertId() . "</p>";    
    
    $firstname = "Jane";
    $lastname = "Jones";
    $email = "janejones@nowhereABC.com";
    $stmt->execute();
    
    echo "<p>Jane Jones Insert Id: " . $db->lastInsertId() . "</p>";
    
        echo "***************** QUERY *****************<br />";
    // QUERY
    $stmt = $db->query('SELECT * from users');

    // Set the fetch mode
    $stmt->setFetchMode(PDO::FETCH_ASSOC);

    while($row = $stmt->fetch())
    {
        echo "<p>" . $row['firstname'] . "&nbsp;" . $row['lastname'] . "</p>";
        echo "<p>" . $row['email'] . "</p><br />";
    }
    
    echo "***************** UPDATE *****************<br />";
    // UPDATE
    $stmt = $db->prepare("UPDATE users set email = :email where lastname=:lastname");

    $stmt->bindParam(':lastname', $lastname);
    $stmt->bindParam(':email', $email);

    $lastname = "Doe";
    $email = "johndoe@somewhere444.com";
    $stmt->execute();
    echo "<p>Update completed</p>";
    
    echo "***************** QUERY (CHECK FOR UPDATED EMAIL) *****************<br />";
    // QUERY (again)
    $stmt = $db->query('SELECT * from users');

    // Set the fetch mode
    $stmt->setFetchMode(PDO::FETCH_ASSOC);

    while($row = $stmt->fetch())
    {
        echo "<p>" . $row['firstname'] . "&nbsp;" . $row['lastname'] . "</p>";
        echo "<p>" . $row['email'] . "</p><br />";
    }    
    
    echo "***************** DELETE *****************<br />";
    // DELETE
    $count = $db->exec("DELETE FROM users");
    
    echo "<p>" . $count . " records deleted</p>";
    
    $db = null;

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

The result should be:

Post to Twitter

This entry was posted in Open Source, PHP. Bookmark the permalink.

5 Responses to A quick intro to using PHP PDO with MySQL

  1. Madhav Vyas says:

    I appreciate your efforts. It is very helpful who wants to use PDO driver for php core projects.

    Thanks for sharing such nice code…..

  2. Excellent little article for the beginner with PDO. One constructive comment is that you may also want to educate users on catching PDOExceptions.

    In some instances where a connection fails because of a server overload (or other error), the PDO schema is exposed with the username and password intact.


    try {
    $db = new PDO("mysql:host=localhost;dbname=pdo-demo", $user, $pass);
    } catch {PDOException $e) {
    $e->getMessage();
    }

  3. Chad Lung says:

    @Andrew,

    Excellent point, thanks.

    Chad

  4. Pingback: Chad Lung’s Blog: A quick intro to using PHP PDO with MySQL | Scripting4You Blog

  5. Pingback: Getting basic error information from PHP PDO | Giant Flying Saucer

Comments are closed.