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

13,196

Solution 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.

Solution 2

Here's my favorite PDO tutorial. It answers all your questions:

Share:
13,196
marked-down
Author by

marked-down

Software Developer

Updated on June 05, 2022

Comments

  • marked-down
    marked-down almost 2 years

    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?

  • marked-down
    marked-down over 12 years
    Great answer! But I'm getting the error PDOStatement::execute() expects parameter 1 to be array, string given with the line $stmt->execute($email);. Why does it expect $email to be an array?
  • Gromski
    Gromski over 12 years
    If you use the named parameters, execute expects an associative name => value array. Look at my code again, I'm using compact to create that array.