views:

7549

answers:

11

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

A: 

I guess the easiest way would be to execute mysqlimport command line utility from the script instead of building ad-hoc parsers.

Vinko Vrsalovic
A: 

Why not take the code from phpMyAdmin and use that? It's Open Source after all...

Mez
Look at it and take ideas, okay. If you don't want to make your application open source, better not take the code, as it's GPL
Vinko Vrsalovic
..because it's an extremely complicated tool, and it's a fairly simple problem..
dbr
It's complicated. The GPL only matters if you plan on distributing your own app as closed source (not possible). Aside from that no reason to spread the opensource fear.
Till
+3  A: 

mysql_query("LOAD DATA LOCAL INFILE '/path/to/file' INTO TABLE mytable");

olle
doesnt work if your server is remote.
Mez
if you upload your file to the server first it does...
olle
+2  A: 

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.

cam8001
That won't always work.. What if you have a query like..SELECT example FROM blah WHERE something = "something;"
dbr
if (substr(rtrim($query, -1) == ';') { is incorrect. It should be: if (substr(rtrim($query), -1) == ';') {
Josh Smeaton
file() reads the file split into lines just fine, and the code is a lot cleaner.
Nouveau
A: 

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

dbr
Unfortunately, mysql_query will only execute one query at a time ;)
SchizoDuckie
`$query="SELECT * FROM posts LIMIT 1; SELECT * FROM posts LIMIT 1"; mysql_query($query);` seems to run fine..? I guess you can't grab the results of each query, but if you're just loading the .sql file, surely all you need to check for is the query erroring?
dbr
I voted +1, but next time, improve on the pseudo code. ;)
Till
+2  A: 

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

SchizoDuckie
FWIW, both phpMyBackup and phpMyAdmin are licensed under GPL. If you 'borrow' any of their code, you are obliged to make your own project GPL as well.
Bill Karwin
+5  A: 

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
//
Bill Karwin
+1 :) It's just as others have mentioned, sometimes you don't have access to what's under the hood.
Till
Why did this get voted down? The point is valid: parsing SQL ain't easy.
Yar
I dunno. Maybe it just wasn't what they wanted to hear?
Bill Karwin
Wow, I just got another downvote (with no comment). People really hate this idea.
Bill Karwin
+2  A: 

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.

arin sarkissian
+5  A: 

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.

Jeremy Privett
Good point that hosted environments are more restrictive. The OP's question does not mention that the application needs to deploy in hosted environments. Hmm. The question of running SQL scripts in PHP comes up often enough that it'd be a great little project.
Bill Karwin
Yeah, I was gonna say that - you can't expect people to assume that you are in the most restrictive environment ever. Especially with virtual machines being offered all over everyone can have their own server at relatively little cost.
Till
I still don't see why he can't just read the .sql file into a string and execute it with PDO or mysqli. This is how I do it. PDO and mysqli support multiple queries. Admittedly, I haven't run any HUGE .sql files yet, but couldn't you just increase or remove PHP's max script exec time?
Lotus Notes
A: 

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.

K B
I just happened to read the code I was referring to in this question the other day so it's fresh. We ended up just reading until we encountered an ; and executing that statement, noting in the comments that it should be improved. The project didn't go anywhere so we didn't come up with a better solution than that.
Josh Smeaton
A: 

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

Francois Zaninotto