How to execute SQL file in PHP

In the PHP applications, we came up with a scenario where we want to dynamically execute SQL file in PHP, without manually uploading the SQL file. This is very useful if we are creating a PHP app installer. Where we want to create and execute the database with a single click. In this article, we will see how to execute SQL file in PHP.

There are many methods to execute the SQL in PHP. We will cover only a couple of methods which can execute a small to medium sized database.

Method 1: PDO (PHP Data Object)

The first step is to create a databse connectin string. Then take the content of SQL using the file_get_contents() function and then pass the content to prepared statements to execute.

<?php
	// database credentials
	$mysql_host = "localhost";
	$mysql_database = "db";
	$mysql_user = "user";
	$mysql_password = "password";

	// database connection string 
	$db = new PDO("mysql:host=$mysql_host;dbname=$mysql_database", $mysql_user, $mysql_password);

	// get data from the SQL file
	$query = file_get_contents("shop.sql");

	// prepare the SQL statements
	$stmt = $db->prepare($query);

	// execute the SQL
	if ($stmt->execute()){
		echo "Success";
	}
	else {
		echo "Fail";
	}
?>

Method 2: MySQLi (procedural)

The steps are same as mentioned in the method#1, however, we will use the mysqli_multi_query() function to execute the SQL statements.

<?php
	// database credentials
	$mysql_host = "localhost";
	$mysql_database = "db";
	$mysql_user = "user";
	$mysql_password = "password";
	
	// database connection string  
	$connection = mysqli_connect($mysql_host, $mysql_user, $mysql_password, $mysql_database);
	mysqli_set_charset($connection,"utf8");
	
	if(!$connection) {
		die('Unable to connect to database'.mysqli_connect_error());
	}

	// get data from the SQL file
	$query = file_get_contents("shop.sql");

	// execute the SQL
	if (mysqli_multi_query($connection, $query)){
		echo "Success";
	}
	else {
		echo "Fail";
	}
?>

Note:

Please note that the above solutions are the simplest ones. They are without any valiations or checks. While writing such scripts, it is necessary to apply necessary validations for security and cleanliness of database.

These methods is useful for small to medium sized database.

Reference:

file_get_contents() used to read the entire file into a string.

mysqli_multi_query() used to execute one or more than one queries on the database.