Running MySQL *.SQL Files in PHP

Running MySQL *.sql files in PHP

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

Loading .sql files from within PHP

I'm getting the feeling that everyone here who's answered this question doesn't know what it's like to be a web application developer who allows people to install the application on their own servers. Shared hosting, especially, doesn't allow you to use SQL like the "LOAD DATA" query mentioned previously. Most shared hosts also don't allow you to use shell_exec.

Now, to answer the OP, your best bet is to just build out a PHP file that contains your queries in a variable and can just run them. If you're determined to parse .sql files, you should look into phpMyAdmin and get some ideas for getting data out of .sql files that way. Look around at other web applications that have installers and you'll see that, rather than use .sql files for their queries, they just package them up in PHP files and just run each string through mysql_query or whatever it is that they need to do.

Execute a *.sql file using php

You are looking for

• mysqli_multi_query - Executes one or multiple queries which are concatenated by a semicolon.

An alternative would be to use the command line client:

mysql -u USERNAME -p DATABASENAME < FILE.SQL 

You can probably use that with exec and/or system, but then you have to provide the password after -p. The above command will prompt you for it.

PDO run script from .sql file

Since this is a script you can execute SQL with the following code:

try {
     $mysql_host = "localhost";
     $mysql_database = "planets_test";
     $mysql_user = "root";
     $mysql_password = "";
     $dbh= new PDO("mysql:host=$mysql_host;dbname=$mysql_database", $mysql_user, $mysql_password);
     $dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
     $query = file_get_contents("planets_db.sql");
     $dbh->exec($query);
     print("Created $table Table.\n");

} catch(PDOException $e) {
    echo $e->getMessage();//Remove or change message in production code
}

If there is an error it should tell you what went wrong.

.sql file execution using php and pdo

After using this script my mysql.sql file imported successfully.

Thanks to Oshane Bailey

How to run a set of SQL queries from a file, in PHP?

1. Don't use file() unless you really want the data line-by-line. file_get_contents() is better.
2. $query==file($file_name); I don't think you want to do a comparison here.
3. mysql_query will only ever execute a single query at once. You'll have to come up with some way of separating your queries in the file and run them one-by-one.

---

Related Topics