updating records with prepared statements, checking if update worked

Question

I have a query that updates a record on my database, it works fine but i wanted to know how to check if the update has happened so i can return true and display the right message?

Now i know with a SELECT query i can do:

if(stmt->fetch())

If that is true i return true and saying "records found" but i haven't got a clue how to do it for an update query?

Anyone know how to?

$query = "UPDATE user
            SET password = ?
            WHERE email = ?";

if($stmt = $conn->prepare($query)) 
{
    $stmt->bind_param('ss', $pwd, $userEmail);
    $stmt->execute();

    //Check if update worked
}

Thanks for the help.

@rage do you know why your currently accepted answer is not correct? Do you know that you can move the green tick? — mickmackusa, Apr 10 '20 at 05:39
Does this answer your question? [mysqli\_fetch\_assoc() expects parameter / Call to a member function bind\_param() errors. How to get the actual mysql error and fix it?](https://stackoverflow.com/questions/22662488/mysqli-fetch-assoc-expects-parameter-call-to-a-member-function-bind-param) — Dharman, Aug 10 '20 at 19:54

dani herrera · Answer 1 · 2015-07-21T15:54:29.873

Execute method returns True when it finished successfully, but, if this behavior is not enough for you, you can check also for affected rows:

$query = "UPDATE user
            SET password = ?
            WHERE email = ?";

if($stmt = $conn->prepare($query)) 
{
    $stmt->bind_param('ss', $pwd, $userEmail);
    if ($stmt->execute()) {
        //query with out errors:
        printf("rows updateds: %d\n", $stmt->affected_rows);
    } else {
        //some error:
        printf("Error: %s.\n", $stmt->error);
    }
}

The second check you can do is to verify that exactly 1 row was updated:

if($stmt = $conn->prepare($query)) 
{
    $stmt->bind_param('ss', $pwd, $userEmail);
    if ($stmt->execute() and $stmt->affected_rows == 1) {
        //your update is succesfully.
    }
}

score 5 · Accepted Answer · edited Dec 25 '20 at 12:18

5

check if below works:

$query = "UPDATE user
        SET password = ?
        WHERE email = ?";

if($stmt = $conn->prepare($query)) {
    $stmt->bind_param('ss', $pwd, $userEmail);
    if ($stmt->execute()) {
        // Worked...
    } else {
        // Didn't work...
    }
}

edited Dec 25 '20 at 12:18

Steven

6,053
2
16
28

answered Jan 19 '12 at 14:19

Fahim Parkar

30,974
45
160
276

3

It would always work.. Because it "tried" and sucessfully ran the query ( where it might have Affected or NOT affected a row). – alexandreferris Jun 03 '15 at 17:41
3

Why is it the accepted answer? `$stmt->execute()` will return false only if something prevented the execution of the query (i.e. internet connection problem). It has nothing to do with the row being updated or not. – pmrotule Sep 05 '16 at 11:03

score 1 · Answer 3 · answered Jul 24 '16 at 17:41

A few years late, but perhaps this could help someone..

As others have mentioned already you can use affected_rows to check if the UPDATE query in a PREPARED STATEMENT has indeed updated any record. However, do note that if the submitted data is the same as the record in the database 'affected_rows' will return a zero (0).

A workaround of mine is creating a column for TIMESTAMP with ON UPDATE CURRENT_TIMESTAMP. Then every time I run the query, I'll also pass on a NULL value to the column tracking the time - that way forcing the row to UPDATE every time the query is executed. Then all you do is check for affected_rows.

score -1 · Answer 4 · answered Jan 19 '12 at 14:15

-1

Would the mysql_affected_rows() function work for you?

answered Jan 19 '12 at 14:15

scott

1,068
1
9
20

1

The basic mysql extension doesn't support prepared statements, so OP doesn't have access to the mysql_affected_rows() function.... assume you mean mysqli_affected_rows() instead – Mark Baker Jan 19 '12 at 14:18

updating records with prepared statements, checking if update worked

4 Answers4

Linked