How to execute multiple queries in mysql with php

1. Use Prepared Statements to Execute Multiple SQL Queries in PHP
2. 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 -p

Create 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 changed

Create 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 successfully

Output (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 successfully

Output (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;
}

Related Article - PHP MySQL