I'm creating an installation script for an application that I'm developing and need to create databases dynamically from within PHP. I've got it to create the database but now I need to load in several .sql files. I had planned to open the file and mysql_query it a line at a time - until I looked at the schema files and realised they aren't just one query per line.
So, please.. how do I load an sql file from within PHP? (as phpMyAdmin does with it's import command).
I guess the easiest way would be to execute mysqlimport command line utility from the script instead of building ad-hoc parsers.
Why not take the code from phpMyAdmin and use that? It's Open Source after all...
mysql_query("LOAD DATA LOCAL INFILE '/path/to/file' INTO TABLE mytable");
Are you sure that its not one query per line? Your text editor may be wrapping lines, but in reality each query may be on a single line.
At any rate, olle's method seems best. If you have reasons to run queries one at time, you should be able to read in your file line by line, then use the semicolon at the end of each query to delimit. You're much better off reading in a file line by line than trying to split an enormous string, as it will be much kinder to your server's memory. Example:
$handle = @fopen("/sqlfile.sql", "r");
if ($handle) {
while (!feof($handle)) {
$query.= fgets($handle, 4096);
if (substr(rtrim($query, -1) == ';') {
// ...run your query, then unset the string
$query = '';
}
}
fclose($handle);
}
Obviously, you'll need to consider transactions and the rest if you're running a whole lot of queries in a batch, but it's probably not a big deal for a new-install script.
Unless you plan to import huge .sql files, just read the entire file into memory, and run it as a query.
It's been a while since I've used PHP, so, pseudo code:
all_query = read_file("/my/file.sql")
con = mysql_connect("localhost")
con.mysql_select_db("mydb")
con.mysql_query(all_query)
con.close()
Unless the files are huge (say, over several megabytes), there's no reason to execute it line-at-a-time, or try and split it into multiple queries (by splitting using ;, which as I commented on cam8001's answer, will break if the query has semi-colons within strings)..
My suggestion would be to look at the sourcecode of PHPMyBackup. It's an automated PHP SQL loader. You will find that mysql_query only loads one query at a time, and projects like PHPMyAdmin and PHPMyBackup have already done the hard work for you of parsing the SQL the correct way. Please don't re-invent that wheel :P
The simplest solution is to use shell_exec() to run the mysql client with the SQL script as input. This might run a little slower because it has to fork, but you can write the code in a couple of minutes and then get back to working on something useful. Writing a PHP script to run any SQL script could take you weeks.
Supporting SQL scripts is more complex than what people are describing here, unless you're certain that your script contains only a subset of the functionality of scripts. Below are some examples of things that may appear in an ordinary SQL script that make it complex to code a script to interpret it line by line.
-- Comment lines cannot be prepared as statements
-- Here's a MySQL client tool builtin command cannot be prepared or executed by server:
USE testdb;
-- Here's multi-line statement:
CREATE TABLE foo (
string VARCHAR(100)
);
-- Here's a statement that is not supported as a prepared statement:
LOAD DATA INFILE 'datafile.txt' INTO TABLE foo;
-- Here's a statement that is not terminated with a semicolon:
DELIMITER //
-- Here's a multi-line statement that contains a semicolon
-- but it's not the statement terminator:
CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM foo;
END
//
mysqli can run multiple queries separated by a ;
you could read in the whole file and run it all at once using mysqli_multi_query()
But, I'll be the first to say that this isn't the most elegant solution.
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.
None of the solutions I have seen here deal with needing to change the delimiter while creating a stored procedure on a server where I can't count on having access to LOAD DATA INFILE. I was hoping to find that someone had already solved this without having to scour the phpMyAdmin code to figure it out. Like others, I too was in the process of looking for someone else's GPL'ed way of doing it since I am writing GPL code myself.
Some PHP libraries can parse a SQL file made of multiple SQL statements, explode it properly (not using a simple ";" explode, naturally), and the execute them.
For instance, check Phing's PDOSQLExecTask