running mysql sql files in php

running mysql sql files in php  using -'php,mysql,zend-framework,scripting'

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.


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().

asked Sep 29, 2015 by sandeep bhadauria
0 votes

6 Answers

0 votes

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().

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.

See also my answers to these related questions:

answered Sep 29, 2015 by nikhilapatil
0 votes

Here is what I use:

function run_sql_file($location){
    //load file
    $commands = file_get_contents($location);

    //delete comments
    $lines = explode("\n",$commands);
    $commands = '';
    foreach($lines as $line){
        $line = trim($line);
        if( $line && !startsWith($line,'--') ){
            $commands .= $line . "\n";

    //convert to array
    $commands = explode(";", $commands);

    //run commands
    $total = $success = 0;
    foreach($commands as $command){
            $success += (@mysql_query($command)==false ? 0 : 1);
            $total += 1;

    //return number of successful queries and total number of queries found
    return array(
        "success" => $success,
        "total" => $total

// Here's a startsWith function
function startsWith($haystack, $needle){
    $length = strlen($needle);
    return (substr($haystack, 0, $length) === $needle);
answered Sep 29, 2015 by shegokar.anjeet
0 votes

I know I'm pretty late to the party but PHP Mini Admin has been a lifesaver on a couple of occasions. It's basically a "lite" PHPMyAdmin all contained in one file so no need for complicated installs, just upload it and log in. Simples!

answered Sep 29, 2015 by virendra.bajaj
0 votes

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.

answered Sep 29, 2015 by vijaygupta1980
0 votes

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.

answered Sep 29, 2015 by param.oncemore
0 votes

I created a migration script with multi_query. It can process mysqldump output and phpmyadmin exports without mysql command line tool. I also made some logic to process multiple migration files based on timestamp stored in DB like Rails. I know it needs more error handling but currently does the work for me.

Check it out:

I think if you don't process user input with it only scripts made by developers or export tools you can use it safely.

answered Sep 29, 2015 by nikhilapatil