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
- I don't have console access
- 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](https://stackoverflow.com/questions/814586/calling-mysql-exe-
using-php-exec-doesnt-work/814722#814722) on [another
thread](https://stackoverflow.com/questions/814586/calling-mysql-exe-using-
php-exec-doesnt-work) 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
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](http://dev.mysql.com/doc/refman/5.1/en/mysql-
commands.html) that are not recognized by the MySQL Server, e.g. CONNECT
,
TEE
, STATUS
, and DELIMITER
.
So I give +1 to @Ignacio Vazquez-Abrams's
[answer](https://stackoverflow.com/questions/4027769/running-mysql-sql-files-
in-php/4027786#4027786). You should run your .sql script in PHP by invoking
the mysql
tool, for instance with
shell_exec()
.
I got this test working:
$command = "mysql --user={$vals['db_user']} --password='{$vals['db_pass']}' "
. "-h {$vals['db_host']} -D {$vals['db_name']} < {$script_path}";
$output = shell_exec($command . '/shellexec.sql');
See also my answers to these related questions: