How to Import MySQL Database from SQL File using PHP

Pradeep Maurya

6 years ago

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.

To import SQL file in the database, you have to login to your hosting server or phpMyAdmin. Also, you can import the database from PHP script without login to your hosting server or phpMyAdmin.

Import database from PHP script is extremely helpful when you need to enable the client to import the database from your web application. A backup of the database should be taken for importing tables in MySQL database.

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.

Import MySQL Database using PHP

Using the importDatabaseTables() work in PHP, you can import tables in the database from a .sql file. The following given parameters are required to import or rstore MySQL database using PHP.

$dbHost – Required. Specifies the host name of the database.
$dbUname – Required. Specifies the database username.
$dbPass – Required. Specifies the database password.
$dbName – Required. Specifies the database name in which you wants to import.
$filePath – Required. Specifies the path of the SQL file from where the tables will be imported.



<?php
/**
 * @function    importDatabaseTables
 * @author      Tutorialswebsite
 * @link        http://www.tutorialswebsite.com
 * @usage       Import database tables from a SQL file
 */

$dbHost     = 'localhost';
$dbUname = 'root';
$dbPass = '';
$dbName     = 'DB Name';
$filePath   = 'files/backupfile.sql';

if(file_exists($filePath)){
importDatabaseTables($dbHost, $dbUname, $dbPass, $dbName, $filePath);
}

function importDatabaseTables($dbHost, $dbUname, $dbPass, $dbName, $filePath){
    // Connect & select the database
    $db = new mysqli($dbHost, $dbUname, $dbPass, $dbName); 

    // Temporary variable, used to store current query
    $templine = '';
    
    // Read in entire file
    $lines = file($filePath);
    
    $error = '';
    
    // Loop through each line
    foreach ($lines as $line){
        // Skip it if it's a comment
        if(substr($line, 0, 2) == '--' || $line == ''){
            continue;
        }
        
        // Add this line to the current segment
        $templine .= $line;
        
        // If it has a semicolon at the end, it's the end of the query
        if (substr(trim($line), -1, 1) == ';'){
            // Perform the query
            if(!$db->query($templine)){
                $error .= 'Error importing query "<b>' . $templine . '</b>": ' . $db->error . '<br /><br />';
            }
            
            // Reset temp variable to empty
            $templine = '';
        }
    }
    return !empty($error)?$error:true;
}
?>

<?php

/**

* @function importDatabaseTables

* @author Tutorialswebsite

* @link http://www.tutorialswebsite.com

* @usage Import database tables from a SQL file

$dbHost = 'localhost';

$dbUname = 'root';

$dbPass = '';

$dbName = 'DB Name';

$filePath = 'files/backupfile.sql';

if(file_exists($filePath)){

importDatabaseTables($dbHost, $dbUname, $dbPass, $dbName, $filePath);

}

function importDatabaseTables($dbHost, $dbUname, $dbPass, $dbName, $filePath){

// Connect & select the database

$db = new mysqli($dbHost, $dbUname, $dbPass, $dbName);

// Temporary variable, used to store current query

$templine = '';

// Read in entire file

$lines = file($filePath);

$error = '';

// Loop through each line

foreach ($lines as $line){

// Skip it if it's a comment

if(substr($line, 0, 2) == '--' || $line == ''){

continue;

}

// Add this line to the current segment

$templine .= $line;

// If it has a semicolon at the end, it's the end of the query

if (substr(trim($line), -1, 1) == ';'){

// Perform the query

if(!$db->query($templine)){

$error .= 'Error importing query "<b>' . $templine . '</b>": ' . $db->error . '<br /><br />';

}

// Reset temp variable to empty

$templine = '';

}

return !empty($error)?$error:true;

}

Are you want to get implementation help, or modify or extend the functionality of this script? Submit paid service request

Pradeep Maurya

Pradeep Maurya is the Professional Web Developer & Designer and the Founder of “Tutorials website”. He lives in Delhi and loves to be a self-dependent person. As an owner, he is trying his best to improve this platform day by day. His passion, dedication and quick decision making ability to stand apart from others. He’s an avid blogger and writes on the publications like Dzone, e27.co