I get Uncaught PDO Exepction error in SQL transcation

Question

This code:

$sql = '
    START TRANSACTION;
    INSERT INTO translation (lang, author, title, text)
      VALUES(:lang, :author, :title, :text);
    INSERT INTO article (translation, author, category, views, banner, visible) 
      VALUES(LAST_INSERT_ID(), :author, :category, 0, :banner, :visible);
    COMMIT;';


    $params = array("lang" => $lang, 
                    "author" => $author, 
                    "title" => $title, 
                    "text" => $content, 
                    "category" => $category, 
                    "banner" => $banner, 
                    "visible" => $v);


    $stmt = $conn->prepare($sql);
    $stmt->execute($params);

Gives me this error:

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT INTO translation (lang, author, title, text) VALUES(?, ?, ?, ?); ' at line 2 in C:\xampp\htdocs\admin\add-article.php:57 Stack trace: #0 C:\xampp\htdocs\admin\add-article.php(57): PDO->prepare('\r\n START TRA...') #1 {main} thrown in C:\xampp\htdocs\admin\add-article.php on line 57

It is my first time trying to use PDO Transactions. I tried this sql directly in phpMyAdmin

BEGIN;
    INSERT INTO translation (lang, author, title, text)
      VALUES(1, 2, "test", "test");
    INSERT INTO article (translation, author, category, views, banner, visible) 
      VALUES(LAST_INSERT_ID(), 2, 1, 100, "", 1);
COMMIT;

And it worked, but didn't work in php script. I wrote 'START TRANSACTION' basing on this post Any thoughts?

That's not how you should do transactions in PDO. https://www.php.net/manual/en/pdo.begintransaction.php — user3783243, May 10 '20 at 16:13

score 0 · Accepted Answer · answered May 10 '20 at 16:34

You need to run each query separately.

Also, usually a transaction is wrapped in a try catch.

 try {
    $pdo->beginTransaction();
    $sql = 'INSERT INTO translation (lang, author, title, text)
        VALUES(:lang, :author, :title, :text)';
    $params = array("lang" => $lang, 
                    "author" => $author, 
                    "title" => $title, 
                    "text" => $content, 
    );
    $stmt = $conn->prepare($sql);
    $stmt->execute($params);    

    $sql = 'INSERT INTO article (translation, author, category, views, banner, visible) 
        VALUES(LAST_INSERT_ID(), :author, :category, 0, :banner, :visible)';
    $params = array(
                    "author" => $author, 
                    "category" => $category, 
                    "banner" => $banner, 
                    "visible" => $v
    );
    $stmt = $conn->prepare($sql);
    $stmt->execute($params);    

    $pdo->commit();
}catch (Exception $e){
    $pdo->rollback();
    throw $e;
}

I get Uncaught PDO Exepction error in SQL transcation

1 Answers1