Running .sql Files in PHP

[xenlab]

I created a demo server for a web application I wrote. The demo allows the user to intereact with almost every feature of the system. Since the people being sent to the demo are executives at large fortune 500 companies, the data in the demo server was planned in advance, to give them a good idea of how the system works. Once the demo system was set up, along with the sample data, i created a SQL dump of the file. Using Cron, I have it set up to remove the data from the database, and recreate the tables with fresh sample data. This happens everynight at midnight and assures us that users will always be presented with a fresh install. This was easy to do in the command prompt, by connecting to the mysql server and importing the sql file using the command:

mysql -uUSERNAME -pPASSWORD DBNAME</path/to/file.sql

Now I want to be able to trigger this same command from the click of a button in the administrator panel of the application. Just in case someone adds alot of garbage to our application, before midnight comes.

Problem is that the sql file is pretty large, in that it drops and recreates about 12 tables, and insterts a modest about rows into each of these tables... sometimes up to 50 at a time.

Recreating each individual SQL statement in a PHP file will be timeconsuming. I would rather import into PHP and run the sql file agains the mysql server all in one go (just as using the command prompt does).

Is this possible? How would I go about coding this in the fewest lines possible? 😕

Thanks!

[LordShryku]

I'd just leave the sql file where it is on the server, and use a [man]system[/man] call to perform the command line operation.

[onion2k]

$databaseLink = mysql_connect("blah","blah","blah");
mysql_select_db("blah",$connection);

$sql = file("test.sql");

foreach($sql as $line => $query) {
mysql_query($query,$databaseLink);
}

So long as your SQL file has one valid query per line I reckon that should do the trick.

[xenlab]

Originally posted by LordShryku
I'd just leave the sql file where it is on the server, and use a [man]system[/man] call to perform the command line operation.

i will try that, as that seems to be the best way to go. i'll let you know how it goes. thanks again. :p

[LordShryku]

I have a small backup script I use that could lead you in the right direction.

$host      = "localhost";
$user      = "user";
$pass      = "pass";
$backup    = "/var/backups/db_backup_".date("Y-m-d").".sql";
system(sprintf("/usr/local/mysql/bin/mysqldump --all-databases -h %s -u %s -p%s > %s", $host, $user, $pass, $backup));