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?

Warning: if the value is *already* set (the updated is executed twice, maybe from a double clicked submit button) the second time would cause an error even though the data is fine. For user experience, it might be more reliable to do a select statement afterwards to see if the record exists with the correct value `select authState from tb_users where id = ? and authPass = ?`. No results means the id or password does not exist. (p.s. I hope you hash the passwords! look at pbdkf2!) — Frank Forte, Apr 27 '17 at 14:47

score 64 · Accepted Answer · edited Jun 22 '16 at 14:03

64

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

edited Jun 22 '16 at 14:03

Sylverdrag

8,898
5
37
54

answered May 09 '12 at 19:08

Marc B

356,200
43
426
500

13

keep in mind that by default, MySQL returns the number of rows actually changed, and not the number of rows matched by the where clause (issuing update with the same values as the existing ones do not change the row) – Maxim Krizhanovsky May 09 '12 at 19:11
1

I know a lot of people look for this question judging by things I've read throughout the web, great job on the quick response! The $f ended up not necessary when it was all said and done, can ended as follows: if($q->rowCount()) { ... – MaurerPower May 09 '12 at 19:37
6

USE: $q->rowCount() THIS GIVES AN ERROR: $f->rowCount() – The D Merged Mar 06 '14 at 14:21
1

yep fell for it. use $q->rowCount(), can this answer be changed to the right one? – jdog Nov 10 '15 at 01:17

score 30 · Answer 2 · answered Apr 03 '17 at 08:53

30

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.

answered Apr 03 '17 at 08:53

Bud Damyanov

30,171
6
44
52

4

I had no idea this was a thing!! All of the yes! – Gurnzbot May 29 '17 at 17:21

score 27 · Answer 3 · edited Jan 20 '22 at 10:01

$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.';
}

score 2 · Answer 4 · edited Apr 09 '22 at 14:38

2

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.

edited Apr 09 '22 at 14:38

Nabeel Khan

3,715
2
24
37

answered Apr 25 '15 at 01:44

Emre Aydin

569
2
16

score -1 · Answer 5 · answered Feb 01 '23 at 14:48

para evitar que la actualización retorne 0, deberás añadir algo al final de la cadena de conexión. Conexion.php

<?php $cadena = "$manejador:host=$servidor;dbname=$dbname";
    $cnx = new PDO($cadena, $usuario, $pass, array(PDO::MYSQL_ATTR_FOUND_ROWS => true));
?>

clase.php

<?php
 ...
 global $cnx;
 $pre = $cnx->prepare($sql);
 $pre->execute($parametros);
 $rpta = $pre->rowCount();
 return $rpta;
?>

Do Hoa Vinh · Answer 6 · 2014-09-28T01:22:20.077

-6

i think PDO rowCount() is useless in MySQL with single UPDATE query. because it always return 0;

ex:
TABLE{id=1, col1="A"}
UPDATE  TABLE SET col1="AA" WHERE id=1;
rowCount will return 0;

also
UPDATE  TABLE SET col1="AA" WHERE id=999;
rowCount will return 0;

so rowCount() is useless in this case.

i have not tested yet with this query UPDATE TABLE SET col1="AA"

edited Sep 28 '14 at 01:22

answered Sep 28 '14 at 01:16

Do Hoa Vinh

356
4
11

1

This returns 0 as MySQL is clever enough to check if the value is still the same as the initial and ignore the update – Nixon Kosgei Nov 19 '20 at 07:35

PDO were rows affected during execute statement

6 Answers6

Linked

Related