Execute Sql Script From a File Using PHP

Execute Sql Script From a File Using PHP

June 4, 2008

The code below allows to retrieve and execute all SQL statements defined in a SQL script file removing all comments.

<?php
$sql_file = 'test.sql';

$contents = file_get_contents($sql_file);

// Remove C style and inline comments
$comment_patterns = array('/\/\*.*(\n)*.*(\*\/)?/', //C comments
                          '/\s*--.*\n/', //inline comments start with --
                          '/\s*#.*\n/', //inline comments start with #
                          );
$contents = preg_replace($comment_patterns, "\n", $contents);

//Retrieve sql statements
$statements = explode(";\n", $contents);
$statements = preg_replace("/\s/", ' ', $statements);

require_once 'MDB2.php';

$mdb2 =& MDB2::connect('mysql://usr:pw@localhost/dbnam');

foreach ($statements as $query) {
    if (trim($query) != '') {
        echo 'Executing query: ' . $query . "\n";
        $res = $mdb2->exec($query);

        if (PEAR::isError($res)) {
            die($res->getMessage());
        }
    }
}
?>

I have used Pear::MDB2 abstraction layer to interact with the database but the code above should work with any other db abstraction layer or PHP built-in functions.