PDO were rows affected during execute statement

Question

I have found many ways to use the exec statement for PDO, but I'm not sure it helps me. My understanding is that I have to use the execute() function for prepared statements. I am updating a row with data from user input, so I would like to use a prepared statement instead of the query() call.

My code is as follows:

$dbh = buildDBConnector(); 
$sql = "UPDATE tb_users 
    SET authState=1
    WHERE id = ? AND authPass = ?";
$q = $dbh->prepare($sql);
$f = $q->execute(array($id,$authPass));
if($f){
    echo '<br />Success<br />';
}else{
    echo '<br />Failure<br />';
}

The issue is that the query itself is error free and executes fine, so there is no failure to store in $f. However, I need to know if it actually found the row to update, then successfully updated it. In other words, I need the affected rows. When googling and such, it keeps coming to the exec statement, but from my understanding, exec isn't for prepared statements? Any suggestions?

Answer 1

Try $q->rowCount(). Prepared statements will return the number of affected rows via that method.

Answer 2

A side note: when updating a table with identical values rowCount() will return always 0. This is normal behavior. You can change it yourself since PHP 5.3 by creating a PDO object with following attribute:

<? php
$p = new PDO($dsn, $user, $pass, array(PDO::MYSQL_ATTR_FOUND_ROWS => true));
?>

The rowCount() then will return how many rows your update-query actually found/matched.

Answer 3

$q->rowCount() returns the number of rows affected by the last (executed) SQL statement where $q is the prepared statement which is often called $stmt.

So most users who read this might want something like:

$pdo = new PDO($dsn, $username, $password);
$sql = "UPDATE tb_users  SET authState=1 WHERE id = ? AND authPass = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute(array($id, $authPass));

if ($stmt->rowCount()){
    echo 'Success: At least 1 row was affected.';
} else{
    echo 'Failure: 0 rows were affected.';
}

Answer 4

PDO's rowCount() from prepared statements returns affected rows if it's an UPDATE, DELETE or INSERT statement. Otherwise it returns how many rows are returned from SELECT statement.