Running MySQL *.sql files in PHP

user_244826 0
Viewed 107085

I have two *.sql files that I use when creating a new web site database. The first file creates all the tables. The second file populates some default records. I would like to execute these files from PHP. I also use the Zend_Framework, if that will help accomplish this.

Additional Info

  1. I don't have console access
  2. I'm trying to automate site generation from within our application.

SOLUTION

Using shell_exec()...

$command = 'mysql'
        . ' --host=' . $vals['db_host']
        . ' --user=' . $vals['db_user']
        . ' --password=' . $vals['db_pass']
        . ' --database=' . $vals['db_name']
        . ' --execute="SOURCE ' . $script_path
;
$output1 = shell_exec($command . '/site_db.sql"');
$output2 = shell_exec($command . '/site_structure.sql"');

...I never did get useful output, but followed some suggestions on another thread and finally got it all working. I switch to the --option=value format for the commands and used --execute="SOURCE ..." instead of < to execute the file.

Also, I never got a good explanation of the difference between shell_exec() and exec().

zend-framework scripting mysql php
14 Answers

Here is my solution and the below code explains what is does. The principle is to read the file line by line, build a query and execute each of them. I saw many solutions using the "file_get_contents" which is not a good solution because it could cause a buffer issue as it read the whole file contents to string variable. My solution takes also into account TRIGGERs' queries. There's no array allocation, comment and empty lines are stripped.

<?php
 /**
 * Get a connection from database
 * @param type $db_host database hostname
 * @param type $db_user database username
 * @param type $db_password database password
 * @param type $db_name database name
 * @return \PDO
 */
 function get_db_connection($db_host, $db_user, $db_password, $db_name)
{
    $dns = "mysql:host=$db_host;dbname=$db_name";
    try
    {
        return new PDO($dns, $db_user, $db_password);
    } catch (PDOException $ex)
    {
        return null;
    }
}

/**
 * Runs SQL queries from file
 */

 function exec_sql_queries_from_file($script_file, $db_host, $db_user, $db_password, $db_name)
{
    // to increase the default PHP execution time
    set_time_limit ( 60 ); // Max time = 60 seconds

    // Connect to database
    $connection = get_db_connection($db_host, $db_user, $db_password, $db_name);

    // If the connection is acquired
    if($connection != null){

        // Open sql file
        $f = fopen($script_file, 'r');

        // sql query
        $query = '';

        // Default delimiter for queries
        $delimiter = ';';

        // read line by line
        while (!feof($f))
        {           
            $line = str_replace(PHP_EOL, '', fgets($f)); // read a line and remove the end of line character

            /* if the current line contains the key word 'DELIMITER'. Ex: DELIMITER ;; or DELIMITER $$
             * mostly used for TRIGGERS' queries
             */
            if(strpos($line, 'DELIMITER') !== false)
            {
                // change the delimiter and read the next line
                $delimiter = str_replace('DELIMITER ', '', $line);
                continue;
            }   

            // Consider the line as part of a query if it's not empty and it's not a comment line
            if (!empty($line) && !starts_with($line, '/*') && !starts_with($line, '--'))
            {
                // the query hasn't reach its end: concatenate $line to $query if $line is not a delimiter
                $query .= $line !== $delimiter ? $line : '';

                // if the current line ends with $delimiter: end of current query
                if (ends_with($line, $delimiter))
                {                
                    // exec the query
                    $connection->exec($query) or die($connection->errorInfo());
                    // start new query
                    $query = '';
                }
            }                    
        }

        fclose($f);
    }
}

 /**
 * Starts with function
 */
function starts_with($haystack, $needle)
{
    return $haystack{0} === $needle{0} ? stripos($haystack, $needle) === 0 : false;
}

/**
 * Ends with function
 */
function ends_with($haystack, $needle)
{
    $pos = stripos($haystack, $needle);
    return $pos === FALSE ? FALSE : substr($haystack, $pos) === $needle;

}

user_56352370

Just wanna to add to @Bill Karwin answer given above.

You can import | reinitialize | execute custom SQL; the database using sql script file, by simply clicking on button. That button would execute the sql script file using ajax.

eg.

Front end code

  <input type="button" value="Execute SQL Script" id="btnExecuteScript" />
  <input type="button" value="reset" onclick="clearDiv('divExecuteScript')" />
  <div id="divExecuteScript" style='display: none'></div>
  <br />

Jquery code calling the ajax

  $('#btnExecuteScript').click(function (event) {
    if ($('#divExecuteScript').html() == '') {
      $('#divExecuteScript').html("<b style='font-family: sans-serif;font-size: larger'>Please Wait, It might take a few minutes</b>");
      $('#divExecuteScript').show();
      $.get("../controller/Controller.php?executeScript=TRUE", function (data) {
        // alert("$" + data + "$");
        $('body').css('cursor', 'default');
        $('#divExecuteScript').html(data);
        $('#divExecuteScript').show();
      });
    } else
      $('#divExecuteScript').toggle();
  });

connection file

class Conn {

    protected $databaseURL; // const
    protected $databaseName;
    protected $databaseUName;
    protected $databasePWord;
    public $mysqli;

        public function __construct($args = null) {
        if (stripos($_SERVER['SERVER_NAME'], "localhost") !== FALSE) {
                $this->databaseURL = "host"; 
                $this->databaseName = "database";
                $this->databaseUName = "user";
                $this->databasePWord = "password";
            } 
            $this->mysqli = new mysqli($this->databaseURL, $this->databaseUName, $this->databasePWord, $this->databaseName) or die('Could not connect to the database server' . mysqli_connect_error());

             if (empty($this->mysqli))
               die("Error while connecting to host"); 
    }

    function get_databaseURL() {
        return $this->databaseURL;
    }

    function get_databaseUName() {
        return $this->databaseUName;
    }

    function get_databasePWord() {
        return $this->databasePWord;
    }

    function get_databaseName() {
        return $this->databaseName;
    }

}

controller code executing the command

$con = new Conn();
  $mysqli = new mysqli($con->get_databaseURL(), $con->get_databaseUName(), $con->get_databasePWord(), $con->get_databaseName()) or die('Could not connect to the database server' . mysqli_connect_error());

if (isset($_GET['executeScript'])) {
  $script_path = '/path-to-script-file/filename.sql';
  $command = "mysql --user={$con->get_databaseUName()} --password='{$con->get_databasePWord()}' "
  . "-h {$con->get_databaseURL()} -D {$con->get_databaseName()} < {$script_path}";
  $output = shell_exec($command);

  if (!empty($output))
    echo "<b style='font-family: sans-serif;font-size: large'>Execute the SQL script<br />";
  else
    echo "<b style='font-family: sans-serif;font-size: large'>Unable to execute the SQL script</b><br />";

  return;
}
user_34887470

PHP Code

The code I found on this page worked for me. (Scroll down to see the commented version)

<?php
$conn = new mysqli('localhost', 'root', '' , 'sql_auto_test_table');

$query = ''; 
$sqlScript = file('sqlFileName.sql');

foreach ($sqlScript as $line)   {

    $startWith = substr(trim($line), 0 ,2);
    $endWith = substr(trim($line), -1 ,1);
    
    if (empty($line) || $startWith == '--' || $startWith == '/*' || $startWith == '//') {
        continue;
    }
    
    $query = $query . $line . "/*<br>*/"; 
    if ($endWith == ';') {
        mysqli_query($conn,$query) or die('<div>Problem in executing the SQL query <b>,<br><br>' . $query. '</b><br><br>'.$conn->error.'</div>');
        $query= '';     
    }
}
echo '<div>SQL file imported successfully</div>';
?>

Potential Fixes

I tested this file with a WordPress database exported to SQL using phpMyAdmin and it worked fine. I had to add the following lines at the top of the .sql file to avoid a few DEFAULT VALUE errors in some DATE columns. Alternatively, you can try executing the following queries before executing your SQL file if you receive a similar error.

SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';
SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';

In addition, substitute the violent die() function with a better error-handling mechanism.

Explanation

In case you want, I added a few comment lines to explain the behavior.

<?php
$conn = new mysqli('localhost', 'root', '' , 'db_name');

$query = ''; //Set an empty query variable to hold the query
$sqlScript = file('mySqlFile.sql'); //Set the sql file location

//Read each line of the file
foreach ($sqlScript as $line)   {

    //Get the starting character and the ending character of each line
    $startWith = substr(trim($line), 0 ,2);
    $endWith = substr(trim($line), -1 ,1);
    
    //Check for empty or comment lines. (If the line starts with --,/*,// or the line is empty, skip to the next line)
    if (empty($line) || $startWith == '--' || $startWith == '/*' || $startWith == '//') {
        continue;
    }
    
    //Add the line to the query. (Additional optional commented out <br> tag added to query for easy error identification)
    $query = $query . $line . "/*<br>*/"; 
    //If the line end with a ";" assume the last query has ended in this line
    if ($endWith == ';') {
        //Therefore, try to execute the query. Upon failure, display the last formed query with the SQL error message
        mysqli_query($conn,$query) or die('<div>Problem in executing the SQL query <b>,<br><br>' . $query. '</b><br><br>'.$conn->error.'</div>');
        //Reset the query variable and continue to loop the next lines
        $query= '';     
    }
}
//If nothing went wrong, display a success message after looping through all the lines in the sql file
echo '<div>SQL file imported successfully</div>';

/*
If failed with an invalid DEFAULT value for a DATE column error, try adding the following lines to the top of your SQL file. Otherwise, execute these lines before executing your .sql file.
SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';
SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';
*/
?>
user_148376780
Related