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

by

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.

More Related Contents:

  1. Save the data into a database and return another value from database using PHP
  2. inserting information and image in php and mysql
  3. mysqli or die, does it have to die?
  4. How to check if a row exists in MySQL? (i.e. check if username or email exists in MySQL)
  5. How to display errors for my MySQLi query? [duplicate]
  6. MySQL vs MySQLi when using PHP [closed]
  7. When should I use MySQLi instead of MySQL?
  8. How to get count of rows in MySQL table using PHP?
  9. Correct way to use LIKE ‘%{$var}%’ with prepared statements?
  10. Why is mysqli giving a “Commands out of sync” error?
  11. How can I enable the MySQLi extension in PHP 7?
  12. mysqli_stmt::bind_param(): Number of elements in type definition string doesn’t match number of bind variables
  13. How can I use mysqli_fetch_array() twice?
  14. Advantages Of MySQLi over MySQL [closed]
  15. check if the query results empty row mysqli [duplicate]
  16. PHP PDO and MySQLi [duplicate]
  17. using nulls in a mysqli prepared statement
  18. Is mysql_real_escape_string() necessary when using prepared statements?
  19. Updating from MYSQL to MYSQLI [duplicate]
  20. PHP + MySql + Stored Procedures, how do I get access an “out” value?
  21. How to prepare statement for update query? [duplicate]
  22. php password_verify() hash and pass won’t match
  23. mysqli – fetch_Array error call to a member function fetch_array() on a non-object mysqli [duplicate]
  24. PHP to MySQL SSL Connections
  25. Strange problem with the length of the field gotten from database [duplicate]
  26. PHP: mysql v mysqli v pdo [closed]
  27. How do I ensure I caught all errors from MySQLi::multi_query?
  28. Cannot figure out how to run a mysqli_multi_query and use the results from the last query
  29. How to get a list of databases?
  30. Why cannot the PHP hide the error message thrown by the MySQL connection?

Leave a Comment