DEV Community

Cover image for How To Import SQL File With PHP
Erhan Kılıç
Erhan Kılıç

Posted on

How To Import SQL File With PHP

This post was first published on How To Import SQL File With PHP

Sometimes there are times when you can not use PhpMyAdmin or any database program, and you have to use other options to import the Sql file into your database. Here, I’ll tell you how you can import with both the old mysql_query and the new mysqli_query in Php. You can also access the codes from the my Github project.

You need to do followings on both methods; upload Sql file and php file at the same place and run the php file through the site address. You can also run console commands through the server.

Update $filename with the name of sql. Update the $mysql_host variable with the database server. If the database server is not different, you can leave it as it is. $mysql_username and $mysql_password are the username and password for your database. $mysql_database is the database name, update it with your database name.

With mysql_query Function

<?php

// Name of the file
$filename = 'sql.sql';
// MySQL host
$mysql_host = 'localhost';
// MySQL username
$mysql_username = 'username';
// MySQL password
$mysql_password = 'password';
// Database name
$mysql_database = 'database';

// Connect to MySQL server
mysql_connect($mysql_host, $mysql_username, $mysql_password) or die('Error connecting to MySQL server: ' . mysql_error());
// Select database
mysql_select_db($mysql_database) or die('Error selecting MySQL database: ' . mysql_error());

// Temporary variable, used to store current query
$templine = '';
// Read in entire file
$lines = file($filename);
// 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
        mysql_query($templine) or print('Error performing query \'<strong>' . $templine . '\': ' . mysql_error() . '<br /><br />');
        // Reset temp variable to empty
        $templine = '';
    }
}
echo "Tables imported successfully";
Enter fullscreen mode Exit fullscreen mode

With mysqli_query class

<?php

// Name of the file
$filename = 'sql.sql';
// MySQL host
$mysql_host = 'localhost';
// MySQL username
$mysql_username = 'username';
// MySQL password
$mysql_password = 'password';
// Database name
$mysql_database = 'database';

// Connect to MySQL server
$con = @new mysqli($mysql_host,$mysql_username,$mysql_password,$mysql_database);

// Check connection
if ($con->connect_errno) {
    echo "Failed to connect to MySQL: " . $con->connect_errno;
    echo "<br/>Error: " . $con->connect_error;
}

// Temporary variable, used to store current query
$templine = '';
// Read in entire file
$lines = file($filename);
// 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
        $con->query($templine) or print('Error performing query \'<strong>' . $templine . '\': ' . $con->error() . '<br /><br />');
        // Reset temp variable to empty
        $templine = '';
    }
}
echo "Tables imported successfully";
$con->close($con);
Enter fullscreen mode Exit fullscreen mode

Top comments (8)

Collapse
 
defman profile image
Sergey Kislyakov

mysql_ functions are deprecated and they have been removed at some point iirc. Use PDO and if your hosting does not support that, you should change the hosting.

Collapse
 
erhankilic profile image
Erhan Kılıç

That's right! Thanks for reminding.

Collapse
 
jakegore profile image
Jake Gore

Could you just use the mysql command like this?

mysql -u username -p database_name < file.sql
Collapse
 
erhankilic profile image
Erhan Kılıç

Yes, you can do that way and it's better and simpler but I wrote this article for who uses shared hosting or free hosting.
You even can't know when you need this. You may need this to help a friend or to do a client's job at a time you've never thought of.

Collapse
 
jakegore profile image
Jake Gore

Ah yeah, I hadn't thought of that. Nice article anyway!

Thread Thread
 
erhankilic profile image
Erhan Kılıç

Thanks! :)

Collapse
 
prezine profile image
Precious Tom

Or you can simply $conn->multi_query(file_get_contents('db.sql'))

Collapse
 
joelde123 profile image
helpyuhboy

worked flawless with PDO 👍