How to insert two or more rows into table using mysqli_multi_query() with out insertion errors?

Question

I need to insert rows to more than one table and the number of rows that I need to insert is not fixed . I am using mysqli_multi_query() to do all my insert queries in a single step.

I need to execute all my queries successfully or else if one of the queries failed revert to original table .

How can I do this ?

My query is

insert into `_survey_user_map` (`user`,`survey`) values('anu','1') 

on duplicate key update survey='1'; delete from _survey_db where user='anu';

Answer 1

Use transactions.

MySQLi::begin_transaction();

// Make your SQL queries here...

if (NO ERROR OCCURRED) {
    MySQLi::commit();
} else {
    MySQLi::rollback();
}

Answer 2

Instead of running multiple queries that INSERTs one row each, compose a single INSERT query that inserts all the rows. It will fail and doesn't insert anything if at least one of the rows cannot be inserted.

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);