Import MySQL large database SQL file using PDO

Somethings you want import MySQL database SQL file from PHP code using PDO. The code below can allow you to import any MySQL SQL files from a small or large database.

/**
 * Import SQL File
 *
 * @param $pdo
 * @param $sqlFile
 * @param null $tablePrefix
 * @param null $InFilePath
 * @return bool
 */
function importSqlFile($pdo, $sqlFile, $tablePrefix = null, $InFilePath = null)
{
	try {
		
		// Enable LOAD LOCAL INFILE
		$pdo->setAttribute(\PDO::MYSQL_ATTR_LOCAL_INFILE, true);
		
		$errorDetect = false;
		
		// Temporary variable, used to store current query
		$tmpLine = '';
		
		// Read in entire file
		$lines = file($sqlFile);
		
		// Loop through each line
		foreach ($lines as $line) {
			// Skip it if it's a comment
			if (substr($line, 0, 2) == '--' || trim($line) == '') {
				continue;
			}
			
			// Read & replace prefix
			$line = str_replace(['<<prefix>>', '<<InFilePath>>'], [$tablePrefix, $InFilePath], $line);
			
			// Add this line to the current segment
			$tmpLine .= $line;
			
			// If it has a semicolon at the end, it's the end of the query
			if (substr(trim($line), -1, 1) == ';') {
				try {
					// Perform the Query
					$pdo->exec($tmpLine);
				} catch (\PDOException $e) {
					echo "<br><pre>Error performing Query: '<strong>" . $tmpLine . "</strong>': " . $e->getMessage() . "</pre>\n";
					$errorDetect = true;
				}
				
				// Reset temp variable to empty
				$tmpLine = '';
			}
		}
		
		// Check if error is detected
		if ($errorDetect) {
			return false;
		}
		
	} catch (\Exception $e) {
		echo "<br><pre>Exception => " . $e->getMessage() . "</pre>\n";
		return false;
	}
	
	return true;
}

NOTE: For large databases exportation, I recommend to select “include column names in every INSERT statement” option as syntax to use when inserting data. Example: Example: INSERT INTO tbl_name (col_A,col_B,col_C) VALUES (1,2,3)

Basic Usage

  1. Connect to your database using PDO
  2. Import SQL file using the PDO resource:
$filePath = 'database/schema.sql';

// Import the SQL file
$res = importSqlFile($pdo, $filePath);
if ($res === false) {
	die('ERROR');
}

Advanced Usage

You can use some variables for tables prefix or for file path (for the INFILE statement) inside your SQL files.

Tables prefix variable

SQL File (Example):

--
-- Table structure for table `<<prefix>>users`
--

DROP TABLE IF EXISTS `<<prefix>>users`;
CREATE TABLE `<<prefix>>users` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `about` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `phone` varchar(60) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `password` varchar(60) COLLATE utf8_unicode_ci DEFAULT NULL,
  `active` tinyint(1) UNSIGNED DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Indexes for table `<<prefix>>users`
--
ALTER TABLE `<<prefix>>users`
  ADD PRIMARY KEY (`id`),
  ADD KEY `email` (`email`),
  ADD KEY `active` (`active`);

--
-- AUTO_INCREMENT for table `<<prefix>>users`
--
ALTER TABLE `<<prefix>>users`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

Usage:

$filePath = 'database/file.sql';
$tablesPrefix = 'lp_';

// Import the SQL file
$res = importSqlFile($pdo, $filePath, $tablesPrefix);
if ($res === false) {
	die('ERROR');
}

File path variable

SQL File (Example):

-- countries
SET NAMES utf8;
SET character_set_database=utf8;
SET FOREIGN_KEY_CHECKS = 0;
LOAD DATA LOCAL INFILE '<<InFilePath>>countries.txt'
INTO TABLE <<prefix>>countries
FIELDS TERMINATED BY "\t"
LINES TERMINATED BY "\n"
IGNORE 22 LINES
(code, name, capital, population, tld, currency_code, languages, @dummy, neighbours);

Usage:

$filePath = 'database/file.sql';
$tablesPrefix = 'lp_';
$InFilePath = 'downloaded/countries/csv/';

// Import the SQL file
$res = importSqlFile($pdo, $filePath, $tablesPrefix, $InFilePath);
if ($res === false) {
	die('ERROR');
}

Done.