using a sql file

cold

what if i have a sql file with the tables to be created and some data to be populated as well.
i want to use php to apply this sql file to a specific database.
do i have to read it into a string and use that string as an sql statement ?
i mean..what if the file is large,is there a better way?

regards

etully

Get to the command line using ssh. Then type:

mysql -u username -p database-name < sql-filename

It will prompt you for a password.

big_nerd

etully - this is assuming the server is MySQL, and cold has SSH access.

cold,

is the server running MySQL? If so, Does your server have phpMyAdmin, this would be the easiest way, its pretty much built right in, it makes basic SQL admin fairly easy.

note:
If you do have shell (SSH / Telnet / Local) access, doing etully's method is the best way.

Weedpacket

And if you're not using MySQL then any other DBMS will offer a similar mechanism.

cold

this is not about having a shell access,i know that doin this from ssh is better.
what i'm talking about is that this is a web installer for a web application that needs to be done from the browser.

if ou recommend phpmyadmin,then there must be a way to do this effectivly from php(since phpmyadmin is already made using php)

Weedpacket

If you don't have shell you could use one of PHP's shell command execution functions. Note that the command will be run under the web server's account, so you may have to supply a more appropriate username/password in the command line.

Kudose

Here is what I use for my installer ... I hacked it up from SMF boards. It takes a SQL file dumped from phpMyAdmin and runs it query by query ... even multi-line queries.

# posted data
                  $loc = $_POST['dbLoc'];
                  $user = $_POST['dbUsr'];
                  $pwd = $_POST['dbPwd'];
                  $name = $_POST['dbNme'];
                  if(@mysql_connect($loc, $user, $pwd)){
                    if(@mysql_select_db($name)){
                      # write to config file
                      $data = file_get_contents('config.php');

                  $towrite = "\n".'$DBLOC = "'.$loc.'";'."\n";
                  $towrite .= '$DBUSER = "'.$user.'";'."\n";
                  $towrite .= '$DBPASS = "'.$pwd.'";'."\n";
                  $towrite .= '$DBNAME = "'.$name.'";'."\n";
                  $towrite .= '$VERSION = "'.$VERSION.'";'."\n";

                  $regex = '^(.*)/\*<-config->\*/(.*)/\*<-config-end->\*/(.*)$';
                  $out = eregi_replace($regex, '\\1/*<-config->*/'.$towrite.'/*<-config-end->*/\\3', $data);

                  $handle = fopen('config.php', 'w');
                  if(@fwrite($handle, $out)){
                    # Config file written
                    # prevent skipping steps
                    $_SESSION['lastStep'] = 2;
                    echo '<span class="validHeader">'.$YES.'Configuration file written to correctly.  Standby for initial database setup.</span><br />';

                    /** run the dbinstall.sql file **/
                  	$sql_lines = file('dbinstall.sql');

                  	// Execute the SQL
                  	$current_statement = '';
                  	$mysqlErrors = array();
                  	$exists = array();
                  	$count = 0;
                  	foreach ($sql_lines as $count => $line)
                  	{
                  		// No comments allowed!
                  		if (substr(trim($line), 0, 1) != '-')
                  			$current_statement .= "\n" . rtrim($line);

                  		// Is this the end of the query string?
                  		if (empty($current_statement) || (preg_match('~;[\s]*$~s', $line) == 0 && $count != count($sql_lines)))
                  			continue;

                  		// Does this table already exist?  If so, don't insert more data into it!
                  		if (preg_match('~^\s*INSERT INTO ([^\s\n\r]+?)~', $current_statement, $match) != 0 && in_array($match[1], $exists))
                  		{
                  			$current_statement = '';
                  			continue;
                  		}

                  		if (mysql_query($current_statement) === false)
                  		{
                  			// Error 1050: Table already exists!
                  			if (mysql_errno() === 1050 && preg_match('~^\s*CREATE TABLE ([^\s\n\r]+?)~', $current_statement, $match) == 1)
                  				$exists[] = $match[1];
                  			else
                  				$mysqlErrors[] = mysql_error();
                  		}

                  		$current_statement = '';
                  		echo '<span class="validHeader">'.$YES.'</span>';
                  		$count++;
                  	}

                    if(count($mysqlErrors) == 0){
                      echo '<br /><span class="validHeader">Initial database setup complete.  Continue to step 3.</span><br /><br />';
                      echo '<input type="button" class="submit" value="  Continue  " name="continue" onclick="window.location.href=\'install.php?step=3\'" />';
                    } else {
                      # error running config SQL
                      echo '<span class="errorHeader">Could not execute queries from dbinstall.sql.</span><br /><br />';
                      echo '<span class="errorHeader">MySQL reports the following errors:<br />';
                        foreach($mysqlErrors as $error)
                          echo $NO.$error.'<br />';
                      echo '</span>';
                    echo '<input type="button" class="submit" value="  Try Again  " name="continue" onclick="window.location.href=\'install.php?step=2\'" />';
                    }
                  } else {
                    # Error writing to the config file
                    echo '<span class="errorHeader">'.$NO.'Could not write to the configuration file.  Please verify it is writable.</span><br /><br />';
                    echo '<input type="button" class="submit" value="  Try Again  " name="continue" onclick="window.location.href=\'install.php?step=2\'" />';
                  }
                } else {
                  # could not select database
                  echo '<span class="errorHeader">'.$NO.'Could not select the database to work with.  Please verify the database exists and you have permission to access it.</span><br /><br />';
                  echo '<input type="button" class="submit" value="  Try Again  " name="continue" onclick="window.location.href=\'install.php?step=2\'" />';
                }
              } else {
                # could not connect to DB
                echo '<span class="errorHeader">'.$NO.'Could not connect to the database.  Please verify credentials.</span><br /><br />';
                echo '<input type="button" class="submit" value="  Try Again  " name="continue" onclick="window.location.href=\'install.php?step=2\'" />';
              }
            }

Hope that helps.

edit
Added "up" ... I didn't hack SMF ... it is OpenSource.
/edit

big_nerd

cold,

On the note of my recommendation of using phpMyAdmin,

you are right there is a way as Kudose posted, however if someone didn't already have something like that seperated, its quite a lot to program, debug, test, especially not knowing what versions of what databases on what server software you are running.

For future, be a bit more specific in saying its for a web installer application, etc to avoid recommendations that won't suit your needs.