Can some SQL queries be executed while the others fail?

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?

Answer

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.