How to Import SQL into a Database using PHP

by Vincy. Last modified on July 9th, 2022.

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

Vincy
Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.

Comments to “How to Import SQL into a Database using PHP”

Leave a Reply

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

↑ Back to Top

Share this page