Using PHP to execute multiple MYSQL Queries

by

Don’t run a bunch of queries at once. Usually the success of one depends on all the other operations having been performed correctly, so you can’t just bulldozer along as if nothing’s gone wrong when there’s a problem.

You can do it like this:

$queries = [
  "CREATE TEMPORARY TABLE tmp SELECT * FROM event_categoriesBU WHERE id = 1",
  "UPDATE tmp SET id=100 WHERE id = 1",
  "INSERT INTO event_categoriesBU SELECT * FROM tmp WHERE id = 100"
];

foreach ($query as $query) {
  $stmt = $conn->prepare($query);
  $stmt->execute();
}

Don’t forget to enable exceptions so that any query failures will stop your process instead of the thing running out of control.

The reason you don’t use multi_query is because that function does not support placeholder values. Should you need to introduce user data of some kind in this query you need to use bind_param in order to do it safely. Without placeholder values you’re exposed to SQL injection bugs, and a single one of those is enough to make your entire application vulnerable.

It’s worth noting that PDO is a lot more flexible and adaptable than mysqli so if you’re not too heavily invested in mysqli, it’s worth considering a switch.

More Related Contents:

  1. mysqli or die, does it have to die?
  2. How to display errors for my MySQLi query? [duplicate]
  3. Fatal error: Call to a member function bind_param() on boolean [duplicate]
  4. MySQL vs MySQLi when using PHP [closed]
  5. When should I use MySQLi instead of MySQL?
  6. How to get count of rows in MySQL table using PHP?
  7. Correct way to use LIKE ‘%{$var}%’ with prepared statements?
  8. Example of how to use bind_result vs get_result
  9. mysqli::query(): Couldn’t fetch mysqli
  10. PHP MySQLI Prevent SQL Injection [duplicate]
  11. How to test if a MySQL query was successful in modifying database table data?
  12. How can I enable the MySQLi extension in PHP 7?
  13. mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in [duplicate]
  14. mysqli_stmt::bind_param(): Number of elements in type definition string doesn’t match number of bind variables
  15. How can I use mysqli_fetch_array() twice?
  16. PHP Commands Out of Sync error
  17. Advantages Of MySQLi over MySQL [closed]
  18. MYSQLi error: User already has more than ‘max_user_connections’ active connections [duplicate]
  19. using nulls in a mysqli prepared statement
  20. Is mysql_real_escape_string() necessary when using prepared statements?
  21. Updating from MYSQL to MYSQLI [duplicate]
  22. How to prepare statement for update query? [duplicate]
  23. php password_verify() hash and pass won’t match
  24. mysqli – fetch_Array error call to a member function fetch_array() on a non-object mysqli [duplicate]
  25. PHP to MySQL SSL Connections
  26. Using Prepared Statement, how I return the id of the inserted row?
  27. Strange problem with the length of the field gotten from database [duplicate]
  28. PHP prepared statements and transactions in a loop [duplicate]
  29. When should I close a database connection in PHP?
  30. Mysqli multiple row insert, simple multi insert query [duplicate]

Leave a Comment