Recommended sites
Web Hosting Reviews

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>   

Sponsored links