Tuesday 6 November 2018

Mysql: Loading .sql files from within PHP

I'm creating an installation script for an application that I'm developing and need to create databases dynamically from within PHP. I've got it to create the database but now I need to load in several .sql files. I had planned to open the file and mysql_query it a line at a time - until I looked at the schema files and realised they aren't just one query per line.
So, how do I load an sql file from within PHP (as phpMyAdmin does with its import command)?

 Answers


I'm getting the feeling that everyone here who's answered this question doesn't know what it's like to be a web application developer who allows people to install the application on their own servers. Shared hosting, especially, doesn't allow you to use SQL like the "LOAD DATA" query mentioned previously. Most shared hosts also don't allow you to use shell_exec.
Now, to answer the OP, your best bet is to just build out a PHP file that contains your queries in a variable and can just run them. If you're determined to parse .sql files, you should look into phpMyAdmin and get some ideas for getting data out of .sql files that way. Look around at other web applications that have installers and you'll see that, rather than use .sql files for their queries, they just package them up in PHP files and just run each string through mysql_query or whatever it is that they need to do.


phpBB uses a few functions to parse their files. They are rather well-commented (what an exception!) so you can easily know what they do (I got this solution from http://www.frihost.com/forums/vt-8194.html). here is the solution an I've used it a lot:
<php
ini_set('memory_limit', '5120M');
set_time_limit ( 0 );
/***************************************************************************
*                             sql_parse.php
*                              -------------------
*     begin                : Thu May 31, 2001
*     copyright            : (C) 2001 The phpBB Group
*     email                : support@phpbb.com
*
*     $Id: sql_parse.php,v 1.8 2002/03/18 23:53:12 psotfx Exp $
*
****************************************************************************/

/***************************************************************************
 *
 *   This program is free software; you can redistribute it and/or modify
 *   it under the terms of the GNU General Public License as published by
 *   the Free Software Foundation; either version 2 of the License, or
 *   (at your option) any later version.
 *
 ***************************************************************************/

/***************************************************************************
*
*   These functions are mainly for use in the db_utilities under the admin
*   however in order to make these functions available elsewhere, specifically
*   in the installation phase of phpBB I have seperated out a couple of
*   functions into this file.  JLH
*
\***************************************************************************/

//
// remove_comments will strip the sql comment lines out of an uploaded sql file
// specifically for mssql and postgres type files in the install....
//
function remove_comments(&$output)
{
   $lines = explode("\n", $output);
   $output = "";

   // try to keep mem. use down
   $linecount = count($lines);

   $in_comment = false;
   for($i = 0; $i &lt; $linecount; $i++)
   {
      if( preg_match("/^\/\*/", preg_quote($lines[$i])) )
      {
         $in_comment = true;
      }

      if( !$in_comment )
      {
         $output .= $lines[$i] . "\n";
      }

      if( preg_match("/\*\/$/", preg_quote($lines[$i])) )
      {
         $in_comment = false;
      }
   }

   unset($lines);
   return $output;
}

//
// remove_remarks will strip the sql comment lines out of an uploaded sql file
//
function remove_remarks($sql)
{
   $lines = explode("\n", $sql);

   // try to keep mem. use down
   $sql = "";

   $linecount = count($lines);
   $output = "";

   for ($i = 0; $i &lt; $linecount; $i++)
   {
      if (($i != ($linecount - 1)) || (strlen($lines[$i]) > 0))
      {
         if (isset($lines[$i][0]) && $lines[$i][0] != "#")
         {
            $output .= $lines[$i] . "\n";
         }
         else
         {
            $output .= "\n";
         }
         // Trading a bit of speed for lower mem. use here.
         $lines[$i] = "";
      }
   }

   return $output;

}

//
// split_sql_file will split an uploaded sql file into single sql statements.
// Note: expects trim() to have already been run on $sql.
//
function split_sql_file($sql, $delimiter)
{
   // Split up our string into "possible" SQL statements.
   $tokens = explode($delimiter, $sql);

   // try to save mem.
   $sql = "";
   $output = array();

   // we don't actually care about the matches preg gives us.
   $matches = array();

   // this is faster than calling count($oktens) every time thru the loop.
   $token_count = count($tokens);
   for ($i = 0; $i &lt; $token_count; $i++)
   {
      // Don't wanna add an empty string as the last thing in the array.
      if (($i != ($token_count - 1)) || (strlen($tokens[$i] > 0)))
      {
         // This is the total number of single quotes in the token.
         $total_quotes = preg_match_all("/'/", $tokens[$i], $matches);
         // Counts single quotes that are preceded by an odd number of backslashes,
         // which means they're escaped quotes.
         $escaped_quotes = preg_match_all("/(?&lt;!\\\\)(\\\\\\\\)*\\\\'/", $tokens[$i], $matches);

         $unescaped_quotes = $total_quotes - $escaped_quotes;

         // If the number of unescaped quotes is even, then the delimiter did NOT occur inside a string literal.
         if (($unescaped_quotes % 2) == 0)
         {
            // It's a complete sql statement.
            $output[] = $tokens[$i];
            // save memory.
            $tokens[$i] = "";
         }
         else
         {
            // incomplete sql statement. keep adding tokens until we have a complete one.
            // $temp will hold what we have so far.
            $temp = $tokens[$i] . $delimiter;
            // save memory..
            $tokens[$i] = "";

            // Do we have a complete statement yet?
            $complete_stmt = false;

            for ($j = $i + 1; (!$complete_stmt && ($j &lt; $token_count)); $j++)
            {
               // This is the total number of single quotes in the token.
               $total_quotes = preg_match_all("/'/", $tokens[$j], $matches);
               // Counts single quotes that are preceded by an odd number of backslashes,
               // which means they're escaped quotes.
               $escaped_quotes = preg_match_all("/(?&lt;!\\\\)(\\\\\\\\)*\\\\'/", $tokens[$j], $matches);

               $unescaped_quotes = $total_quotes - $escaped_quotes;

               if (($unescaped_quotes % 2) == 1)
               {
                  // odd number of unescaped quotes. In combination with the previous incomplete
                  // statement(s), we now have a complete statement. (2 odds always make an even)
                  $output[] = $temp . $tokens[$j];

                  // save memory.
                  $tokens[$j] = "";
                  $temp = "";

                  // exit the loop.
                  $complete_stmt = true;
                  // make sure the outer loop continues at the right point.
                  $i = $j;
               }
               else
               {
                  // even number of unescaped quotes. We still don't have a complete statement.
                  // (1 odd and 1 even always make an odd)
                  $temp .= $tokens[$j] . $delimiter;
                  // save memory.
                  $tokens[$j] = "";
               }

            } // for..
         } // else
      }
   }

   return $output;
}

$dbms_schema = 'yourfile.sql';

$sql_query = @fread(@fopen($dbms_schema, 'r'), @filesize($dbms_schema)) or die('problem ');
$sql_query = remove_remarks($sql_query);
$sql_query = split_sql_file($sql_query, ';');

$host = 'localhost';
$user = 'user';
$pass = 'pass';
$db = 'database_name';

//In case mysql is deprecated use mysqli functions. 
mysql_connect($host,$user,$pass) or die('error connection');
mysql_select_db($db) or die('error database selection');

$i=1;
foreach($sql_query as $sql){
echo $i++;
echo "<br />";
mysql_query($sql) or die('error in query');
}

?>


mysqli can run multiple queries separated by a ;
you could read in the whole file and run it all at once using mysqli_multi_query()
But, I'll be the first to say that this isn't the most elegant solution.


My suggestion would be to look at the sourcecode of PHPMyBackup. It's an automated PHP SQL loader. You will find that mysql_query only loads one query at a time, and projects like PHPMyAdmin and PHPMyBackup have already done the hard work for you of parsing the SQL the correct way. Please don't re-invent that wheel :P


Since I can't comment on answer, beware to use following solution:
$db = new PDO($dsn, $user, $password);

$sql = file_get_contents('file.sql');

$qr = $db->exec($sql);
There is a bug in PHP PDO https://bugs.php.net/bug.php?id=61613
db->exec('SELECT 1; invalidstatement; SELECT 2');
won't error out or return false (tested on PHP 5.5.14).


Try This:
// SQL File
$SQLFile = 'YourSQLFile.sql';

// Server Name
$hostname = 'localhost';

// User Name
$db_user = 'root';

// User Password
$db_password = '';

// DBName
$database_name = 'YourDBName';

// Connect MySQL
$link = mysql_connect($hostname, $db_user, $db_password);

if (!$link) {
die("MySQL Connection error");
}

// Select MySQL DB
mysql_select_db($database_name, $link) or die("Wrong MySQL Database");

// Function For Run Multiple Query From .SQL File
function MultiQuery($sqlfile, $sqldelimiter = ';') {
set_time_limit(0);

if (is_file($sqlfile) === true) {
$sqlfile = fopen($sqlfile, 'r');

if (is_resource($sqlfile) === true) {
$query = array();
echo "<table cellspacing='3' cellpadding='3' border='0'>";

while (feof($sqlfile) === false) {
$query[] = fgets($sqlfile);

if (preg_match('~' . preg_quote($sqldelimiter, '~') . '\s*$~iS', end($query)) === 1) {
$query = trim(implode('', $query));

if (mysql_query($query) === false) {
echo '<tr><td>ERROR:</td><td> ' . $query . '</td></tr>';
} else {
echo '<tr><td>SUCCESS:</td><td>' . $query . '</td></tr>';
}

while (ob_get_level() &gt; 0) {
ob_end_flush();
}

flush();
}

if (is_string($query) === true) {
$query = array();
}
}
echo "</table>";

return fclose($sqlfile);
}
}

return false;
}

/* * * Use Function Like This: ** */

MultiQuery($SQLFile);



mysql_query("LOAD DATA LOCAL INFILE '/path/to/file' INTO TABLE mytable");


This The Best Code For restore sql by php can use 100% Goooood! Thank A lot
$file_content = file('myfile.sql');
$query = "";
foreach($file_content as $sql_line){
if(trim($sql_line) != "" && strpos($sql_line, "--") === false){
 $query .= $sql_line;
 if (substr(rtrim($query), -1) == ';'){
   echo $query;
   $result = mysql_query($query)or die(mysql_error());
   $query = "";
  }
 }
}


Briefly, the way I have done this is:
  1. Read the file (a db dump eg $ mysqldump db > db.sql)
    $sql = file_get_contents(db.sql);
  2. Import it using mysqli::multi_query
    if ($mysqli->multi_query($sql)) {
    $mysqli->close();
} else {
    throw new Exception ($mysqli->error);
}
Watch out mysqli_query supports async queries. More here: http://php.net/manual/en/mysqli.multi-query.php and here https://.com/a/6652908/2002493


Some PHP libraries can parse a SQL file made of multiple SQL statements, explode it properly (not using a simple ";" explode, naturally), and the execute them.
Many hosts will not allow you to create your own database through PHP, but you seem to have solved that.
Once the DB has been created, you can manipulate and populate it simply:
mysql_connect("localhost");
mysql_query("SOURCE file.sql");


This may be helpful -->
More or less what it does is to first take the string given to the function (the file_get_contents() value of your file.sql) and remove all the line breaks. Then it splits the data by the ";" character. Next it goes into a while loop, looking at each line of the array that is created. If the line contains the " ` " character, it will know it is a query and execture the myquery() function for the given line data.
Code:
function myquery($query) {

mysql_connect(dbhost, dbuser, dbpass);

mysql_select_db(dbname);

$result = mysql_query($query);

if (!mysql_errno() && @mysql_num_rows($result) > 0) {
}

else {

$result="not";
}
mysql_close();

return $result;

}



function mybatchquery ($str) {

$sql = str_replace("\n","",$str)

$sql = explode(";",$str);

$x=0;

while (isset($str[$x])) {

if (preg_match("/(\w|\W)+`(\w|\W)+) {

myquery($str[$x]);

}

$x++

}

return TRUE;

}




function myrows($result) {

$rows = @mysql_num_rows($result);

return $rows;
}




function myarray($result) {

$array = mysql_fetch_array($result);

return $array;
}




function myescape($query) {

$escape = mysql_escape_string($query);

return $escape;
}



$str = file_get_contents("foo.sql");
mybatchquery($str);


I use this all the time:
$sql = explode(";",file_get_contents('[your dump file].sql'));// 

foreach($sql as $query)
 mysql_query($query);


this actually worked for me:
/* load sql-commands from a sql file */
function loadSQLFromFile($url)
{
    // ini_set ( 'memory_limit', '512M' );
    // set_time_limit ( 0 );

    global $settings_database_name;
    global $mysqli_object; global $worked; $worked = false;

    $sql_query = "";

    // read line by line
    $lines = file($url);
    $count = count($lines);

    for($i = 0;$i<$count;$i++)
    {
        $line = $lines[$i];
        $cmd3 = substr($line, 0, 3);
        $cmd4 = substr($line, 0, 4);
        $cmd6 = substr($line, 0, 6);
        if($cmd3 == "USE")
        {
            // cut away USE ``;
            $settings_database_name = substr($line, 5, -3);
        }
        else if($cmd4 == "DROP")
        {
            $mysqli_object->query($line); // execute this line
        }
        else if(($cmd6 == "INSERT") || ($cmd6 == "CREATE"))
        {
            // sum all lines up until ; is detected
            $multiline = $line;
            while(!strstr($line, ';'))
            {
                $i++;
                $line = $lines[$i];
                $multiline .= $line;
            }
            $multiline = str_replace("\n", "", $multiline); // remove newlines/linebreaks
            $mysqli_object->query($multiline); // execute this line
        }       
    }

    return $worked;
}
?>


I noticed that the PostgreSQL PDO driver does not allow you to run scripts separated by semicolons. In order to run a .sql file on any database using PDO it is necessary to split the statements in PHP code yourself. Here is a solution that seems to work quite well:
The referenced class has done the trick for me in a database independent way, please message me if there are any issues. Here is how you could use the script after adding it to your project:
$pdo = new PDO($connectionString, $userName, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$parser = new SqlScriptParser();
$sqlStatements = $parser->parse($fileName);
foreach ($sqlStatements as $statement) {
    $distilled = $parser->removeComments($statement);
    if (!empty($distilled)) {
        $statement = $pdo->prepare($sql);
        $affectedRows = $statement->execute();
    }
}

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)