Friday, 23 June 2017

How to Import SQL File in Mysql Database using PHP


Import of Sql table by using PHP script, it will be helpful when we want to make database dynamically when execution of script. For example When we have developed any App or any type of Web application plugin then at that time when We have installed App or plugin online then at that time importing of SQL table will be helpful to installed application table into database at the of set up of App or plugin.

In this post, we will going to learn how to import SQL script into a Mysql Database by using PHP script. Here we will seen on SQL table file in which we have on images table for import into Database. PHP script will read SQL file line by line and make query statement and then after it will execute query statement by using PHP Mysql query function.

Here we have make simple PHP script is used for import SQL table file into Mysql database. It will convert content of file into PHP array by using PHP built in file() function and this PHP Array data has been fetch by foreach loop and make query by removing comment and empty line from script.

In PHP script we have make condition to check if it has found any symbols that used from define comment then it will not check code and it will check only script without comment symbol. By using this PHP script you can easily import sql file into Msyql database.


Source Code


index.php



<?php 
$message = '';
if(isset($_POST["import"]))
{
 if($_FILES["database"]["name"] != '')
 {
  $array = explode(".", $_FILES["database"]["name"]);
  $extension = end($array);
  if($extension == 'sql')
  {
   $connect = mysqli_connect("localhost", "root", "", "testing1");
   $output = '';
   $count = 0;
   $file_data = file($_FILES["database"]["tmp_name"]);
   foreach($file_data as $row)
   {
    $start_character = substr(trim($row), 0, 2);
    if($start_character != '--' || $start_character != '/*' || $start_character != '//' || $row != '')
    {
     $output = $output . $row;
     $end_character = substr(trim($row), -1, 1);
     if($end_character == ';')
     {
      if(!mysqli_query($connect, $output))
      {
       $count++;
      }
      $output = '';
     }
    }
   }
   if($count > 0)
   {
    $message = '<label class="text-danger">There is an error in Database Import</label>';
   }
   else
   {
    $message = '<label class="text-success">Database Successfully Imported</label>';
   }
  }
  else
  {
   $message = '<label class="text-danger">Invalid File</label>';
  }
 }
 else
 {
  $message = '<label class="text-danger">Please Select Sql File</label>';
 }
}
?>

<!DOCTYPE html>  
<html>  
 <head>  
  <title>How to Import SQL File in Mysql Database using PHP</title>  
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>  
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />  
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>  
 </head>  
 <body>  
  <br /><br />  
  <div class="container" style="width:700px;">  
   <h3 align="center">How to Import SQL File in Mysql Database using PHP</h3>  
   <br />
   <div><?php echo $message; ?></div>
   <form method="post" enctype="multipart/form-data">
    <p><label>Select Sql File</label>
    <input type="file" name="database" /></p>
    <br />
    <input type="submit" name="import" class="btn btn-info" value="Import" />
   </form>
  </div>  
 </body>  
</html>

0 comments:

Post a Comment