How to Restore MySQL Database from SQL File using PHP

Generally, the import section of phpMyAdmin is used to import or restore the database from a SQL file. Like phpMyAdmin, there are various options are available to restore the tables of MySQL database. To import SQL file in the database, you need to login to your hosting server or phpMyAdmin. Also, you can restore the database from PHP script without login to your hosting server or phpMyAdmin.

Restore database from PHP script is very useful when you want to allow the user to restore the database from your web application. A backup of the database needs to be taken for importing tables in MySQL database. In this tutorial, we will show you how to import and restore the database from SQL file using PHP. Our simple PHP script helps to restore MySQL database from SQL file.

Restore MySQL Database using PHP

For better usability, all the PHP code is grouped together in the restoreDatabaseTables() function. Using the restoreDatabaseTables() function in PHP, you can restore tables in the database from a .sql file. The following parameters are required to restore MySQL database using PHP.

• $dbHost – Required. Specifies the host of the database.
• $dbUsername – Required. Specifies the database username.
• $dbPassword – Required. Specifies the database password.
• $dbName – Required. Specifies the database which you wants to backup.
• $filePath – Required. Specifies the path of the SQL file from where the tables will be restored.

/**
 * @function    restoreDatabaseTables
 * @author      CodexWorld
 * @link        http://www.codexworld.com
 * @usage       Restore database tables from a SQL file
 */
function restoreDatabaseTables($dbHost, $dbUsername, $dbPassword, $dbName, $filePath){
    // Connect & select the database
    $db = new mysqli($dbHost, $dbUsername, $dbPassword, $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 performing query "<b>' . $templine . '</b>": ' . $db->error . '<br /><br />';
            }
            
            // Reset temp variable to empty
            $templine = '';
        }
    }
    return !empty($error)?$error:true;
}

Usage:
Use restoreDatabaseTables() function in PHP to restore MySQL database from SQL file.

$dbHost     = 'localhost';
$dbUsername = 'root';
$dbPassword = 'root';
$dbName     = 'databaseName';
$filePath   = 'files/yourMysqlBackupFile.sql';

restoreDatabaseTables($dbHost, $dbUsername, $dbPassword, $dbName, $filePath);

Backup / Export MySQL Database using PHP