Run SQL file from PHP

Sometimes you will have to call a predefined .sql file in order to setup database and populate the initial database schema, instead of calling mysql_query from the PHP script. The most straight forward way to do this is to invoke the MySQL command line and pass in the .sql file.

But this is not really preferable if you have to distribute your web applications to your customers because you can't expect them to understand all the intricacies of setting up MySQL databases. The best way is to wrap all these commands in PHP code  under install.php so that the users don't have to know what's under the hood. An amazingly, calling MySQL command line from PHP proved to be a troublesome thing for me. So I am blogging here just to help those who might need some help.

I ran across here where there is a PHP script that contains the necessary code to run .sql file. This post uses exec() method to call the MySQL command line. But unfortunately it doesn't work on Windows. So I actually asked around for help, and neither StackOverflow nor ServerFault users offered a correct answer. But luckily after some trying I managed to get it to work.

Here's the code that is guaranteed to work... on my Windows machine.

<?php

  $dbhost = 'localhost';
  $dbuser = 'root';
  $dbpass = 'password';
  $db = 'job';
  $file =dirname(__FILE__).'\\'.'job_create.sql';
  $mySQLDir='"C:\\Program Files\\MySQL\\MySQL Server 5.1\\bin\\mysql.exe"';



    if ($dbpass != '') {
        $cmd = $mySQLDir.' -h '.$dbhost.' --user='.$dbuser.' --password='.$dbpass.' < "'.$file.'"';

    } else {
        $cmd = $mySQLDir.' -h '.$dbhost.' --user='.$dbuser.' < "'.$file.'"';
    }

     echo $cmd;  

   exec('"'.$cmd.'"',$out ,$retval);
   echo "\n";
    echo ($retval);

?>

Hopefully this code is useful to you as well!