PHP: mysqli_multi_query stops the execution of further MySQL queries.

The mysqli_query statement

Generally, we use mysqli_query to execute Mysql queries in PHP. We can use multiple mysqli_query statements to execute multiple operations like insert, update, and select. Sometimes, we store records in more than one table during save operation at the backend, but it is from a single form at UI level or sometimes we are doing inserting a record and updating other records in the same or in another table.

For example, we have a UI form where employee information is filled. There may be fields like Id, Employee code, Name, Email, Joining Date, Department, Hobbies, etc on UI form. Now, for this, we may have two tables to store these records as follows:

Employees (id, emp_code, name, email, joining_date, department);
Hobbies (id, employee_id, hobby)	// employee_id is a foreign key from id column of table employees

Column id in both tables is the AutoIncrement column. Hobbies can be multiple values for a single employee, so we can have a separate table for Hobbies. This is just an example to show a scenario.

This is the most commonly used approach to save record from a single UI form to two tables (Employees and Hobbies). We will have the following php code:

	
$link = mysqli_connect($host_name, $user_name, $password, $db_name);       // $link is the variable making connection with mysql database. 

$query1 = “DELETE FROM employees WHERE email = ‘rakesh@mail.com’”; // Suppose we want unique record of email, we can delete record.
$query2 = “INSERT INTO employees (emp_code, name, email, joining_date, department) VALUES (45, ‘Rakesh’, ‘rakesh@mail.com’, ‘2018-04-01’, ‘Web Development’)”;
mysqli_query($link, $query2);
$inserted_record_id = $link->insert_id;	  // This statement will provide id of record saved in employees table.
$query3 = “INSERT INTO hobbies (employee_id, hobby) VALUES ($inserted_record_id, ‘Music’), ($inserted_record_id, ‘Singing’)”;
mysqli_query($link, $query3);

Some users use another code line in place of

$inserted_record_id = $link->insert_id;

such as:

$inserted_record_id=mysqli_insert_id($link);

Or another MySQL query with SELECT statement using email id if the email id is unique in the table.

Sometimes, there is a situation where we cannot use multiple mysqli_query() function to do multiple operations (queries) during one task. Sometimes, it seems better to use mysqli_multi_query() function. The mysqli_multi_query() function is used when we are providing more than one query separated by a semicolon to execute Mysql queries.

Let’s see how we would use mysqli_multi_query() function.

$link = mysqli_connect('localhost', 'root', '123456', 'practiceDB');

$query = "DELETE FROM employees WHERE email IN ('rakesh@mail.com', 'sam@mail.com'); INSERT INTO employees (emp_code, name, email, joining_date, department) VALUES (45, 'Rakesh', 'rakesh@mail.com', '2018-04-01', 'Web Development'), (50, 'Sam', 'Sam@mail.com', '2018-04-01', 'Web Development'); ";

// query shows deletion of records if exist and then insertion of records.

$result = mysqli_multi_query($link, $query);

if($result)
{	
$nextQuery = "SELECT id, email FROM employees WHERE email IN ('rakesh@mail.com', 'sam@mail.com')";
	
	$nextResult = mysqli_query($link, $nextQuery);
	
	if($nextResult)
	{
		// suppose we get employee id 10 for rakesh and 11 for sam from above select query
		$finalQuery = "INSERT INTO hobbies (employee_id, hobby) VALUES (12, 'Music'), (12, 'Dance'), (13, 'Reading books and newspapers')";
		$finalResult = mysqli_query($link, $finalQuery);
		echo "pass";
	}
	else
	{
		echo "fail";
	}
}

When we write the above sample code, it looks fine to us, but actually, $nextResult does not execute and accordingly further operations are not performed after statement mysqli_multi_query. Sometimes, there may be multiple SELECT queries in one single query instead of insert-update-delete as shown in the above sample code. In that case, we need multiple result sets and the following statement is a must before we go for $nextQuery statement for getting multiple result sets.

while (mysqli_more_results()) 
{
mysqli_next_result($link);
	mysqli_use_result($link);
}

But in our case, we don’t need result set to display and need to break the multi_query, so we can use the following statement:

while (mysqli_next_result($link)) {if (!mysqli_more_results($link)) break;}

With only this statement, the issue in which multi_query stops the execution of mysqli_query gets resolved.