In PHPMyAdmin you can import data in the database table in various formats e.g. SQL, CSV XML, etc. formats.
While import CSV file in PHPMyAdmin need to make sure that it should be the valid format like – the number of columns, data.

Similar functionality can be implemented with PHP where read the uploaded file and insert the record in the MySQL database table.

It is possible to check for duplicate entry with PHP while import but it is not possible in PHPMyAdmin.

1. Table structure

I am using user table in the tutorial demonstration.

CREATE TABLE `user` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `username` varchar(80) NOT NULL,
  `fname` varchar(50) NOT NULL,
  `lname` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2. Configuration

Create a config.php for database connection.

<?php

$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "tutorial"; /* Database name */

$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
  die("Connection failed: " . mysqli_connect_error());
}

3. HTML and PHP

Create a uploads directory for storing the CSV file.
In HTML create a <form > with file element and a submit button. On submit, button click uploads the selected file in the uploads directory.

Read the uploaded file and initialize the $importData_arr array with the data. Loop on $importData_arr and skip the header row.

Check for duplicate entry before insert record in MySQL database table.
Remove the CSV file after successfully importing and display the last 10 records to check data imported or not.

Completed Code

<?php
include "config.php";

if(isset($_POST['but_import'])){
   $target_dir = "uploads/";
   $target_file = $target_dir . basename($_FILES["importfile"]["name"]);

   $imageFileType = pathinfo($target_file,PATHINFO_EXTENSION);

   $uploadOk = 1;
   if($imageFileType != "csv" ) {
     $uploadOk = 0;
   }

   if ($uploadOk != 0) {
      if (move_uploaded_file($_FILES["importfile"]["tmp_name"], $target_dir.'importfile.csv')) {

        // Checking file exists or not
        $target_file = $target_dir . 'importfile.csv';
        $fileexists = 0;
        if (file_exists($target_file)) {
           $fileexists = 1;
        }
        if ($fileexists == 1 ) {

           // Reading file
           $file = fopen($target_file,"r");
           $i = 0;

           $importData_arr = array();
                       
           while (($data = fgetcsv($file, 1000, ",")) !== FALSE) {
             $num = count($data);

             for ($c=0; $c < $num; $c++) {
                $importData_arr[$i][] = $data[$c];
             }
             $i++;
           }
           fclose($file);

           $skip = 0;
           // insert import data
           foreach($importData_arr as $data){
              if($skip != 0){
                 $username = $data[0];
                 $fname = $data[1];
                 $lname = $data[2];
                 $email = $data[3];

                 // Checking duplicate entry
                 $sql = "select count(*) as allcount from user where username='" . $username . "' and fname='" . $fname . "' and  lname='" . $lname . "' and email='" . $email . "' ";

                 $retrieve_data = mysqli_query($con,$sql);
                 $row = mysqli_fetch_array($retrieve_data);
                 $count = $row['allcount'];

                 if($count == 0){
                    // Insert record
                    $insert_query = "insert into user(username,fname,lname,email) values('".$username."','".$fname."','".$lname."','".$email."')";
                    mysqli_query($con,$insert_query);
                 }
              }
              $skip ++;
           }
           $newtargetfile = $target_file;
           if (file_exists($newtargetfile)) {
              unlink($newtargetfile);
           }
         }

      }
   }
}
?>

<!-- Import form (start) -->
<div class="popup_import">
 <form method="post" action="" enctype="multipart/form-data" id="import_form">
  <table width="100%">

   <tr>
    <td colspan="2">
     <input type='file' name="importfile" id="importfile">
    </td>
   </tr>
   <tr>
    <td colspan="2" ><input type="submit" id="but_import" name="but_import" value="Import"></td>
   </tr>
   <tr>
    <td colspan="2" align="center"><span class="format">Username, First name, Last name,Email</span> </td>
   </tr>
   <tr>
    <td colspan="2" align="center"><a href="import_example.csv" target="_blank">Download Sample</a></td>
   </tr>

   <tr>
    <td colspan="2"><b>Instruction : </b><br/>
     <ul>
      <li>Enclose text field in quotes (' , " ) if text contains comma (,) is used.</li>
      <li>Enclose text field in single quotes (') if text contains double quotes (")</li>
      <li>Enclose text field in double quotes (") if text contains single quotes (')</li>
     </ul>
    </td>
   </tr>
  </table>
 </form>
</div>
<!-- Import form (end) -->

<!-- Displaying imported users -->
<table border="1" id="userTable">
  <tr>
   <td>S.no</td>
   <td>Username</td>
   <td>First name</td>
   <td>Last name</td>
   <td>Email</td>
  </tr>
  <?php
    $sql = "select * from user order by id desc limit 10";
    $sno = 1;
    $retrieve_data = mysqli_query($con,$sql);
    while($row = mysqli_fetch_array($retrieve_data)){
        $id = $row['id'];
        $username = $row['username'];
        $fname = $row['fname'];
        $lname = $row['lname'];
        $email = $row['email'];

        echo "<tr>
            <td>".$sno."</td>
            <td>".$username."</td>
            <td>".$fname."</td>
            <td>".$lname."</td>
            <td>".$email."</td>

        </tr>";
        $sno++;
    }
   ?>
</table> 

4. CSS

 

.popup_import{
  border: 1px solid black;
  width: 550px;
  height: auto;
  background: white;
  border-radius: 3px;
  margin: 0 auto;
  padding: 5px;
}

.format{
  color: red;
}

#userTable{
  border-collapse: collapse;
  margin: 0 auto;
  margin-top: 15px;
  width: 550px;
}

#but_import{
  margin-left: 10px;
}

5. Conclusion

With an above PHP script, you can provide an interface to the users for import data and insert it into a MySQL database table with PHP.

Remove the skip if($skip != 0) condition from the PHP script if the CSV file doesn’t have the header row.

If you found this tutorial helpful then don't forget to share.