How to Import SQL into a Database using PHP

Importing SQL script via programming will be useful when we need to create database structure dynamically. For example, if we provide APP or plugin to download and install from online, the dynamic SQL import will be used to setup the application database at the time of installing the APP or plugin.

In this tutorial, we are going to import a SQL file into a database using PHP programming. In this example, we have a file containing SQL for creating contact table and inserting data. The PHP code parses the file line by line and extracts the query statements to execute using PHP MySQL function.

PHP Code to Import SQL

This simple PHP code is used to import SQL file into a database. It extracts the file contents into an array of lines by using PHP file(). The line array is iterated in a foreach loop to construct the query by omitting comment and empty lines.

The condition checks if the line contains symbols used as comment line delimiters like (–, /*, //). If so, the loop skips the current iteration and continue with the next iteration. In this way, it repeats the same until the ‘end of query’ statement. Once, the end of query character is found then the query will be executed. Then the query variable will be reset before starting next iteration.

<?php
$conn =new mysqli('localhost', 'root', '' , 'blog_samples');

$query = '';
$sqlScript = file('database-script.sql');
foreach ($sqlScript as $line)	{
	
	$startWith = substr(trim($line), 0 ,2);
	$endWith = substr(trim($line), -1 ,1);
	
	if (empty($line) || $startWith == '--' || $startWith == '/*' || $startWith == '//') {
		continue;
	}
		
	$query = $query . $line;
	if ($endWith == ';') {
		mysqli_query($conn,$query) or die('<div class="error-response sql-import-response">Problem in executing the SQL query <b>' . $query. '</b></div>');
		$query= '';		
	}
}
echo '<div class="success-response sql-import-response">SQL file imported successfully</div>';
?>

Download

This PHP code tutorial was published on June 12, 2017.

↑ Back to Top