Loading .sql files from within PHP
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:
<phpini_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 < $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 (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 < $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;}$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. mysqli_connect($host,$user,$pass) or die('error connection');mysqli_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');}?>
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.