CODEWITHSUNDEEP
mysqltransactionstry-catchcommitrollback
isom
isom

Reputation: 304

Two queries in one transaction with rollback

i want to cancel the two queries if there is a failure in one of two, is my tests that execute the first query although the second aground

public function testCommit(){

        $host = "localhost";
        $db = "agm_gs";
        $user = "root";
        $password = "";
        $conn;
        $conn = new mysqli($host, $user, $password, $db);

        $sql1 = 'INSERT INTO livreur (id_livreur, code_livreur, nom_livreur, pre_livreur)  VALUES (NULL, \'L001\', \'nom1\', \'pre1\')';
        $sql2 = 'INSERT INTO otherTable XXXXXXXXXXincorrect queryXXXXXXXX';

        try
        {
            $conn->begin_transaction();
            $conn->autocommit(FALSE);

            $conn->query($sql1);
            $conn->query($sql2);

            $conn->commit();
            echo 'Sucess transaction';
        }
        catch(PDOException $e)
        {
            $conn->rollback();
            echo ' Error transaction <br />' . $e->getMessage();
        }
}

and this my result of insert

enter image description here

Upvotes: 1

Views: 563

Answers (1)

Abhishek Sharma
Abhishek Sharma

Reputation: 6661

Try this :-

$sql1 = 'INSERT INTO livreur 
id_livreur, code_livreur, nom_livreur, pre_livreur)  
VALUES (NULL, \'L001\', \'nom1\', \'pre1\'),
(NULL, \'L002\', \'nom2\', \'pre2\')';

INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas.

Upvotes: 1

Related Questions