Member Avatar for bhuvi_2

hi i want to know how to delete duplicate rows in imported csv files in phpmyadmin in php..below is my code to import csv file in php,i want to delete duplicated rows in my imported csv files which is in database of xampp(phpmyadmin)can anyone add thecode for it

<?php
$user = 'root';
$pass = '';
$db = 'testdb';
$db = new mysqli('localhost',$user,$pass,$db) or die("unable to connect");
$connect = mysqli_connect("localhost", "root", '', "testdb");
echo"great work!!!";
if(isset($_POST["submit"]))
{
 if($_FILES['file']['name'])
 {
  $filename = explode(".", $_FILES['file']['name']);
  if($filename[1] == 'csv')
  {
   $handle = fopen($_FILES['file']['tmp_name'], "r");
   while($data = fgetcsv($handle))
   {
    $item1 = mysqli_real_escape_string($connect, $data[0]);  
                $item2 = mysqli_real_escape_string($connect, $data[1]);
                $query = "INSERT into excel(excel_name, excel_email) values('$item1','$item2')";
                mysqli_query($connect, $query);
   }
   fclose($handle);
   echo "<script>alert('Import done');</script>";
  }
 }
}
?>  
<!DOCTYPE html>  
<html>  
 <head>  
  <title>Webslesson Tutorial</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>  
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
 </head>  
 <body>  
  <h3 align="center">How to Import Data from CSV File to Mysql using PHP</h3><br />
  <form method="post" enctype="multipart/form-data">
   <div align="center">  
    <label>Select CSV File:</label>
    <input type="file" name="file" />
    <br />
    <input type="submit" name="submit" value="Import" class="btn btn-info" />
   </div>
  </form>
 </body>  
</html>

?>
  • 2 Contributors
  • 1 Reply
  • 338 Views
  • 4 Hours Discussion Span
  • Latest Post Latest Post by cereal
Member Avatar for cereal

Hi,

usually you define which column (or columns) mark a row as a duplicate and set a unique key index, so you have to alter your table schema. Once this is done add IGNORE to your insert query:

INSERT IGNORE into excel(excel_name, excel_email) values('name', 'email@address');

Then use:

$affected = mysqli_affected_rows($connect);

It will return an integer: 0 if the insert failed, 1 if it occurred, -1 means there was an error. For more information, read:

Also, use prepared statements, do not use PHP variables inside queries. And, please, make meaningful titles for your threads: "php" does not help anyone.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.