How to check if update query was successful in php pdo

How can I test to see if the following query executed successfully?

$STH = $this->_db->prepare("UPDATE UserCreds SET
    VerificationString=:newVerificationString, ExpiryDate=:expiryDate 
    WHERE UserID = :userID;");
$STH->execute($params);

I know I can use lastInsertId() when I'm adding new rows, but what about UPDATEs and SELECTs?

Henders

1,1951 gold badge24 silver badges26 bronze badges

asked Mar 1, 2012 at 17:44

Chuck Le ButtChuck Le Butt

46.3k59 gold badges192 silver badges281 bronze badges

2

The execute returns true on success and false on failure.

From Docs:

Returns TRUE on success or FALSE on failure.

So you can make sure query ran successfully like:

if ($STH->execute($params))
{
  // success
}
else
{
  // failure
}

answered Mar 1, 2012 at 17:47

5

execute() returns true/false based on success/failure of the query:

$status = $STH->execute($params);

if ($status) {
   echo 'It worked!';
} else {
   echo 'It failed!';
}

One note: a select query which returns no rows is NOT a failure. It's a perfectly valid result that just happens to have NO results.

answered Mar 1, 2012 at 17:48

Marc BMarc B

351k42 gold badges402 silver badges486 bronze badges

0

simply i can count the number of row effected:

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

if($count =='0'){
    echo "Failed !";
}
else{
    echo "Success !";
}

answered Feb 21, 2017 at 17:17

3

This is best way to verify Doctrine query result return success or failed result and same way as per above suggest to check weather query returns Success on True and Failed on false.

    public static function removeStudent($teacher_id){
        $q = Doctrine_Query::create()
            ->delete('Student s')
            ->where('s.teacher_id = ?');

        $result = $q->execute(array($teacher_id));

        if($result)
        {
         echo "success";
        }else{
         echo "failed";
        }
    }

answered Apr 21, 2015 at 12:59

Nikhil ThombareNikhil Thombare

1,0002 gold badges9 silver badges25 bronze badges

0

1. UPDATE query with positional placeholders
2. UPDATE query with named placeholders
3. Comments (14)

First of all, make sure you've got a properly configured PDO connection variable that is needed in order to run SQL queries using PDO (and to inform you of the possible errors).

In order to run an UPDATE query with PDO just follow the steps below:

• create a correct SQL UPDATE statement
• replace all actual values with placeholders
• prepare the resulting query
• execute the statement, sending all the actual values to execute() in the form of array.

UPDATE query with positional placeholders

As usual, positional placeholders are more concise and easier to use

$sql = "UPDATE users SET name=?, surname=?, sex=? WHERE id=?";
$stmt= $pdo->prepare($sql);
$stmt->execute([$name, $surname, $sex, $id]);

or you can chain execute() to prepare():

$sql = "UPDATE users SET name=?, surname=?, sex=? WHERE id=?";
$pdo->prepare($sql)->execute([$name, $surname, $sex, $id]);

UPDATE query with named placeholders

In case you have a predefined array with values, or prefer named placeholders in general, the code would be

$data = [
    'name' => $name,
    'surname' => $surname,
    'sex' => $sex,
    'id' => $id,
];
$sql = "UPDATE users SET name=:name, surname=:surname, sex=:sex WHERE id=:id";
$stmt= $pdo->prepare($sql);
$stmt->execute($data);

or you can chain execute() to prepare():

$sql = "UPDATE users SET name=:name, surname=:surname, sex=:sex WHERE id=:id";
$pdo->prepare($sql)->execute($data);

Remember that you should't wrap every query into a try..catch statement. Instead, let a possible error to bubble up to either the built-in PHP's or your custom error handler.

Related articles:

• SELECT query with PDO
• INSERT query using PDO
• PDO Examples
• How to connect to MySQL using PDO
• Authenticating a user using PDO and password_verify()
• How to check if email exists in the database?
• Select the number of rows using PDO
• How to create a WHERE clause for PDO dynamically
• How to create a prepared statement for UPDATE query
• DELETE query using PDO
• Getting a nested array when multiple rows are linked to a single entry
• Adding a field name in the ORDER BY clause based on the user's choice
• How to execute 1000s INSERT/UPDATE queries with PDO?
• INSERT helper function for PDO Mysql
• PDO Examples
• PDO Examples

How can check insert query was successful in PHP PDO?

How to update query in pdo?

How to update data in php using form pdo?

How does PDO work in PHP?