PDO Getting Started Part 2 – PDOStatement

Apr 27

Introduction

Most of your work with PDO will center around the PDOStatement class. Of all of the classes in the PDO package this is the one that you will need to know the best. As such I will try to cover the core functions with a little more code then last time. I will not be covering all of the functions in the post as there isn’t much I will be able to expand upon from the PHP docs. My goal is to give you a mid level understanding of the methods and how/when to use them so learning the rest of the methods will be a lot easier for you. For this post I will be writing examples that center around pulling user data for an admin panel like section of a site.

PDOStatement::execute()

The execute method does exactly what it sounds like. It runs the query for its statement. Optionally when escaping parameters while using question marks you will pass them into execute as an array in the order they will be replaced in the query. Lets get right into the example with this one. First we will generate the statement object.


$stmt = $db->prepare('SELECT u.username, u.display_name FROM users u WHERE u.id = ?');

Notice the question mark? This is where PDOStatement will replace the value we tell it to run. By giving the value to PDO, we let PDO escape the value which helps to protect you against SQL injections. To tell PDOStatement which value to put in there we pass it in as an array to the execute function:

$userId = 10;
$stmt->execute(array($userId)); // runs - SELECT u.username, u.display_name FROM USERS u where u.id = 10;

If you don’t have any values to escape you just wouldn’t pass in the array parameter. It should also be noted that the return value of execute is a bool. TRUE if the query was successful FALSE if it wasn’t. The stumble here is it will ONLY return FALSE if the SQL has an error. So if the SQL is valid but return no rowset you will still get a TRUE return value. Keep this in mind when checking the return value.

rowCount

Row count will give you either the number of affected rows, or the number of returned rows depending on your query (Please note that in some systems it will only return affected rows when running DELETE, INSERT, and UPDATE queries. In MySQL running PHP 5.3 it will return the number or returned rows. Just keep in mind YMMV).

$stmt = $db->prepare('SELECT * FROM users WHERE role_id = 1'); // select all admins
$stmt->execute();
echo $stmt->rowCount(); // Echo the number of admins in the database
$stmt = $db->prepare('DELETE FROM users WHERE id = 10');
$stmt->execute();
echo $stmt->rowCount(); // Assuming there is a user with the ID of 10 this will echo 1

Binding input

Binding provides a way to assign values that need to be escaped and inserted into a SQL statement(Like the first question mark example we looked at). When using the methods bindParam or bindValue it expects named place holders. This can make larger queries easier to read. The only difference between bindParam and bindValue is bindParam passes the value you assign by reference and bindValue is not. This works well when you have stored procedures that have input/output values, but this is a topic for later. Lets convert the above example to use named place holders.

$stmt = $db->prepare('SELECT u.username, u.display_name FROM users u WHERE u.id = :userId');
$stmt->bindValue(':userId', 10);
$stmt->execute(); // Runs the same query as above.

Note that if you convert the above example to use bindParam it will throw a warning because you cannot pass a value by reference. Just keep this stumbling block in mind when writing your queries

Fetching data

So after all that lets finally get our data! PDOStatement provides a few different ways to fetch the data from your query. We will cover them one at a time

fetch

I think its fairly obvious just from the name what this does, so I wont say to much about it. I will note that once you use this function it moves on to the next result in the row set. If there isn’t another row it will return FALSE.

$stmt = $db->prepare('SELECT * FROM users WHERE role_id = 1'); // select all admins
$stmt->execute();

while($row = $stmt->fetch()) {
    echo $row['username'].'
'; // Just echo out the username in a list
}

The fetch method takes a few params but I will only cover the first. This param tells the method in what way to return the row values. By default it returns an array that includes numbered keys as well as keys with column names. In most cases you will use one or the other but not both. Lets look at some examples:

// ... build and execute statement
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    // This one will work exactly as above the returns row just will not have numbered keys
    echo $row['username'].'
'; // Just echo out the username in a list
}

// Option 2: fetch stdClass
while($row = $stmt->fetch(PDO::FETCH_OBJ)) {
    echo $row->username .'
';
}

// Option 3: this is the same as option 2 just using a built in PDOStatement method
while($row = $stmt->fetchObject()) {
    echo $row->username .'
';
}

PDOStatement has some other options for fetching types that we will cover in a little more advanced post.

Fetching EVERYTHING

The final option is to pull all the rows at once in an array. You do this through the fetchAll method. Again you can tell this method how to return the rows.

// ... build and execute statement
$rowSet = $stmt->fetchAll(); // This will give you an array of arrays
$rowSet = $stmt->fetchAll(PDO::FETCH_OBJ); // This will give you an array of stdClasses

Conculsion

I think we have covered quite a bit for this post and this is a good place to stop. The sad part is there is still quite a bit with building and executing queries to cover. I just don’t think its smart to try to do it all in one post. Feel free to ask questions about the topics I covered here. Also let me know if you think I should have covered anything that I didn’t.

8 comments

  1. MrShizzle /

    Just wanted to thank you for making these. Coming from a non-native english speaker, they’re very easy to read and understand. I took a PHP course last year and they taught us old SQL statements. I’ll be using PDO from now on.

  2. I appreciate your thanks. Just glad I could help.

  3. Make sure that you save your input data in UTF8 right away. It will save you a lot of headache later.

    I.e
    $dbh = new PDO(……….
    $dbh->exec(“SET NAMES ‘utf8′”);
    $dbh->exec(“SET CHARACTER SET utf8″);
    $dbh->exec(“SET character_set_server=utf8″);

  4. Charlie /

    I’ve been using the old MySQL extensions forever and I know it inside and out. I’ve been wanting to switch to PDO, and your tutorials finally made me do so. Very clear, concise and informative without all the fluff and useless nonsense others have had. I finished converting two sites without issue thanks to you!

  5. Not a problem, Glad I could help.

  6. Add me to the list of people thanking you for these two posts. ;) I’ve long just used the mysql_ extension, which PHP is now “soft-deprecating”, so I decided that for my new project I should start with PDO instead.

    Gave me a good introduction, so thanks!

    On a side note, the link to this post on the first PDO introduction post doesn’t actually lead you here, so you might want to fix that one. :)

  7. Dr. P /

    This post is really concise and straight forward .. PDO was very new to me..after reading it .. I needed some further clarification and got more detailed breakdown.. from http://www.phpro.org/tutorials/Introduction-to-PHP-PDO.html then came back and reread this tutorial and it made so much more sense.. thanks for the post

Leave a Reply