How to execute SQL file in PHP web applications

How to execute SQL file in PHP web applications

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";
     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.

Related Posts