Pdo MySQL: How to Know If Insert Was Successful

PDO mysql: How to know if insert was successful

PDOStatement->execute() returns true on success. There is also PDOStatement->errorCode() which you can check for errors.

check pdo php insert success

You don't need it.

There are three possible scenarios for handling the result of insert operation in PDO:

1. To tell the success, no verification is needed. Just keep with your program flow.
2. To handle an unexpected error, keep with the same - no immediate handling code is needed. An exception will be thrown in case of a database error, and it will bubble up to the site-wide error handler that eventually will result in a common 500 error page.
3. To handle an expected error, like a duplicate primary key, and if you have a certain scenario to handle this very error - then use a try..catch operator.

For a regular PHP user it sounds a bit alien - how's that, not to verify the direct result of the operation? - but this is how exceptions work - you check the error somewhere else. Once for all. Extremely convenient.

So, in a generic case you don't need any handling code at all. Just keep your code as is.

I wrote an article on PHP error reporting basics explaining the matter in detail, you may find it useful

Only in case you have a handling scenario other than just reporting the error, you could catch an error. To rollback a transaction for example. The code is taken from my PDO tutorial:

try {
    $pdo->beginTransaction();
    $stmt = $pdo->prepare("INSERT INTO users (name) VALUES (?)");
    foreach (['Joe','Ben'] as $name)
    {
        $stmt->execute([$name]);
    }
    $pdo->commit();
}catch (Exception $e){
    $pdo->rollback();
    throw $e;
}

How to tell if PDO MySQL INSERT was successful with IGNORE

Try:

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

That will tell you how many rows were affected by the last query.

Confirm record INSERT with PDO and MySQL

PDO::rowCount will return the number of affected rows.

So you could: echo $stmt->rowCount();

Syntax for if/else statement if insert was successful in a PDO prepared statement

There are a number of ways you can check whether an INSERT worked correctly.

1. Return value from $stmt->execute()

As you said, $stmt->execute(); returns true on success or false on failure. So you can use:

$result = $stmt->execute();
if ($result) {...}

PDO Execute documentation here

2. Row Count

rowCount will return the number of rows afffected by a query. After a successful insert, this should be 1.

$stmt->execute();
$affected_rows = $stmt->rowCount();
if ($affected_rows == 1) {...} 

PDO rowCount documentation here

3. Last Inserted Id

If your table has an ID column, you can return the ID of the last inserted row using lastInsertId().

$stmt->execute();
$newCustomerInfoId = $pdo->lastInsertId();
if ($newCustomerInfoId) {...}

Note: You must call lastInsertId on the PDO object, not the $stmt.

PDO lastInsertId documentation here

Insert data to MySql DB and display if insertion is success or failure

$result = mysql_query("INSERT INTO PEOPLE (NAME ) VALUES ('COLE')"));
if($result)
{
echo "Success";

}
else
{
echo "Error";

}

How to know if PDO inserted a record on a table with a composite key?

PDO::lastInsertId appears to only work on auto incremented PKs, however, this requirement does not seem to be documented very well at http://php.net/manual/en/pdo.lastinsertid.php. Furthermore, it does not appear to work with composite keys.

pdo::rowCount() is required. See http://php.net/manual/en/pdostatement.rowcount.php. Thanks given to AbraCadaver and Ryan Vincent.

$sql='INSERT INTO docx_priv_projects (documents_id,projects_id)
SELECT :doc_id,t.id
FROM projects AS t INNER JOIN entities AS e ON e.id=t.id
WHERE t.id=:id AND e.record_status='active' AND e.sites_id=:sites_id';
$stmt = $conn->prepare($sql);
$stmt->execute(array('id'=>1379519490 ,'sites_id'=>2416619273,'doc_id'=>2972614382));
$x=$stmt->rowCount();  //Will be `1` if added

How to tell when query executed successfully in PHP PDO?

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
}

---

Related Topics