CODEWITHSUNDEEP
phpmysqlsqlmysqli
Dharman
Dharman

Reputation: 33375

Multiple queries dependent on each other

I have two queries both dependent on each other, i.e. if first query is not executed the second shouldn't be executed, and the other way around if second can't be executed the first shouldn't be executed. 

INSERT INTO `table` VALUES (1,2,3)
UPDATE `otherTable` SET `val1`=1 WHERE `id`=$idOfInsert

ON DUPLICATE KEY UPDATE is not the answer.

I tried using mysqli::multi_query but as it turned out it executes the first even though the second can't be executed (it stops on error).

How can I achieve this in PHP?

Upvotes: 2

Views: 3731

Answers (4)

Dharman
Dharman

Reputation: 33375

What I was looking for are transactions. @tmuguet's answer has really helped me back then, but now looking at it from the perspective of time I would like to provide a more up to date answer.

Each query needs to be executed separately and using prepared statements to prevent SQL injection. 

try {
    // Start transaction
    $mysqli->begin_transaction();

    $mysqli->query('INSERT INTO `table` VALUES (1,2,3)');

    $stmt = $mysqli->prepare('UPDATE otherTable SET val1=1 WHERE id=?');
    $stmt->bind_param('s', $idOfInsert);
    $stmt->execute();

    // Commit changes
    $mysqli->commit();
} catch (\Throwable $e) {
    // Something went wrong. Rollback
    $mysqli->rollback();
    throw $e;
}

Of course for this to work properly you need to have mysqli error reporting enabled. Just add this line before new mysqli() in your code.

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

Upvotes: 1

tmuguet
tmuguet

Reputation: 1165

You can use transactions, if the engine you use support it (InnoDB, BDB).

See http://dev.mysql.com/doc/refman/5.0/en/commit.html for examples.

Edit: quick example using mysqli:

$connection->autocommit(FALSE); // disable auto-commit and start a new transaction
$result  = $connection->query("INSERT INTO `table` VALUES (1,2,3)");
$result &= $connection->query("UPDATE `otherTable` SET `val1`=1 WHERE `id`=$idOfInsert");
if (!$result) {
  // One of the queries has failed: cancel the transaction
  $connection->rollback();
} else {
  // Both queries worked:commit the current transaction
  $connection->commit();
}
$connection->autocommit(TRUE); // enable auto-commit

You may want to optimize the queries (i.e. not execute the second one if the first has failed, use prepared statements, ...)

Upvotes: 4

Mihai Stancu
Mihai Stancu

Reputation: 16117

Use [INSERT IGNORE] to make the SQL server ignore the errors.

INSERT IGNORE INTO `table` VALUES (1,2,3);

Then use LAST_INSERT_ID() to get the inserted ID or 0 if nothing was inserted. This will make UPDATE fail if since there is no record with an ID = 0.

UPDATE `otherTable` SET `val1`=1 WHERE `id`=LAST_INSERT_ID();

Upvotes: -1

Marc B
Marc B

Reputation: 360762

Break it into multiple query calls:

$result = mysql_query("INSERT ...");
if ($result) {
    mysql_query("UPDATE ...");
}

But the "don't do first if second can't" is impossible. PHP cannot reach back in time and warn the first query that the second one has failed.

Upvotes: -3

Related Questions