PDO prepared statements : How to execute, check affected rows, then fetch a field

Question

I'm very new to PDO - only being told to head in that direction this morning. So, hear me out. I'm trying to rewrite my login verification function from a standard mysql_query() to a PDO prepared statement, but I'm encountering some issues.

The function loginCheck() passes the supplied email and password, then grabs the salt from the matching email, if the number of affected rows of that query was 1, apply the variable $salt to the result of that query.

For the latter portion of the function, I was previously simply using:

// standard mysql query goes here

if (mysql_num_rows($query) == 1) {
    $salt = mysql_result($query, 0);
}

Now my entire function looks like:

// new mysql query below 

global $dbh;

$stmt = $dbh->prepare("SELECT `salt` FROM `users` WHERE `email`=? LIMIT 1");
$stmt->execute($email);

// not sure what to write here?

but I'm having trouble understanding how to translate the topmost portion of code to something similar in PDO. I'm also probably doing something else wrong here (as always), so point it out to me as well.

I've looked through the PHP manual and I simply cannot understand most of it. Any ideas?

Answer 1

I guess what you're looking for is PDOStatement::rowCount:

$stmt = $dbh->prepare("SELECT `salt` FROM `users` WHERE `email`=? LIMIT 1");
$stmt->execute($email);
if ($stmt->rowCount() == 1) {
    $salt = $stmt->fetchColumn(0);
}

I'd rather write this like this though:

$stmt = $dbh->prepare("SELECT `salt` FROM `users` WHERE `email`= :email LIMIT 1");
$stmt->execute(compact('email'));

$user = $stmt->fetch(PDO::FETCH_ASSOC);
if ($user) {
    // work with $user['salt']
}

Explicit naming is more robust than depending on column counts.

---

To understand the manual, you need to understand object oriented notation/concepts. The documentation for the PDO class looks like:

PDO {
   ...
   PDOStatement prepare ( string $statement [, array $driver_options = array() ] )
   ...
}

This means a PDO object ($dbh in your example), has a method prepare which returns a PDOStatement object. You're using it like this:

$stmt = $dbh->prepare(...);

So $stmt is a PDOStatement object. Knowing this you can look at the documentation for PDOStatement, and see that it has a method int PDOStatement::rowCount ( void ), which you can use.