How to check adding to database in PDO?

O

Optimus2014-07-13 14:36:11

PHP

Optimus, 2014-07-13 14:36:11

The subject is in the subject ...
I used to write like this:

if ( -1 !== mysql_affected_rows() ) {
            echo 'Вы успешно зарегистрированы!';
        } else {
            echo 'Ошибка при регистрации';
        }

In PDO I read there is such a thing as the number of the last added record:
$insertId=$db->lastInsertId();
But if the insertion fails, it will simply return the previous one to me and that's it ...

Reply

Answer the question

In order to leave comments, you need to log in

4 answer(s)

A

Alexey, 2014-07-13
Pyan @marrk2

Is it really that hard to read the manual ?

D

Dmitry Entelis, 2014-07-13
@DmitriyEntelis

Using lastInsertId to check if the insert succeeded or not is a bad idea.
Small example from php.net

Example

Example on a new Row: 
<?php 
$sql = "INSERT INTO city (`city`) VALUES ('Paris') ON DUPLICATE KEY UPDATE `city` = 'Paris"; 
$dbh->query($sql); 
echo $dbh->lastInsertId(); 
?> 

Above displays: 1 
Expected display: 1 

Example on an existing row that gets updated: 
<?php 
$sql = "INSERT INTO city (`city`) VALUES ('Paris') ON DUPLICATE KEY UPDATE `city` = 'Paris"; 
$dbh->query($sql); 
echo $dbh->lastInsertId(); 
?> 
Above displays: 2 
Expected display: 1 (since no new records were inserted)

The behavior is because innodb always increments the auto-increment counter whether the insert succeeds or fails.
In my opinion, if you need to check the success of the insert, it is better to use transactions, but there are nuances there too (an example from the same place)

<?php 
try { 
    $dbh = new PDO('mysql:host=localhost;dbname=test', 'username', 'password'); 

    $stmt = $dbh->prepare("INSERT INTO test (name, email) VALUES(?,?)"); 

    try { 
        $dbh->beginTransaction(); 
        $tmt->execute( array('user', '[email protected]')); 
        $dbh->commit(); 
        print $dbh->lastInsertId(); 
    } catch(PDOExecption $e) { 
        $dbh->rollback(); 
        print "Error!: " . $e->getMessage() . "</br>"; 
    } 
} catch( PDOExecption $e ) { 
    print "Error!: " . $e->getMessage() . "</br>"; 
} 
?>

S

Sergey, 2014-07-13
Protko @Fesor

lastInsertId should be called when you have verified that the data has been written to the database. And you can make sure by simply checking that there were no errors when executing the request. mysql_affected_rows is not needed here, it is enough to check for errors after exec, or even better - tell PDO to throw exceptions and process them if something happens.

F

FanatPHP, 2014-07-16
@FanatPHP

How to check adding to database in PDO?

For some reason, newbies ask this question all the time.
But at the same time, no one can answer WHY it is necessary to check the success of the insert.
Just to write the sacramental phrase 'Registration failed'?
Well, that is, it is clear that in the manual from the last century there was a code -1 !== mysql_affected_rows(), and it must be dragged to a new project. But sometimes you have to think - what is the point in those actions that you perform. What use case should lead to the need for such an error. And how to process it humanly.
For those who want to live in the 21st century, and not be stuck forever in the 20th century, here are a few clarifications:
1. You never need to check the result of PDO functions.
2. If you want to display a message like "something broke", you should not write it after each function, but do it CENTRALIZED, in the error
handler how it should be handled - you can use try-catch.
Everything. In addition to these three points, there should be no checks for the success of the insert