FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Executing a .sql file through PHP





squeakypants
How would I go about this? I'm creating a php program for a friend of mine, and I want to go about it correctly and make an install script. All the install script would do is execute an sql file, and as simple as it seems, I still cannot figure out a way to do it through php. I'd rather stick to php and not use CGI to do it, since I know php and only know the basics of Perl. The install sql file is, simply, 'install.sql'.
kv
Assuming your file contains one statement per line, you can read the file in your php code and use mysql_query to execute the statements.
homer09001
my php knowledge is very and i mean very limited but can you not just use a simple "Include" line?
squeakypants
Nope, since you need to execute each query separately.

I took the easy way out and just wrote the sql in to the script. I'd still like to know how to do this, though.


KV: I don't understand what you mean...
webapp
Use PHP Admin to administer ur databases.......MYSQL it has all the tools u need to run exceute the query, if u need to query the db and output the result on php then u need few lines to be added...to ur existing php file

Search on google for code.... if u want i can post one..from my php file
squeakypants
Could you? It would be a big help Wink Though I'm not on that project anymore, install scripts are always good to know how to make.
friscofrankie
too busy to try this but something like:
Code:

require 'DB.php';
$conn      =       DB::connect('mysql://userid:passwd@localhost/db');
$stmt       =      "source `/path/to/yourfile`";
$res         =       $dbcon->Query($stmt)or die("Query died:\n");

if (DB::isError($res))
{
   do something about the error
}

should work.
izcool
Code:

<?php
$file = include("sqlfile.sql");
mysql_query($file) or exit(mysql_error());
?>


Not sure if that will work. Question Just a guess as I've never really done that before. Razz

- Mike.
BlackSkad
izcool wrote:
Code:

<?php
$file = include("sqlfile.sql");
mysql_query($file) or exit(mysql_error());
?>


Not sure if that will work. Question Just a guess as I've never really done that before. Razz

- Mike.


This won't work, because mysql_query can only handle 1 command at a time. 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:

Code:
<?php
/***************************************************************************
*                             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 < $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 < $linecount; $i++)
   {
      if (($i != ($linecount - 1)) || (strlen($lines[$i]) > 0))
      {
         if ($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 < $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("/(?<!\\\\)(\\\\\\\\)*\\\\'/", $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 < $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("/(?<!\\\\)(\\\\\\\\)*\\\\'/", $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;
}

?>
charliehk
Are there any examples to call the phpbb functions to achieve executin a .sql file? Question
BlackSkad
Euhm, maybe you could check the phpBB installation file?

But wathever,

load the sql-file with this (returns a string)
Code:
$sql_query = @fread(@fopen($dbms_schema, 'r'), @filesize($dbms_schema));


Now call the remove_remaks to delete all unnecessairy lines (returns a string)
Code:
$sql_query = $remove_remarks($sql_query);


Now call the split_sql_file. This will return an array with a command on each key (like $sql[1] = "CREATE..."; $sql[2] = "INSERT ...")
Code:
$sql_query = split_sql_file($sql_query, $delimiter);


Then perform a loop to execute each command in the array, using mysql_query.

Not that difficult is it?
BlackSkad
charliehk
thx a lot!

Quote:
Not that difficult is it?


It is not easy for someone not touching programming for a while!
Related topics
SLOW UPLOADING AN SQL FILE
how do u attach an application to an email message?
making sql dump wit php
[PHP] Faking Shell Access Through PHP
Importing/executing a BIG .SQL File
A very good PHP MySQL Tutorial
what is dump?
How do i execute an sql file in a php script?
How do i install a Mod in PHPBB
sending a file through local connection
upload image through PHP AND AJAX
Old post
how to connect database through php
File : emailer.php yeah... it happened again.
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.