3

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.

Improve this question
8
  • 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, ...),... Commented Jul 11, 2018 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. Commented Jul 11, 2018 at 12:12
  • @VikashMishra sorry should better read.. well use transaction then mysqli_begin_transaction followed by mysqli_commit when done or look here stackoverflow.com/questions/5178697/… Commented Jul 11, 2018 at 12:15
  • Possible duplicate of MySQL Insert into multiple tables? (Database normalization?) Commented Jul 11, 2018 at 12:18
  • 2
    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. Commented Jul 11, 2018 at 12:18

2 Answers 2

Reset to default
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

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

@user3783243 where have I mentioned mysqli?
@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.
@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.
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.
I will enjoy never speaking to you sometimes. Also, your analogy is wrong, entirely.
|
2

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.