❮ PHP MySQLi Reference
Example - Object Oriented style
Perform multiple queries against the database:
<?php
$mysqli = new mysqli("localhost","my_user","my_password","my_db");
if ($mysqli -> connect_errno) {
echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
exit();
}
$sql =
"SELECT Lastname FROM Persons ORDER BY LastName;";
$sql .= "SELECT Country FROM Customers";
// Execute multi query
if ($mysqli -> multi_query($sql)) {
do {
// Store first result set
if ($result = $mysqli -> store_result()) {
while ($row = $result -> fetch_row()) {
printf("%s\n", $row[0]);
}
$result
-> free_result();
}
// if there are more result-sets, the print a divider
if ($mysqli -> more_results()) {
printf("-------------\n");
}
//Prepare next result set
} while ($mysqli -> next_result());
}
$mysqli -> close();
?>
Look at example of procedural style at the bottom.
Definition and Usage
The multi_query() / mysqli_multi_query() function performs one or more queries against the database. The queries are separated with a semicolon.
Syntax
Object oriented style:
$mysqli -> multi_query(query)
Procedural style:
mysqli_multi_query(connection, query)
Parameter Values
connection | Required. Specifies the MySQL connection to use |
query | Required. Specifies one or more queries, separated with semicolon |
Technical Details
FALSE if the first query fails |
5+ |
Example - Procedural style
Perform multiple queries against the database:
<?php
$con = mysqli_connect("localhost","my_user","my_password","my_db");
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
exit();
}
$sql = "SELECT Lastname FROM Persons ORDER BY LastName;";
$sql .= "SELECT Country FROM Customers";
// Execute multi query
if
(mysqli_multi_query($con, $sql)) {
do {
// Store first result set
if ($result = mysqli_store_result($con)) {
while ($row = mysqli_fetch_row($result)) {
printf("%s\n", $row[0]);
}
mysqli_free_result($result);
}
// if there are more result-sets,
the print a divider
if (mysqli_more_results($con)) {
printf("-------------\n");
}
//Prepare next result set
} while (mysqli_next_result($con));
}
mysqli_close($con);
?>
❮ PHP MySQLi Reference
It seems you are not executing $query1 - $query3. You have just skipped to $query4 which won't work if the others have not been executed first.
Also
$query4 = "SELECT *, @rank_change = prev_rank - current_rank as rank_change from final_output";should probably be
$query4 = "SELECT *, @rank_change := prev_rank - current_rank as rank_change from final_output";or else the value of rank_change will just be a boolean, true if @rank_change is equal to (prev_rank - current_rank), false if it is not. But do you need @rank_change at all? Will you use it in a subsequent query? Maybe you can remove it altogether.
Even better, you could just combine all the queries into one like this:
SELECT curr.player, curr.rank AS current_rank, @rank_change := prev.rank - curr.rank AS rank_change FROM main_table AS curr LEFT JOIN main_table AS prev ON curr.player = prev.player WHERE curr.date = X AND prev.date = date_sub('X', INTERVAL 1 MONTH)- Use Prepared Statements to Execute Multiple SQL Queries in PHP
- Use Multi_query to Execute Multiple SQL Queries in PHP
This tutorial will teach you to execute multiple SQL queries in PHP. We’ll discuss two methods that show how to do this.
The first method uses PHP prepared statements, while the second method will use PHP multi_query function.
Use Prepared Statements to Execute Multiple SQL Queries in PHP
To demonstrate how to execute multiple SQL queries with prepared statements, we’ll need three things which are: an HTML form, a MySQL database, and the PHP code to execute the queries. Let’s begin with the database.
Use MySQL to Create a Database and Database Table
To create the database, we’ll use a local MySQL database with XAMPP. Download and install XAMPP from their official website.
Once the download completes, launch the XAMPP control panel. Then launch the MySQL shell with the following command.
# This login command assumes that the # password is empty, and the user is "root" mysql -u root -pCreate a database called fruit_database and use it.
CREATE database fruit_database; USE fruit_database;Output:
Query OK, 1 row affected (0.002 sec) Database changedCreate a table called fruits in the database.
CREATE TABLE fruits (id INT NOT NULL AUTO_INCREMENT, name VARCHAR(20) NOT NULL, PRIMARY KEY (id)) ENGINE = InnoDB;Output:
Query OK, 0 rows affected (0.028 sec)Create the HTML Form
We’ll use two form inputs and a submit button in the HTML form. The form inputs will take two strings that we’ll insert into the database.
Here is the HTML for the form.
<main> <form action="multi-sql.php" method="post"> <div class="form-row"> <label for="first-color"> First Color </label> <input type="text" placeholder="Enter the first color" name="first-color" id="first-color" required > </div> <div class="form-row"> <label for="second-color"> Second Color </label> <input type="text" placeholder="Enter the second color" name="second-color" id="second-color" required > </div> <div class="form-row flex-center"> <button type="submit" name="submit" > Submit Colors </button> </div> </form> </main>Create the PHP Code
The PHP code will process the form inputs before submitting their values to the database.
We sanitize the user-submitted strings. Then we use these strings as part of the SQL queries.
Afterward, we store the queries in an array. To submit them, we use prepared statements.
Save the following PHP code as multi-sql.php.
<?php if (isset($_POST['submit'])) { // Connect to the database $connection_string = new mysqli("localhost", "root", "", "fruit_database"); // Sanitise the input strings $firstColor = mysqli_real_escape_string($connection_string, trim(htmlentities($_POST['first-color']))); $secondColor = mysqli_real_escape_string($connection_string, trim(htmlentities($_POST['second-color']))); // If there is a connection error, notify // the user, and Kill the script. if ($connection_string->connect_error) { echo "Failed to connect to Database"; exit(); } // Check string length, empty strings and // non-alphanumeric characters. if ( $firstColor === "" || !ctype_alnum($firstColor) || strlen($firstColor) <= 3 ) { echo "The first color value is invalid."; exit(); } if ( $secondColor === "" || !ctype_alnum($secondColor) || strlen($secondColor) <= 3 ) { echo "The second color value is invalid."; exit(); } $queries = [ "INSERT into fruits (name) VALUES ('$firstColor')", "INSERT into fruits (name) VALUES ('$secondColor')" ]; // Execute the multiple SQL queries foreach ($queries as $query) { $stmt = $connection_string->prepare($query); $stmt->execute(); } if ($stmt->affected_rows === 1) { echo "Data inserted successfully"; } } else { // The user accessed the script directly // Kill the script. echo "That is not allowed!"; exit(); } ?>Output (if successful):
Data inserted successfullyOutput (if there is an error in an input):
The first color value is invalid.Use Multi_query to Execute Multiple SQL Queries in PHP
You can execute multiple SQL queries with multi_query, a built-in function in PHP. The SQL queries should be in a quoted string to make multiple queries with multi_query, but each SQL should be delimited with a semicolon.
For the HTML and CSS, you can use the same HTML and CSS code from the previous section.
The next PHP code will use multi_query to insert the data into the database. Save it as multi-sql-v2.php.
Update your HTML accordingly.
<?php if (isset($_POST['submit'])) { // Connect to the database $connection_string = new mysqli("localhost", "root", "", "fruit_database"); // Sanitise the input strings $firstColor = mysqli_real_escape_string($connection_string, trim(htmlentities($_POST['first-color']))); $secondColor = mysqli_real_escape_string($connection_string, trim(htmlentities($_POST['second-color']))); // If there is a connection error, notify // the user, and Kill the script. if ($connection_string->connect_error) { echo "Failed to connect to Database"; exit(); } // Check string length, empty strings // and non-alphanumeric characters. if ($firstColor === "" || !ctype_alnum($firstColor) || strlen($firstColor) <= 3 ) { echo "The first color value is invalid."; exit(); } if ( $secondColor === "" || !ctype_alnum($secondColor) || strlen($secondColor) <= 3 ) { echo "The second color value is invalid."; exit(); } // Prepare the SQL queries for MySQL // multi queries $sql = "INSERT into fruits (name) VALUES ('$firstColor'); INSERT into fruits (name) VALUES ('$secondColor')"; // Execute the queries with multi_query if ($connection_string->multi_query($sql) === TRUE) { echo "Data inserted successfully"; } } else { // The user accessed the script directly // Kill the script. echo "That is not allowed!"; exit(); } ?>Output (if successful):
Data inserted successfullyOutput (if there is an error in an input):
The first color value is invalid.Create CSS Code to Style the HTML Form
The following CSS code will style the HTML form created in this article.
* { box-sizing: border-box; padding: 0; margin: 0; } body { display: grid; align-items: center; place-items: center; height: 100vh; } main { display: flex; justify-content: center; width: 60%; border: 5px solid #1a1a1a; padding: 1em; } form { font-size: 1.2em; width: 100%; } input, label { width: 50%; font-size: 1em; padding: 0.2em; } button { padding: 0.2em; font-size: 1em; } .form-row { display: flex; justify-content: space-between; margin-bottom: 0.5em; padding: 0.2em; } .flex-center { display: flex; justify-content: center; }