How to execute multiple queries in the backend Mysql database through PHP computer programming language

Communication with a backend database management system gets easier due to the specific support for the task by different computer programming languages. One of the many languages which supports connection with a database and interaction with it is PHP, a computer programming language which is used to write applications for the web.

Having experimented lately with the PHP computer programming language, I decided to share my experience with it so far. Through this blog post you will learn how to execute multiple sql queries in a Mysql database by making use of a custom PHP script.

What are multiple sql queries?

As you may already know, for one to talk with a database management system such as Mysql, it is required knowledge on the structured query language which is widely known on the internet as sql.

What's the one thing every developer wants? More screens! Enhance your coding experience with an external monitor to increase screen real estate.

When one writes and executes a sql query in the backend database management system, if everything goes fine, a result is being returned.

For example, the following sql query, asks for a specific movie based on title in one of the tables of my database.

select * from movies where title='iron man';

Once the above gets executed in the console of my database management system in the backend, I get a specific result. A result which is specific to Mysql.

A multi query in sql, is a statement which contains many single sql queries that are separated by semi colons.

The following is an example of a multi query in sql.

$multi_query = "create table test (id int); drop table test if exists; select * from movies where title='test'"; 

As you can see from the above example, the PHP variable $multi_query, contains three sql queries in one single statement string.

What does it mean to execute multiple sql queries at once

Based on my experience with the Mysql database management system, for each query it executes, a result is being returned. To execute multiple queries at once means to save client-server round trips as all the sql queries are being sent through one request of the client.

Write the script in PHP

Let’s define some bullet points which are going to guide us during our process of writing the PHP script for executing multiple queries in the backend database management system.

  1. Creation the PHP script.
  2. Definition of database connection settings.
  3. Connection setup with the database management system.
  4. Error checking during the connection with the database.
  5. Definition of the sql statement with multiple queries.
  6. Execution of the sql statement with multiple queries in the backend database management system.
  7. Reading of the results.

The above points define a clear guidance for writing the PHP script needed to execute multiple queries in the backend database management system over a single connection.

Creation of the PHP script

For the purpose of this blog post, I am going to name my PHP script like shown below. I highly suggest you do the same for your own custom PHP script.

multiple_queries.php

The initial PHP script should look like the one shown below.

<?php
// script to execute multiple sql queries over the same connection

?>

Let’s continue with the second point.

Define the database connection settings

To execute the script which we are going to write in this tutorial, you need a backend database management system like Mysql. Make sure to respectively insert your credentials in the variables defined below.

<?php

// connection settings
$hostname = "";
$username = "";
$password = "";
$database = "";
?>

Setup a new connection with the database

Once we have managed to define the database connection settings within our PHP script, it’s time to start a new connection with the database in the backend database management system. Fortunately, PHP comes with all the necessary tools to interact with the database management system.

<?php

// connection settings
$hostname = "";
$username = "";
$password = "";
$database = "";

// create a new connection
$mysqli = new mysqli($hostname, $username, $password, $database);

?>

It is always a good idea to check for errors with the help of an if conditional while connecting with the database in the backend.

<?php

// connection settings
$hostname = "";
$username = "";
$password = "";
$database = "";

// create a new connection
$mysqli = new mysqli($hostname, $username, $password, $database);
if ($mysqli->connect_errno) {
  echo("Failed to connect with the database");
}

?>

Define the sql queries for execution in the backend dbms

<?php

// connection settings
$hostname = "";
$username = "";
$password = "";
$database = "";

// create a new connection
$mysqli = new mysqli($hostname, $username, $password, $database);
if ($mysqli->connect_errno) {
  echo("Failed to connect with the database");
}

// define the sql queries
$sql = "SELECT * FROM students;";
$sql .= "SELECT * FROM subjects;";

?>

Execute the multiple queries

Now that we have defined the sql queries for execution in the backend database management system, it is time to execute them with the help of a specific PHP function for the job.

<?php

// connection settings
$hostname = "";
$username = "";
$password = "";
$database = "";

// create a new connection
$mysqli = new mysqli($hostname, $username, $password, $database);
if ($mysqli->connect_errno) {
  echo("Failed to connect with the database");
}

// define the sql queries
$sql = "SELECT * FROM students;";
$sql .= "SELECT * FROM subjects;";

if (!$mysqli->multi_query($sql)) {
  echo("Failed to execute the queries.");
}

?>

We can also display the specific errors that come from the backend database management system.

<?php

// connection settings
$hostname = "";
$username = "";
$password = "";
$database = "";

// create a new connection
$mysqli = new mysqli($hostname, $username, $password, $database);
if ($mysqli->connect_errno) {
  echo("Failed to connect with the database");
}

// define the sql queries
$sql = "SELECT * FROM students;";
$sql .= "SELECT * FROM subjects;";

if (!$mysqli->multi_query($sql)) {
  echo("Failed to execute the queries." . $mysqli->error);
}

?>

Read the results from the execution of the queries

If the execution of the query goes fine, we can read the results coming from the execution of each query.

<?php

// connection settings
$hostname = "";
$username = "";
$password = "";
$database = "";

// create a new connection
$mysqli = new mysqli($hostname, $username, $password, $database);
if ($mysqli->connect_errno) {
  echo("Failed to connect with the database");
}

// define the sql queries
$sql = "SELECT * FROM students;";
$sql .= "SELECT * FROM subjects;";

if (!$mysqli->multi_query($sql)) {
  echo("Failed to execute the queries." . $mysqli->error);
}

// read the results from the execution of the queries


do {
     if ($result_set=$mysqli->store_result()){
        while ($row=$result_set->fetch_row()){
           echo($row[0]);
           echo(":");
           echo($row[1]);
           echo(":");
           echo($row[2]);
           echo("<br>");
  
         }
      $result_set->free();
      }
  }
  
  while ($mysqli->next_result());

?>

Final thoughts

PHP offers all the necessary tools to connect and interact with a database management system in the backend. Through this blog post you learned how to write a basic PHP script which connects with a Mysql database, executes two sql queries over the same connection and in the end reads and returns the results.

Note: For the script of this tutorial to properly execute, you need to create two tables in a database in your Mysql server and populate them with at least two fields.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Author: Rishabh

Rishabh is a full stack web and mobile developer from India. Follow me on Twitter.

Leave a Reply

Your email address will not be published. Required fields are marked *