MySQLPHP

How to execute SQL file in PHP web applications

181
7.7k
2018 / 05 / 03

In PHP development when we are dealing with dynamic applications we need to work with SQL and to execute SQL file in PHP.

Sometimes it becomes a hectic job to connect with the database server using command line (CLI) and execute the complex and sensitive commands to work with the SQL. Most of the developers has installed Xampp server on their local development workstation and it provides an easy SQL client “PHPMyAdmin” to deal with the database.

Lets suppose when you have to migrate a website and you are using servers like AWS that don’t have PHPMyAdmin or have complex ways to deal creating tables or inserting data. To make it simpler, you can simply execute an SQL file using PHP script.

Follow the following steps to cater this situation:

  1. Create an SQL file (dump of your database)
  2. Create a PHP file and add the following snippet (replace host, database name, username and password)
  3. Link your SQL file in the snippet (need to give accurate path)
$mysql_host = "localhost";
$mysql_database = "db";
$mysql_user = "user";
$mysql_password = "password";
# MySQL with PDO_MYSQL  
$db = new PDO("mysql:host=$mysql_host;dbname=$mysql_database", $mysql_user, $mysql_password);

$query = file_get_contents("shop.sql");

$stmt = $db->prepare($query);

if ($stmt->execute()){
     echo "Success";
}else{ 
     echo "Fail"
}

Now, run this PHP snippet and it will execute the SQL into your database server.

The function file_get_content() takes content from the SQL file and the PDO executes the SQL.

The script is written using PDO extension so you need to make sure the PHP PDO extension is enabled in your apache server.

Hi, I am a full-stack web developer with 5+ years of experience in working with different web technologies. Do you want to ask something? just send me a message through the contact form. Please visit my portfolio at hamzamehmood.com. Thanks