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'.
Executing a .sql file through PHP
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.
my php knowledge is very and i mean very limited but can you not just use a simple "Include" line?
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...
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...
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
Search on google for code.... if u want i can post one..from my php file
Could you? It would be a big help 
Though I'm not on that project anymore, install scripts are always good to know how to make.
Though I'm not on that project anymore, install scripts are always good to know how to make.too busy to try this but something like:
should work.
| 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.
| Code: |
|
<?php $file = include("sqlfile.sql"); mysql_query($file) or exit(mysql_error()); ?> |
Not sure if that will work.
Just a guess as I've never really done that before. 
- Mike.
| izcool wrote: | ||
Not sure if that will work. Just a guess as I've never really done that before.
- 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; } ?> |
Are there any examples to call the phpbb functions to achieve executin a .sql file?
Euhm, maybe you could check the phpBB installation file?
But wathever,
load the sql-file with this (returns a string)
Now call the remove_remaks to delete all unnecessairy lines (returns a string)
Now call the split_sql_file. This will return an array with a command on each key (like $sql[1] = "CREATE..."; $sql[2] = "INSERT ...")
Then perform a loop to execute each command in the array, using mysql_query.
Not that difficult is it?
BlackSkad
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
thx a lot!
It is not easy for someone not touching programming for a while!
| Quote: |
| Not that difficult is it? |
It is not easy for someone not touching programming for a while!
 |