[RESOLVED] Restore db from sql

Julianna

Hi.

I am trying to create a php script which will drop an existing database, re-create it, and restore the data from an sql file. So far, I have been able to create one which drops and recreates, but I can't figure out how to execute the sql file from php.

I need this script so I can regularly and automatically refresh a demo site which uses mySQL. I have access to creating a cronjob via cPanel, but cannot access the mysql executable so I'm trying to do this via php.

The code I have is:

<?php

$dbhost = 'localhost';
$dbuser = 'username';
$dbpass = 'password';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

$query = 'DROP DATABASE demosite';
$result = mysql_query($query);

$query = "CREATE DATABASE demosite";
$result = mysql_query($query);

ob_start();
include('demosite.sql');
$output = ob_get_contents();
ob_end_clean();

mysql_close($conn);

?>

I pieced this together from a php tutorial and this post. The script successfully drops and recreates the database, but does not seem to do anything with the sql file.

What am I doing wrong?

Thanks

Houdini

Just for fun do this

ob_start();
include('demosite.sql');
$output = ob_get_contents();
ob_end_clean();

mysql_close($conn);
var_dump($output);

And see what you get.

bradgrafelman

Uh... you're trying to get PHP to run a SQL file. Probably not going to happen.

If you are able to execute commands on the server through PHP, you can just use the MySQL CLI like so:

system("mysql --host=$dbhost --user=$dbuser --password=$dbpass demosite </home/blah/path/to/test.sql");

Julianna

For both suggestions, I am getting a "No input file specified." error.

I found this post over at webmasterworld and am still unable to get it to work.

Any other tips? I think I may simply have to convert the sql to php.

Thanks

bradgrafelman

Just curious, can you paste the system() attempt you did?

Julianna

<?php

$dbhost = 'localhost';
$dbuser = 'username';
$dbpass = 'password';
$dbname = 'demosite';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

$query  = 'DROP DATABASE demosite';
$result = mysql_query($query);

$query  = "CREATE DATABASE demosite";
$result = mysql_query($query);

mysql_select_db($dbname, $conn) or die ($dbname . " Database not found." . $dbuser);
echo "Database " .  $database . " is selected";

system("mysql --localhost=$dbhost --username=$dbuser --password=$dbpass demosite </path/to/restore/demosite.sql");

mysql_close($conn);

?>

Variations:
-without selecting the database
-with the other code
-with the other code and without selecting the database
-without the full path to the sql

I also tried creating a table instead of using the system() code and received the same error. That's when I put in the piece to connect to the database firstly, and still got the error.

Julianna

It turns out I was trying to do things the long way. (See this post)

If anyone is still interested in solving the problem via php, I'm happy to know how to do it that way too. Otherwise, nevermind :o

Thanks to everyone for their help!