Running MySQL *.sql files in PHP

Question

I have two *.sql files that I use when creating a new web site database. The first file creates all the tables. The second file populates some default records. I would like to execute these files from PHP. I also use the Zend_Framework, if that will help accomplish this.

Additional Info

1. I don't have console access
2. I'm trying to automate site generation from within our application.

SOLUTION

Using shell_exec()...

$command = 'mysql'
        . ' --host=' . $vals['db_host']
        . ' --user=' . $vals['db_user']
        . ' --password=' . $vals['db_pass']
        . ' --database=' . $vals['db_name']
        . ' --execute="SOURCE ' . $script_path
;
$output1 = shell_exec($command . '/site_db.sql"');
$output2 = shell_exec($command . '/site_structure.sql"');

...I never did get useful output, but followed some suggestions on another thread and finally got it all working. I switch to the --option=value format for the commands and used --execute="SOURCE ..." instead of < to execute the file.

Also, I never got a good explanation of the difference between shell_exec() and exec().

Answer 1

You'll need to create a full SQL parser for this. I recommend you use the mysql command line tool for this instead, invoking it externally from PHP.

Answer 2

One suggestion:

// connect to db.
if (mysql_query("SOURCE myfile.sql")) {

  echo "Hello Sonny";

} 

Answer 3

Don't forget about phpMyAdmin. Pretty solid interface for interacting with MySQL.

I don't know if it solves your problem, since I don't know if you can interact with it directly from code, but just wanted to throw it out there.

Answer 4

This question comes up from time to time. There's no good solution for running a .sql script directly from PHP. There are edge cases where statements common in a .sql script can't be executed as SQL statements. For example, the mysql tool has builtin commands that are not recognized by the MySQL Server, e.g. CONNECT, USE, and DELIMITER.

So I give +1 to @Ignacio Vazquez-Abrams's answer. You should run your .sql script in PHP by invoking the mysql tool, for instance with shell_exec().

See also my answers to these related questions:

• Loading .sql files from within PHP
• is it possible to call a sql script from a stored procedure in another sql script?
• PHP: multiple SQL queries in one mysql_query statement

---

I got this test working:

$command = "mysql -u{$vals['db_user']} -p{$vals['db_pass']} "
 . "-h {$vals['db_host']} -D {$vals['db_name']} < {$script_path}";

$output = shell_exec($command . '/shellexec.sql');

The crucial part is that MySQL's -p option must not be followed by a space.

I also wrote it with variable interpolation syntax instead of so much string concatenation.