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

Solution 1

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, TEE, STATUS, 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 --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:

• 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

Solution 2

$commands = file_get_contents($location);   
$this->_connection->multi_query($commands);

Solution 3

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.

Solution 4

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){
        if(trim($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);
}

Solution 5

I have never had to use it but the mysqli class has a multi_query method:

http://php.net/manual/en/mysqli.multi-query.php

View more solutions

Comments

[Pekka]

You can't use the console? It's that much easier....

[Sonny]

@Pekka - updated my post

[ethanpil]

worked great for me on linux. havent tried it on windows xampp, but it doubt it will work :)

[michaellindahl]

The difference between shell_exec() and exec() is that shell_exec returns all of the output stream as a string. exec returns the last line of the output. via stackoverflow.com/a/7093892/557358

[Chris Muench]

Here is a very good solution I have come up with stackoverflow.com/a/41404203/627473

[Pekka]

If this works (will depend on privileges) it is definitely the single best way to go.

[Pekka]

Good suggestion too. Parsing mySQL dumps in pure PHP sucks, phpMyAdmin takes the pain out of it (is not automatable, though).

[Bill Karwin]

No, SOURCE is a builtin of the mysql tool. You can't execute it as an SQL query.

[Sonny]

mysql_query() doesn't support multiple queries

[Sonny]

I am trying the shell_exec() route, but I am not finding examples specifying a file to execute. This is what I have so far: shell_exec('mysql' . ' -u ' . $vals['db_user'] . ' -p ' . $vals['db_pass'] . ' -D ' . $vals['db_name']);

[Bill Karwin]

You read the file to execute with shell redirection: mysql ... < mysqldump.sql

[zerodin]

There are ways around it. but, please be very mindful about the queries; if not careful, the are prone to sql injection. Have a read of: php.net/manual/en/function.mysql-query.php and dev-explorer.com/articles/multiple-mysql-queries

[Sonny]

Your solution works for mysqli. I am using PDO. Your answer prompted me to do a search, and I found this: stackoverflow.com/questions/6346674/…

[mindplay.dk]

There are edge-cases where this will fail, and not with an error-message but (potentially) unexpected behavior. For example, multi-line string literals in your SQL statements could start with the string '--', or string literals might contain ; characters. If you're going to go this route, you really should use a full SQL parser.

[Aaron Newton]

I thought I would offer some insight into anyone reading this who couldn't get it to work, One issue that can occur with shell_exec is that mysql is not on the path. This is easy to test by running the command mysql in the console of the affected machine. In this case, MySQL would need to be added to the path or an alternate approach would be required.

[Bill Karwin]

@AaronNewton, good idea. Path-related issues are pretty basic, but I forget how many people still struggle with them.

[techspider]

Please see how to answer questions on SO.

[Jimbo]

I'm using mysqli and can't get this solution to work. $commands just comes back as an empty string. For $location I have tried both a relative local path (the script file is in the same folder as the php file) and a fully qualified hyperlink. I get the same result either way.

[Xantium]

When writing an answer to your own question, could you please post an explanation as well as code. Without an explanation we still don't know why the problem was fixed without studying a lot of useless code.

[Sergiy Lavryk]

It wasn't my own question. TC asked "I would like to execute these files from PHP". I gave script how to do this. If you are unable to read such small piece of code (there is not much to study, it's too small and obvious), and if you don't need solution - why you can's just skip my answer instead of being such a rude? :)

[Xantium]

I'm not trying to be rude. See that down-vote above (someone else put it there in case you think it's me)? I'm trying to tell you why you are getting it and suggesting what you can do to get upvotes instead. I'm only trying to help. also it was edited edited 3 min ago and it now looks better. Unfortunately for newcomers users here expect a fantastic answer (and question) as well as working code. It's a knack. Once you have it you will be able to do good answers (and questions) and get upvotes.

[Xantium]

I hope I did not offend, I do not mean to. As I said above I'm trying to get you some reputation by helping you improve your answers and questions.

[Jonathan]

This seems to fail for certain types of advanced queries, such as creating a procedure and executing it.

[GNB]

What If I need to create a database as well?

[Bill Karwin]

@GhanshyamBhava You can put CREATE DATABASE <database> and USE <database> statements into the top of the SQL script file. Or alternatively, you can execute a CREATE DATABASE statement from PHP first, then run the SQL script as I showed above.

[Bill Karwin]

@LuongTranNguyen, No. I would rather use --defaults-file and put the user name and password in that file. See dev.mysql.com/doc/refman/8.0/en/option-file-options.html