RollBack () and beginTransaction() not work in my PHP PDO

Question

RollBack () and beginTransaction() not work in my PHP PDO and my table type is innoDB. In the following code my $sql1 is correct and my $sql2 is wrong (I added d to $last_id to just make it wrong). But it still executes sql1 meaning roll back no effect. Thank you for your advice.

<?php

include 'connect.php';
// Get multiple input field's value



try {
   // set the PDO error mode to exception
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);


   // Starts our transaction
   $conn->beginTransaction();

   foreach ($_POST['phone'] as $value) {

       $sql1 = "INSERT INTO tbl_contact_info (type)
       VALUES ('$value')";    

       // use exec() because no results are returned
       $conn->exec($sql1);
       $last_id = $conn->lastInsertId();

       $sql2="INSERT INTO tbl_img (img_type)
       VALUES ('$dlast_id')";    

       $conn->exec($sql2);    

   }
   // Commits out queries
   $conn->commit();
   echo "New record created successfully";    

}

catch(PDOException $e)
   {
   // Something borked, undo the queries!!    
   $conn->rollBack();    

   echo $sql . "<br>" . $e->getMessage();
   }

$conn = null;
?>

How is it wrong? `$dlast_id` doesn't exist, but PHP will simply pass in an empty string, and `INSERT INTO tbl_img (img_type) VALUES ('')` **is valid** syntax. — Blue, Jul 15 '18 at 08:20
You could add constraints, to prevent empty strings: https://stackoverflow.com/a/2514339/4875631 — Blue, Jul 15 '18 at 08:21
Thanks for your comments. Actually I intentionally added "d" to make it wrong and rollback but it did not rollback and execute sql2. — user9547961, Jul 16 '18 at 04:42
FrankerZ, now I just understand your comment so could you explain more further how to add constraints to my above code? — user9547961, Jul 17 '18 at 09:03

score 0 · Accepted Answer · answered Jul 15 '18 at 09:01

0

First of all (and sorry it it's obvious but I realised it's not always clear to everyone) SQL and PHP are different languages. MySQL Server will not react to Undefined variable notices triggered in PHP code.

Secondly, notices are not exceptions so they cannot be caught with try/catch statements. (You can certainly write a custom error handler that will throw exceptions on errors but it doesn't seem to be the case here.)

answered Jul 15 '18 at 09:01

Álvaro González

142,137
41
261
360

Thanks Alvaro, I don't understand much on your answer as I am a bit begginer to programming skill. However, I tried to make sql2 error but it didn't rollback. – user9547961 Jul 16 '18 at 04:48
The key point is that the issue is not related to SQL or PDO. I suggest you have a look at the [Exceptions](http://php.net/manual/en/language.exceptions.php) chapter. PHP core is from year 2005, when the language didn't have exceptions at all, what means that many basic errors (like `$dlast_id` instead of `$last_id`) will not throw exceptions at all, so you cannot try/catch it. – Álvaro González Jul 16 '18 at 06:36
Thank you again Alvaro. Now I got it. Actually, my rollback works properly. So is there any great way to prevent this empty inserting by using PHP without touching to edit database? – user9547961 Jul 17 '18 at 09:00
@user9547961 It depends on what you want to accomplish exactly. I would not waste energy trying to detect a typo like this on runtime: that the feature is not working at all becomes obvious as soon as you try it. Just ensure your development box is configured to display all errors. But you really need to use prepared statements. Not only it's security 101, but an undefined variable will render `null` so a simple `NOT NULL` column definition will totally avoid the insertion. – Álvaro González Jul 17 '18 at 09:33

RollBack () and beginTransaction() not work in my PHP PDO

1 Answers1