Creating a simple SQL script executor

In this tutorial I will show you how you can create a simple PHP script to execute MySQL batch files.

Sometimes if you want to install a new PHP CMS, blog, wiki, ... you first need to create the necessary database. The installation packages contains the sql file you need to execute, however if you don't have any web based management tool then it can take a bit more time. In this article we will create a simple PHP solution which can execute any sql script on your MySQL database.

Step 1.

The front end of our script is a simple HTML form where you can define the general database connection information like host-name, user-name and password. It is really simple as you can see here:

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post" >
  <table width="100%">
   <tr>
    <td>Hostname:</td>
    <td><input name="hostname" type="text" /></td>
   </tr>
   <tr>
    <td>Username:</td>
    <td><input name="username" type="text" /></td>
   </tr>
   <tr>
    <td>Password:</td>
    <td><input name="password" type="password" /></td>
   </tr>
   <tr>
    <td align="center" colspan="2">
     <input type="submit" name="submitBtn" value="Install" />
    </td>
   </tr>
  </table>
</form>

Step 2.

I suppose that you have uploaded the sql file to execute to your webserver. To make it more easy now we just hard code the file name in the code but of course you can extend the HTML form with an input field to get this information.

If the form was submitted then we try to connect to the database. If it was success then we load the sql batch file content into a variable let's call it to $sqlFile. As next step we will explode this string into an array. We know that sql statements must be ended with a semicolon so we use it as parameter of the explode method.

The result is an array where each array element is a standalone sql statement.

The actual code look like this:

<?php
   $con = mysql_connect($host,$user,$pass);
   if ($con !== false){
     // Load and explode the sql file
     $f = fopen($sqlFileToExecute,"r+");
     $sqlFile = fread($f,filesize($sqlFileToExecute));
     $sqlArray = explode(';',$sqlFile);
?>

Step 3.

To execute the statement we just need to create a foreach loop and execute the statements step by step. In case of an error we save the error code and the error text as well and finish the execution. The code for this process is the following:

<?php
     //Process the sql file by statements
     foreach ($sqlArray as $stmt) {
       if (strlen($stmt)>3){
            $result = mysql_query($stmt);
              if (!$result){
                 $sqlErrorCode = mysql_errno();
                 $sqlErrorText = mysql_error();
                 $sqlStmt      = $stmt;
                 break;
              }
           }
      }
?>

Step 4.

As last step we need to inform the user if the installation was success or not. We can do this by checking the error variables. If it is not set then everything was fine else we print out the error as follows:

<?php
   if ($sqlErrorCode == 0){
      echo "<tr><td>Installation was finished succesfully!</td></tr>";
   } else {
      echo "<tr><td>An error occured during installation!</td></tr>";
      echo "<tr><td>Error code: $sqlErrorCode</td></tr>";
      echo "<tr><td>Error text: $sqlErrorText</td></tr>";
      echo "<tr><td>Statement:<br/> $sqlStmt</td></tr>";
   }
?>

Download:

You can find a full SQL Executor script on this site.

On the next page you will find the complete code of this tutorial.

[newpage=Complete code]

<?php
   $sqlErrorText = '';
   $sqlErrorCode = 0;
   $sqlStmt      = '';
   $sqlFileToExecute = 'test.sql';
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
                      "DTD/xhtml1-transitional.dtd">
<html>
<head>
   <title>MySQL Executor</title>
</head>
<body>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post" >
  <table width="100%">
   <tr>
    <td>Hostname:</td>
    <td><input name="hostname" type="text" /></td>
   </tr>
   <tr>
    <td>Username:</td>
    <td><input name="username" type="text" /></td>
   </tr>
   <tr>
    <td>Password:</td>
    <td><input name="password" type="password" /></td>
   </tr>
   <tr>
    <td align="center" colspan="2">
     <input type="submit" name="submitBtn" value="Install" />
    </td>
   </tr>
  </table>
</form>
<?php
if (isset($_POST['submitBtn'])){
   $host = isset($_POST['hostname']) ? $_POST['hostname'] : '';
   $user = isset($_POST['username']) ? $_POST['username'] : '';
   $pass = isset($_POST['password']) ? $_POST['password'] : '';

   $con = mysql_connect($host,$user,$pass);
   if ($con !== false){
     // Load and explode the sql file
     $f = fopen($sqlFileToExecute,"r+");
     $sqlFile = fread($f,filesize($sqlFileToExecute));
     $sqlArray = explode(';',$sqlFile);

     //Process the sql file by statements
     foreach ($sqlArray as $stmt) {
       if (strlen($stmt)>3){
            $result = mysql_query($stmt);
              if (!$result){
                 $sqlErrorCode = mysql_errno();
                 $sqlErrorText = mysql_error();
                 $sqlStmt      = $stmt;
                 break;
              }
           }
      }
   }

   echo '<table width="100%">';
   if ($sqlErrorCode == 0){
      echo "<tr><td>Installation was finished succesfully!</td></tr>";
   } else {
      echo "<tr><td>An error occured during installation!</td></tr>";
      echo "<tr><td>Error code: $sqlErrorCode</td></tr>";
      echo "<tr><td>Error text: $sqlErrorText</td></tr>";
      echo "<tr><td>Statement:<br/> $sqlStmt</td></tr>";
   }
   echo '</table>';
}
?>
</body>

Creating a simple SQL script executor

MoocPlayer