Check if update succeeded and echo

Question

I have a basic update query, I want to echo if it was successful so I've tried variations on this

$sql = 'UPDATE storeroom
    SET fruit=?
    WHERE stock_id =?
    AND stock_status <> \'supplied\'';

$stmt = $dbh->prepare($sql);
$stmt->execute($v);

$count = $stmt->rowCount();
}

if ($count < 1) {
echo 'Unknown Stock ID or status change failed.';
}
else {
echo 'Status change successful.';
}

But it fails on the pg_affected_rows expecting a resource.

First time I'm using pg_affected rows which I found here

If I substitute the affected rows for rowcount I get a different error

Have you tried putting $stmt instead of $sql in pg_affected_rows($stmt); — User1010, Jul 25 '21 at 11:27
Please make sure that you understand that PDO connection has nothing in common with `pg_` functions. — u_mulder, Jul 25 '21 at 11:28
@u_mulder so I can't use pg_affected_rows to see if it updated successfully? — Kilisi, Jul 25 '21 at 11:29
Obviously not, for PDO you should use [rowCount](https://www.php.net/manual/ru/pdostatement.rowcount.php) — u_mulder, Jul 25 '21 at 11:30
Where are the parameters for those placeholders? What is `$v`? — mickmackusa, Jul 25 '21 at 11:38
`$sql` is a __string__. Please open the manual, __read it__. You will see that correct usage is `$stmt->rowCount()`. — u_mulder, Jul 25 '21 at 11:39
We don't need to post another `rowCount()` answer -- we have plenty of pages on Stack Overflow that give this advice. — mickmackusa, Jul 25 '21 at 12:02

Stefanov.sm · Accepted Answer · 2021-07-25T11:56:12.873

You may also try an alternative of rowCount - the SQL statement itself returns the number of updated rows. Here is the SQL:

with t as
(
 UPDATE storeroom
    SET fruit = ?
    WHERE stock_id = ?
    AND stock_status <> 'supplied' 
    RETURNING stock_id 
) select count(*) from t;

and

$sql = <<<SQL
with t as
(
 UPDATE storeroom
    SET fruit = ?
    WHERE stock_id = ?
    AND stock_status <> 'supplied'
    RETURNING stock_id 
) select count(*) from t;
SQL;
$stmt = $dbh -> prepare($sql);
$stmt -> execute($v);
$count = $stmt-> fetchColumn();
...

score -2 · Answer 2 · answered Jul 25 '21 at 11:46

-2

I think this will do the trick, can depend on PHP version which I am not aware of:

$affected = $stmt->affected_rows;

answered Jul 25 '21 at 11:46

Peter Koltai

8,296
2
10
20

`affected_rows` is for `mysqli` connection. We have found in comments that OP has PDO. – u_mulder Jul 25 '21 at 11:49
I have postgresql – Kilisi Jul 25 '21 at 11:50
oh, my bad, I missed that it is PDO – Peter Koltai Jul 25 '21 at 12:02

Check if update succeeded and echo

2 Answers2