PHP/MySQL - How to use MySQL BEGIN/COMMIT with PDO

Question

I was wondering how to use a MySQL BEGIN/COMMIT with a PDO. I've read that it's best to create a query that either inserts all data or none at all to provide 'consistency' in the database Here's my code

$query = $db -> prepare 
                        ("
                        BEGIN;
                        INSERT INTO chat (chat_id,msg,datetime) 
                        VALUES (:cid,:msg,:datetime)
                        INSERT INTO chat_connect (chat_id,sender_id,receiver_id)
                        VALUES (:cid2,:sender_id,:receiver_id);
                        COMMIT;
                        ");
$query -> execute(array(
            "cid" => $cid,
            "msg" => $msg,
            "datetime" => $datetime,
            "sender_id" => $getid,
            "receiver_id" => $frid,
            "cid2" => $cid
            ));

Your Common Sense · Accepted Answer · 2016-10-12 06:25:18Z

1

Your code would work only if emulation mode is turned ON.

Otherwise you have to run your queries in separate calls like this.

$db->query("BEGIN");
$stmt = $db->prepare("INSERT INTO chat (chat_id,msg,datetime) VALUES (:cid,:msg,:datetime)");
$stmt->execute(...);
$stmt = $db->prepare("INSERT INTO chat_connect (chat_id,sender_id,receiver_id)
                        VALUES (:cid2,:sender_id,:receiver_id)");
$stmt->execute(...);
$db->query("COMMIT");

this is a general rule dor running miltiple-query statements in PHP.

However, in case of a transaction, instead of SQL commands BEGIN and COMMIT you can use their PDO counterparts.

edited Oct 12, 2016 at 6:25

answered Oct 12, 2016 at 6:16

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:07:02Z

Transaction syntax:

START TRANSACTION [transaction_characteristic [, transaction_characteristic] ...]

transaction_characteristic: WITH CONSISTENT SNAPSHOT | READ WRITE | READ ONLY

BEGIN [WORK] COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE] SET autocommit = {0 | 1}

Transaction example:

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

Taken from here.

You intend to create a transaction via PDO. That is not really a problem. You can do it by generating the query text accordingly:

$query = $db -> prepare 
                        ("
                        START TRANSACTION;
                        INSERT INTO chat (chat_id,msg,datetime) 
                        VALUES (:cid,:msg,:datetime)
                        INSERT INTO chat_connect (chat_id,sender_id,receiver_id)
                        VALUES (:cid2,:sender_id,:receiver_id);
                        COMMIT;
                        ");
$query -> execute(array(
            "cid" => $cid,
            "msg" => $msg,
            "datetime" => $datetime,
            "sender_id" => $getid,
            "receiver_id" => $frid,
            "cid2" => $cid
            ));

Here you can see how you can write a bullet-proof transaction.

Collectives™ on Stack Overflow

PHP/MySQL - How to use MySQL BEGIN/COMMIT with PDO

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related