How to run multiple insert query in SQL using PHP in one go?

Question

I want to run mysql queries to insert data from a custom html form. Here I have to insert multiple set of data, some data to one table and some to other. Currently I am using the simple approach to send data to php page using jquery ajax and run multiple mysqli_query() functions one after another. But I guess when there will be large number of users, there will be problems related to speed. So can anyone suggest me a better way to do the same.

There are 5 tables in the database and each table has 7 to 10 columns that need to get different set of data, every time.

I want to run each query only if the previous insert query is successfully completed. That's why I am checking for the result every time and then running the next query, which makes me feel the issue of speed on large user base.

you could pass all data you need to server and merge you data in a single query `INSERT INTO myTable(id, anything, ...) VALUES (id, any, ...), (id2, any2, ...),(id3, any3, ...),...` — Nitro.de, Jul 11 '18 at 12:10
@Nitro.de, I want to add data to multiple tables. Its not like I want to add multiple rows in same table. Please suggest me some good way to do this. — Vikash Mishra, Jul 11 '18 at 12:12
@VikashMishra sorry should better read.. well use transaction then `mysqli_begin_transaction` followed by `mysqli_commit` when done or look here https://stackoverflow.com/questions/5178697/mysql-insert-into-multiple-tables-database-normalization — Nitro.de, Jul 11 '18 at 12:15
Possible duplicate of [MySQL Insert into multiple tables? (Database normalization?)](https://stackoverflow.com/questions/5178697/mysql-insert-into-multiple-tables-database-normalization) — camelsWriteInCamelCase, Jul 11 '18 at 12:18
Where is the bottleneck currently? Is this just a hypothetical question? A transaction will help with reverting if there are errors, I don't see it helping with the speed of inserting into each table. — user3783243, Jul 11 '18 at 12:18
The problem is, I need to insert data to first table and if its successfully inserted then only run query for the second table. so every time I am checking for the result first then running other query. That is why I guess the speed will be hampered. — Vikash Mishra, Jul 11 '18 at 12:22
That is where you want a transaction. If any fail it will revert. — user3783243, Jul 11 '18 at 12:34
Please share more deails. Which kind of queries are you talking about? Only reading data? Data modification? Have you tried checking the individual queries for index issues? — Nico Haase, May 27 '20 at 10:24

score 4 · Accepted Answer · edited May 27 '20 at 11:16

4

The problem is, I need to insert data to first table and if its successfully inserted then only run query for the second table.

This means you need a transaction.

A transaction is a set of queries that either all execute ok or if one fails - they all fail. This is to ensure you don't end up with crap data in your tables.

Do not

Do not use multiquery.
Do not use mysql_* function(s).
Do not use bulk inserts.

People telling you to do that just have absolutely no clue what they're doing, ignore them.

Do

Use PDO
Use prepared statements
Prepare the statement(s) ONCE, use them MULTIPLE times

Sample code - do NOT copy paste

$dsn = 'mysql:dbname=testdb;host=127.0.0.1;charset=utf8mb4';
$user = 'dbuser';
$password = 'dbpass';

$pdo = new PDO($dsn, $user, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$insert['first'] = $pdo->prepare("INSERT INTO table SET col1 = :val, col2 = :val2");
$insert['second'] = $pdo->prepare("INSERT INTO another_table SET col1 = :val, col2 = :val2"); 

$pdo->beginTransaction();

$insert['first']->bindValue(':val', 'your value');
$insert['first']->bindValue(':val2', 'anothervalue');
$insert['first']->execute();

$insert['second']->bindValue(':val', 'your value');
$insert['second']->bindValue(':val2', 'anothervalue');
$insert['second']->execute();

$pdo->commit();

The code above will save the data in two tables ONLY if both inserts are successful.

edited May 27 '20 at 11:16

Dharman

30,962
25
85
135

answered Jul 11 '18 at 12:33

N.B.

13,688
3
45
55

@user3783243 where have I mentioned mysql**i**? – N.B. Jul 11 '18 at 12:35
@user3783243 since I haven't mentioned it, that must mean the example is applicable to mysqli, and OP, being a programmer, is capable of adjusting the example labeled as `do not copy paste`. I prefer PDO over mysqli, I used it to relay the technique. It can be swapped out for mysqli without any issues, but I'm simply much more familiar with PDO - hence PDO in the example. – N.B. Jul 11 '18 at 12:37
@user3783243 - so we can both agree everything is fine, and since we both prefer PDO - there's no harm being done. We've added in comments that the same can be done with mysqli. I love being "that guy" who finds the needle in haystack, but we'll end up wasting more words than the answer requires to describe a transaction. – N.B. Jul 11 '18 at 12:45
Think of it like this, if you ran a car shop and a user said they didn't like the increased cost for 93 octane. Would you suggest they replace the whole engine with diesel? The statement `Use PDO` is misleading in the same way, it is not nit picking. The answer is to use transactions. PDO and mysqli support it. You don't need a `mysqli` example . Final comment on the topic. – user3783243 Jul 11 '18 at 12:54
I will enjoy never speaking to you sometimes. Also, your analogy is wrong, entirely. – N.B. Jul 11 '18 at 12:57
can you give example with mysqli , i may be wrong but i think when you execute in mysqli it auto commit ,so how do i commit 2 querys at the same time in mysqli? – user1246950 Aug 09 '18 at 09:10
Do not catch exceptions when opening connection to PDO. This is completely useless and dangerous. – Dharman May 27 '20 at 11:12

score 2 · Answer 2 · edited Jun 14 '20 at 12:04

To paraphrase the accepted answer but with accent on mysqli.

The key is to configure mysqli to throw exceptions and to use a transaction.

A transaction will ensure that all operations either complete in their entirety or have no effect whatsoever. Another important advantage of using a transaction is that it makes multiple inserts faster, eliminating all possible delays that could be caused by separate query execution.

To use transactions with mysqli you need to do as follows:

First of all, make sure you have a proper mysqli connection, which, among other things, tells mysqli to throw an exception in case of error. Then just prepare your queries, start a transaction, execute the queries and commit the transaction - just like it is shown in the accepted answer, but with mysqli:

include 'mysqli.php';

$stmt1 = $mysqli->prepare("INSERT INTO table SET col1 = ?, col2 = ?");
$stmt2 = $mysqli->prepare("INSERT INTO another_table SET col1 = ?, col2 = ?"); 

$mysqli->begin_transaction();

$stmt1->bind_param("ss", $col1, $col2);
$stmt1->execute();

$stmt2->bind_param("ss", $col1, $col2);
$stmt2->execute();

$mysqli->commit();

Thanks to exceptions and transactions there is no need to verify the result of each query manually.

How to run multiple insert query in SQL using PHP in one go?

2 Answers2

Do not

Do

Sample code - do NOT copy paste

Linked

Related