CODEWITHSUNDEEP
phpmysqlzend-frameworkscripting
Sonny
Sonny

Reputation: 8326

Running MySQL *.sql files in PHP

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().

Upvotes: 56

Views: 117738

Answers (14)

Javed Iqbal
Javed Iqbal

Reputation: 166

I found the easy solution, that's works for me

$new_conn=mysqli_connect("localhost","db_user","pass","db_name");
$quries=file_get_contents("db_backup.sql");
$res=mysqli_multi_query($new_conn,$quries);

Upvotes: 0

Adeel Raza Azeemi
Adeel Raza Azeemi

Reputation: 793

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

Upvotes: -1

Bill Karwin
Bill Karwin

Reputation: 562230

This question comes up from time to time. There's no good solution for running a .sql script directly from PHP. There are edge cases where statements common in a .sql script can't be executed as SQL statements. For example, the mysql tool has builtin commands that are not recognized by the MySQL Server, e.g. CONNECT, TEE, STATUS, and DELIMITER.

So I give +1 to @Ignacio Vazquez-Abrams's answer. You should run your .sql script in PHP by invoking the mysql tool, for instance with shell_exec().

I got this test working:

$command = "mysql --user={$vals['db_user']} --password='{$vals['db_pass']}' "
 . "-h {$vals['db_host']} -D {$vals['db_name']} < {$script_path}";

$output = shell_exec($command . '/shellexec.sql');

See also my answers to these related questions:

Upvotes: 53

N&#39;faly Kaba
N&#39;faly Kaba

Reputation: 677

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;

}

Upvotes: 1

Sergiy Lavryk
Sergiy Lavryk

Reputation: 39

You can use this script to run MySQL script files. You'll need to set $hostName, $userName, $password, $dataBaseName, $port and $fileName of course. 

<?php

function parseScript($script) {

  $result = array();
  $delimiter = ';';
  while(strlen($script) && preg_match('/((DELIMITER)[ ]+([^\n\r])|[' . $delimiter . ']|$)/is', $script, $matches, PREG_OFFSET_CAPTURE)) {
    if (count($matches) > 2) {
      $delimiter = $matches[3][0];
      $script = substr($script, $matches[3][1] + 1);
    } else {
      if (strlen($statement = trim(substr($script, 0, $matches[0][1])))) {
        $result[] = $statement;
      }
      $script = substr($script, $matches[0][1] + 1);
    }
  }

  return $result;

}

function executeScriptFile($fileName, $dbConnection) {
  $script = file_get_contents($scriptFleName);
  $statements = parseScript($script);
  foreach($statements as $statement) {
    mysqli_query($dbConnection, $statement);
  }
}

$hostName = '';
$userName = '';
$password = '';
$dataBaseName = '';
$port = '';
$fileName = '';

if ($connection = @mysqli_connect($hostName, $userName, $password, $dataBaseName, $port)) {
  executeScriptFile($fileName, $connection);
} else {
  die('Can not connect to MySQL');
}

Upvotes: 2

Kizito Ikapel
Kizito Ikapel

Reputation: 69

To execute table generation from within the application, you may want to create a php file that will do just that when you run it. 

$hostname  = "localhost";
$database  = "databasename";
$username  = "rootuser";
$UserPassword  = "password";

$myconnection = mysql_pconnect($hostname, $username , $UserPassword) or trigger_error(mysql_error(),E_USER_ERROR); 
mysql_connect($hostname , $username , $UserPassword ) or die(mysql_error());
mysql_select_db($database) or die(mysql_error());

if ( !$myconnection ){ echo "Error connecting to database.\n";}


$userstableDrop = " DROP TABLE IF EXISTS `users`";
$userstableCreate = " CREATE TABLE IF NOT EXISTS `users` (
`UserID` int(11) NOT NULL,
  `User_First_Name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=15" ;

$userstableInsert = "INSERT INTO `users` (`UserID`, `User_First_Name`) VALUES
(1, 'Mathew'),
(2, 'Joseph'),
(3, 'James'),
(4, 'Mary')";

$userstableAlter1 = "ALTER TABLE `users` ADD PRIMARY KEY (`UserID`)";
$userstableAlter2 = " ALTER TABLE `users` MODIFY `UserID` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=15";

$createDb_sql = $userstableDrop;
$insertSite = mysql_query($createDb_sql);

$createDb_sql = $userstableCreate;
$insertSite = mysql_query($createDb_sql);

$createDb_sql = $userstableInsert;
$insertSite = mysql_query($createDb_sql);

$createDb_sql = $userstableAlter1;
$insertSite = mysql_query($createDb_sql);

$createDb_sql = $userstableAlter2;
$insertSite = mysql_query($createDb_sql);

echo "Succesful!";
mysql_close($myconnection );

Upvotes: 0

Farid Movsumov
Farid Movsumov

Reputation: 12725

$commands = file_get_contents($location);   
$this->_connection->multi_query($commands);

Upvotes: 25

kepes
kepes

Reputation: 800

I created a migration script with multi_query. It can process mysqldump output and phpmyadmin exports without mysql command line tool. I also made some logic to process multiple migration files based on timestamp stored in DB like Rails. I know it needs more error handling but currently does the work for me.

Check it out: https://github.com/kepes/php-migration

I think if you don't process user input with it only scripts made by developers or export tools you can use it safely.

Upvotes: 1

Matt
Matt

Reputation: 9443

I know I'm pretty late to the party but PHP Mini Admin has been a lifesaver on a couple of occasions. It's basically a "lite" PHPMyAdmin all contained in one file so no need for complicated installs, just upload it and log in. Simples!

Upvotes: 6

Ibrahim Lawal
Ibrahim Lawal

Reputation: 1228

Here is what I use:

function run_sql_file($location){
    //load file
    $commands = file_get_contents($location);

    //delete comments
    $lines = explode("\n",$commands);
    $commands = '';
    foreach($lines as $line){
        $line = trim($line);
        if( $line && !startsWith($line,'--') ){
            $commands .= $line . "\n";
        }
    }

    //convert to array
    $commands = explode(";", $commands);

    //run commands
    $total = $success = 0;
    foreach($commands as $command){
        if(trim($command)){
            $success += (@mysql_query($command)==false ? 0 : 1);
            $total += 1;
        }
    }

    //return number of successful queries and total number of queries found
    return array(
        "success" => $success,
        "total" => $total
    );
}


// Here's a startsWith function
function startsWith($haystack, $needle){
    $length = strlen($needle);
    return (substr($haystack, 0, $length) === $needle);
}

Upvotes: 13

Dave
Dave

Reputation: 89

I have never had to use it but the mysqli class has a multi_query method:

http://php.net/manual/en/mysqli.multi-query.php

Upvotes: 8

jocull
jocull

Reputation: 21085

Don't forget about phpMyAdmin. Pretty solid interface for interacting with MySQL.

I don't know if it solves your problem, since I don't know if you can interact with it directly from code, but just wanted to throw it out there.

Upvotes: 2

zerodin
zerodin

Reputation: 877

One suggestion:

// connect to db.
if (mysql_query("SOURCE myfile.sql")) {

  echo "Hello Sonny";

}

Upvotes: -2

Ignacio Vazquez-Abrams
Ignacio Vazquez-Abrams

Reputation: 798456

You'll need to create a full SQL parser for this. I recommend you use the mysql command line tool for this instead, invoking it externally from PHP.

Upvotes: 15

Related Questions