How to execute multiple MySql query at same time?
The best way is use sql triggers or stored procedure:
https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html
https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-stored-procedures.html
In other way you can run it in code:
<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
if (!$mysqli->query("DROP TABLE IF EXISTS test") || !$mysqli->query("CREATE TABLE test(id INT)")) {
echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
$sql = "SELECT COUNT(*) AS _num FROM test; ";
$sql.= "INSERT INTO test(id) VALUES (1); ";
$sql.= "SELECT COUNT(*) AS _num FROM test; ";
if (!$mysqli->multi_query($sql)) {
echo "Multi query failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
do {
if ($res = $mysqli->store_result()) {
var_dump($res->fetch_all(MYSQLI_ASSOC));
$res->free();
}
} while ($mysqli->more_results() && $mysqli->next_result());
?>
php/mysql with multiple queries
Thanks to php.net I've come up with a solution : you have to use (mysqli_multi_query($link, $query))
to run multiple concatenated queries.
/* create sql connection*/
$link = mysqli_connect("server", "user", "password", "database");
$query = "SQL STATEMENTS;"; /* first query : Notice the 2 semicolons at the end ! */
$query .= "SQL STATEMENTS;"; /* Notice the dot before = and the 2 semicolons at the end ! */
$query .= "SQL STATEMENTS;"; /* Notice the dot before = and the 2 semicolons at the end ! */
$query .= "SQL STATEMENTS"; /* last query : Notice the dot before = at the end ! */
/* Execute queries */
if (mysqli_multi_query($link, $query)) {
do {
/* store first result set */
if ($result = mysqli_store_result($link)) {
while ($row = mysqli_fetch_array($result))
/* print your results */
{
echo $row['column1'];
echo $row['column2'];
}
mysqli_free_result($result);
}
} while (mysqli_next_result($link));
}
EDIT - The solution above works if you really want to do one big query but it's also possible to execute as many queries as you wish and execute them separately.
$query1 = "Create temporary table A select c1 from t1";
$result1 = mysqli_query($link, $query1) or die(mysqli_error());
$query2 = "select c1 from A";
$result2 = mysqli_query($link, $query2) or die(mysqli_error());
while($row = mysqli_fetch_array($result2)) {
echo $row['c1'];
}
Execute multiple queries at once in Mysql using PHP
What you are looking for is TRANSACTIONS
assuming you are not using MyISAM
since it does not supports Transactions
.
The concept of transactions
is that either all the queries will execute or no query would execute at all.
In simple words all-or-nothing
is what Transactions
do
This is a basic example using mysqli
mysqli_query("START TRANSACTION");
$query1 = mysqli_query("INSERT INTO TABLE1(id) VALUES(2)");
$query2 = mysqli_query("INSERT INTO TABLE2(id) VALUES(3)");
if ($query1 and $query2) {
mysqli_query("COMMIT"); //Commits the current transaction
} else {
mysqli_query("ROLLBACK");//Even if any one of the query fails, the changes will be undone
}
NOTE: This was just a simple example.It would better if you implement using try
and catch
blocks handling then exceptions properly.
Take a look at PHP DOCS
PHP code to execute multiple queries with a single mysql_query() call
From the manual:
mysql_query()
sends a unique query (multiple queries are not
supported) to the currently active database on the server that's
associated with the specified link_identifier.
Move to mysqli
, which has support for multiple statements.
Execute multiple queries in PHP
This might be non obvious, but if you keep your script connected, you can access you temporary tables in separate queries. What you should do is to slice your query into three different operations, especially the "select" being a separate one. Only having single select statement in a query, php will be able to properly fetch resultset.
How to Select multiple queries in PHP to MySQL
You can resolve the problem with Mysql UNION statement.
$query = "(SELECT * FROM `table` WHERE `ip` LIKE '1.1.1.1') UNION (SELECT * FROM `table` WHERE `ip` LIKE '2.2.2.2')";
Related Topics
How to Pass Get Parameters to Laravel from with Get Method
How to Get Time Zone Through Ip Address in PHP
When *Not* to Use Prepared Statements
What Security Issues Should I Look Out for in PHP
Storing Objects in PHP Session
How to Display Unicode Data with PHP
Regex to Detect Invalid Utf-8 String
How to Convert Ipv6 from Binary for Storage in MySQL
PHP - How to Build Tree Structure List
How to Hide the Actual Download Folder Location
How to Exclude Certains Columns While Using Eloquent
.Htaccess 301 Redirect of Single Page
What Is the Format for E-Mail Headers That Display a Name Rather Than the E-Mail