BeginTransaction can not work in PHP mysql

Question

I want to insert data into CLOUD and IDC tables.

Cloudid is the foreign key of IDC table, so i want to use transaction.

Before $conn->beginTransaction(); and $conn->commit(); are added ,it works fine, but without them, it works fine.

Here is my code:

<?php
if($_GET["act"]=="add")
{
      try
      {
        $conn=new PDO('mysql:host=localhost;port=3306;dbname=xxx' , 'root' , 'xxxx');

        //$conn->beginTransaction();
        $query="
             insert into CLOUD (name,date) VALUES('".$_POST['customerName']."','".$_POST['firstDay']."');
             insert into  IDC (name,id,phone,cloudid) VALUES('".$_POST['engName3']."','".$_POST['engID3']."','".$_POST['engPhone3']."',LAST_INSERT_ID());                 
             insert into  IDC (name,id,phone,cloudid) VALUES('".$_POST['engName4']."','".$_POST['engID4']."','".$_POST['engPhone4']."',LAST_INSERT_ID());
             ";

        $stmt=$conn->query($query);
        //$conn->commit();

        echo "success";
      }
      catch(PDOException $e)
      {
         $conn->rollBack();
         echo "connect failed!".$e->getMesage();
         exit;
      }  

}
?>

what error do you get when you use `beginTransaction()` and `commit()` ? — ᴄʀᴏᴢᴇᴛ, Aug 09 '17 at 08:33

ᴄʀᴏᴢᴇᴛ · Answer 1 · 2017-08-09T08:31:14.490

0

Transactions are only available when the database uses InnoDB as the storage engine. You are probably using MyISAM

Have a look here for more details on the difference between the storage engines https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html

As a side note, your code is vulnerable to SQL injections because you use raw post data in non prepared queries. you should have a look there : How can I prevent SQL injection in PHP?

edited Aug 09 '17 at 08:31

answered Aug 09 '17 at 08:15

ᴄʀᴏᴢᴇᴛ

2,939
26
44

@CROZET,mysql> SHOW ENGINES\g, it shows InnoDB | DEFAULT .So my database uses InnoDB as the storage engine.Beside, when $query="begin;.......commit;" conn->query($query);, it works fine. – stack Aug 09 '17 at 08:25
@CROZET, show create table, IDC and CLOUD are ENGINE=InnoDB.So it doesn't matter about storage engine. – stack Aug 09 '17 at 08:49
@stack you still have not answered the questions in the question comment : what happens whan you use the transaction and what error do you get ? – ᴄʀᴏᴢᴇᴛ Aug 09 '17 at 09:26
@CROZET, $conn->commit(); can not pass. I have tested, $conn->beginTransaction();$stmt=$conn->query($query) can pass.There is no error information. Just hold on at $conn->commit(); – stack Aug 09 '17 at 09:46

Лев Макаренко · Answer 2 · 2017-08-09T09:37:31.910

Try to initiate your connection and get last inserted id like below, also i would recommend you to use prepared statements (see http://php.net/manual/ru/pdo.prepare.php):

if($_GET["act"]=="add")
{      
    try {
      $dbh = new PDO('mysql:host=localhost;port=3306;dbname=xxx', 'root', 'xxx', 
          array(PDO::ATTR_PERSISTENT => true));
      echo "Connected\n";
    } catch (Exception $e) {
      die("Unable to connect: " . $e->getMessage());
    }

    try {  
        $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

        $dbh->beginTransaction();

        $stmt = $dbh->prepare('insert into CLOUD (name,date) VALUES(:customerName,:firstDay)');
        $stmt->execute([
            'customerName' => $_POST['customerName'],
            'firstDay' => $_POST['firstDay']
        ]);

        $cloud_id = $dbh->lastInsertId();

        $stmt = $dbh->prepare('insert into IDC (name,id,phone,cloudid) VALUES(:name,:id,:phone,:cloudid)');
        $stmt->bindParam(':name', $name);
        $stmt->bindParam(':id', $id);
        $stmt->bindParam(':phone', $phone);
        $stmt->bindParam(':cloudid', $cloud_id);

        $name = $_POST['engName3'];
        $id = $_POST['engID3'];
        $phone = $_POST['engPhone3'];        
        $stmt->execute();

        $name = $_POST['engName4'];
        $id = $_POST['engID4'];
        $phone = $_POST['engPhone4'];        
        $stmt->execute();        

        $dbh->commit();

    } catch (PDOException $e) {
      $dbh->rollBack();
      echo "Failed: " . $e->getMessage();
    }
}

BeginTransaction can not work in PHP mysql

2 Answers2