Run SQL script via PHP

Question

I've created a backup.sql script to restore a MySql database. I've checked the script with PHPMyAdmin import and everything works fine (the database has been restored successfully). Now I would like to run it via PHP. I've found this question and I have:

1) created a PHP file into htdocs folder with the following content

$site_path= realpath(dirname(__FILE__)).'/';

$command = 'mysql'
    . ' --host=' . 'localhost'
    . ' --user=' . 'myuser'
    . ' --password=' . 'mypass'
    . ' --database=' . 'dbname'
    . ' --execute="SOURCE ' . $site_path;

$output = shell_exec($command . 'backup.sql"');
echo "<pre>".$output."</pre>";

2) placed the backup.sql script into htdocs folder

But when I run the script, nothing happens on the database and nothing is displayed regarding shell_exec results. I'm running PHP and MySql under Apache on a windows machine. The command variable has the following value:

mysql --host=localhost --user=myuser --password=mypass--database=dbname --execute="SOURCE C:\Programmi\Apache Software Foundation\Apache2.2\htdocs/

What am I missing?

Yes, I've enabled errors with both `error_reporting(E_ALL);` and `ini_set('display_errors', 1);` — Giorgio, Oct 22 '14 at 11:58
i have just realized that you want to import previously made backup with PHP. why do you even want to do that with PHP script? you can do this easily by hand or even bash script. — Laurynas Mališauskas, Oct 22 '14 at 18:11
Because I would like to give the possibility to site admin to restore database via browser (i.e. simply clicking a button). Admin doesn't know anything about Mysql and PhpMyAdmin and should only browse for sql file. The rest is done via PHP script. — Giorgio, Oct 23 '14 at 06:51

Hugo Alves · Answer 1 · 2014-10-24T15:06:18.177

7

$mysql_host = "localhost";
$mysql_database = "db";
$mysql_user = "user";
$mysql_password = "password";
# MySQL with PDO_MYSQL  
$db = new PDO("mysql:host=$mysql_host;dbname=$mysql_database", $mysql_user, $mysql_password);

$query = file_get_contents("shop.sql");

$stmt = $db->prepare($query);

if ($stmt->execute())
     echo "Success";
else 
     echo "Fail";

If you have the whole code in your sql file 100% correct and nothing to change on it, then try this, use PDO for better security in your code.

edited Oct 24 '14 at 15:06

answered Oct 22 '14 at 12:01

Hugo Alves

79
7

It says "There was a warning during the export of dbname to ~/backup.sql". But, if I correctly interpret your answer, this script seems to export a database, while I want to import into Mysql a previously created backup. – Giorgio Oct 22 '14 at 12:07

score 0 · Answer 2 · answered Aug 27 '15 at 21:24

You can overcome this by using a MYSQL DBMS tool as Workbench where you can connect to the remote/local server and run the .sql file without the use of PHP.

PHP is meant to be used for applying business queries that reflect certain business functionality and not basically made for server actions or other tool jobs like DBMS tools..

If you really need to do this you can check this tutorial that reads the sql file and explode the queries into an Array using the ; character that shall delimit each query.

It then loops through each query in the array using foreach and executes the query on its own.

php, while probably mainly used for running web applications, is a general purpose scripting language perfectly suitable for server actions, automation, internal functionality, or any other use case where a scripting language is required — chiliNUT, Oct 05 '17 at 18:55

Federico Baù · Answer 3 · 2022-01-04T06:17:40.143

One thing I notice is that, the operation may take longer or if another operation is needed afterwards, especially during development to test that the file has been executed, the execute method will no to terminated.

I simple solution is to execute the query, then get the prepared query and run method rowCount,with this then we can safely test whether or not the execution has terminated.

So just use $count = $stmt->rowCount(); and not only $stmt->execute();

try {
    $db = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USER, DB_PASS);
    $query = file_get_contents(__DIR__ . "/test.sql");
    $stmt = $db->prepare($query);
    $stmt->execute();
    $stmt->closeCursor();// Safely consuming the SQL operation till end
    $count = $stmt->rowCount(); // Check if not pending transaction
    if ($count) {
        foreach($db->query('SELECT * from person') as $row) {
            print_r($row);
        }
    }

} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    exit;
}

Run SQL script via PHP

3 Answers3