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>

16 comments:

  1. No working, need what php version?

    ReplyDelete
  2. Very Interesting information shared than other blogs
    Thanks for Sharing and Keep updating us

    ReplyDelete

  3. I have seen lot blogs and Information on othersites But in this PHP Blog Information is very useful thanks for sharing it........

    ReplyDelete
  4. i like the way you are teaching am a new programmer and i learn more staff from you thanks

    ReplyDelete
  5. Hi am a new programmer and i like your way of teaching

    ReplyDelete
  6. Not working!! showing this error message: 'There is an error in Database Import';

    ReplyDelete
  7. Its showing No Database Selected.

    ReplyDelete
  8. its possible same extension upload, like mysql to mysql

    ReplyDelete
  9. Thanks a lot for this post, This helped me a lot for database import relating task

    ReplyDelete
  10. Good Evening Sir,

    I am facing These Errors, Please solve it.


    Warning: mysqli_connect(): The server requested authentication method unknown to the client [caching_sha2_password] in C:\xamppnew\htdocs\upload\index.php on line 17

    Warning: mysqli_connect(): (HY000/2054): The server requested authentication method unknown to the client in C:\xamppnew\htdocs\upload\index.php on line 17

    Warning: mysqli_query() expects parameter 1 to be mysqli, boolean given in C:\xamppnew\htdocs\upload\index.php on line 30

    Warning: mysqli_query() expects parameter 1 to be mysqli, boolean given in C:\xamppnew\htdocs\upload\index.php on line 30

    ReplyDelete
  11. During upload large file it take time and finally show that time taking more not responding....

    ReplyDelete
  12. Hi I am really appreciated I was stuck in my project . it help me a lot
    and it working 100% after I update little things. Would please allow me to incorporate your source code to my project?

    ReplyDelete
  13. Hello Great thanks for you , Can you please allow me to incorporate the code in my project.

    ReplyDelete
  14. Nice Article!


    Thanks for sharing with us 🙂

    React Training in Hyderabad

    ReplyDelete