Can some SQL queries be executed while the others fail?

Question

I have read from some posts that it is a better practice to execute queries one by one instead of doing the multi-query function in PHP files. I wonder say I have two mysqli_query functions, is it possible that the first query succeeds and MySQL Database has been changed accordingly when the next query fails?

If this is the case, I need to execute the second query only if the first succeeded, for example ( create table, then insert data to that table). Is putting an if statement for the condition that the first query is successful the best way of ensuring this?

There are many ways to achieve this. I would probably use a transaction for that particular example. — Qirel
– Qirel, Commented Oct 2, 2020 at 21:35

Dharman · Accepted Answer · 2020-10-02 21:45:30Z

2

Yes, this is possible. If your database engine supports transactions (e.g. InnoDB) then you can use transactions. They enable you to stash changes until you call commit manually. This has nothing to do with multi-query.

You can use transactions in PDO as well as in mysqli. Although as always you should choose PDO whenever possible.

Here is an example of a simple transaction using mysqli. The whole thing is wrapped in a try-catch so that if one of these queries fails then the other ones are rolled back.

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli('localhost', 'user', 'pass', 'test');
$mysqli->set_charset('utf8mb4'); // always set the charset

// start transaction
$mysqli->begin_transaction();
try {
    $stmt = $mysqli->prepare('INSERT INTO user(name) VALUE(?)');
    $stmt->bind_param('s', $username);
    $stmt->execute();
    $id = $mysqli->insert_id;

    $stmt = $mysqli->prepare('INSERT INTO hobby(uid, name) VALUE(?, ?)');
    $stmt->bind_param('ss', $id, $hobby);
    $stmt->execute();

    // save changes
    $mysqli->commit();
} catch (Throwable $e) {
    $mysqli->rollback();
    throw $e;
}

In the example above, either both queries are successful or neither of them.

Create table cannot be used with transactions, however, because it is a DDL statement and they always cause an implicit commit when they are executed.

edited Oct 2, 2020 at 21:45

answered Oct 2, 2020 at 21:22

Dharman♦

33.9k27 gold badges105 silver badges157 bronze badges

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

4 Comments

gview Over a year ago

It is important to note that transactions don't work with the myisam engine -- only engines with transaction support like innodb will work with code like this. So all the tables involved in a transaction must be InnoDB engine tables.

Andy Lin Over a year ago

hi Dharman, does the sql query finished execution right after the query function is executed? for example, in your code does the query finish right after this line of code? $stmt->execute();

Dharman Over a year ago

@AndyLin The query is executed on the MySQL server when you call execute() but if you use transactions then changed are not persisted until you call commit()

Andy Lin Over a year ago

@Dharman yes, without the transaction is what I meant, so on the MySQL, after the execute() function is called, queries will be applied. Thank you.

Collectives™ on Stack Overflow

Can some SQL queries be executed while the others fail?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related