CODEWITHSUNDEEP
phpmysqlshellscripting
Giorgio
Giorgio

Reputation: 1970

Run SQL script via PHP

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?

Upvotes: 4

Views: 21194

Answers (3)

Federico Ba&#249;
Federico Ba&#249;

Reputation: 7665

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;
}

Upvotes: 0

KAD
KAD

Reputation: 11112

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.

Upvotes: 0

Hugo Alves
Hugo Alves

Reputation: 79

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

Upvotes: 7

Related Questions