CODEWITHSUNDEEP
phpmysqlimysqli-multi-query
Anuraj TS
Anuraj TS

Reputation: 19

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

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';

Upvotes: 0

Views: 135

Answers (2)

axiac
axiac

Reputation: 72186

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);

Upvotes: 0

Sverri M. Olsen
Sverri M. Olsen

Reputation: 13263

Use transactions.

MySQLi::begin_transaction();

// Make your SQL queries here...

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

Upvotes: 2

Related Questions