Search This Blog


Saturday, May 2, 2009

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.

  $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!


Alex said...

In this condition I use usually next software-retriving data from mdf file,because tool has many facilities and it is free as far as I know,it repair data from corrupted databases in MS SQL Server format (*.mdf extension).

Alexis said...

Yesterday I lost all my sql files...But fortunately in net I found-how to recover sql database,and tool helped me in a minute and free of cost.Moreover tool showed repair data from corrupted databases in the MS SQL Server format (files with the *.mdf extension).

Anonymous said...

miley cyrus nude miley cyrus nude miley cyrus nude