0

i have a .csv file where its data get inserted into database,but my code has a drawback like

if my .csv file contain a row and its inserted in db then to insert more row the user should remove that 1st row from .csv and put new row value then it will insert else duplicate key entry as a column in my db have unique key.

my code for inserting .csv to mysql.

<?php
//db connection goes here
$csv_file="/var/www/html/141.csv";
 $savefile="141.csv";
  $handle = fopen($csv_file, "r");

$i=0;
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
if($i>0){
$import="INSERT into  lead141(urn,phone)values('".addslashes($data[0])."','".addslashes($data[1])."')";
mysql_query($import) or die(mysql_error());
}
$i=1;
}

$test="select * from lead141
where phone in (select phone from dnc)";
$query=mysql_query($test);
$testing=count($query);
echo "matched rows".$testing; 

?>

my mysql lead141table structure

    CREATE TABLE `lead141` (
 id int(100) NOT NULL AUTO_INCREMENT,
 urn int(100) NOT NULL,
 phone bigint(20) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `phone` (`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1

my 141.csv file

urn    phone
141     20134325
141     20985675

if i run my code 141.csv inserting into database but when a user is adding new rows in 141.csv the user should remove the previous rows and add new rows to 141.csv for inserting.

i need to make my code work to insert new values from 141.csv to db evenif the old values are present in .csv.i think if i put a update statement and then insert then it will do the trick.

am i into right path for the logic? please help

  • Why not use [LOAD DATA INFILE](http://dev.mysql.com/doc/refman/5.6/en/load-data.html) instead? Using `REPLACE` | `IGNORE` as appropriate – Mark Baker Jun 21 '14 at 11:05
  • Friend, Before 3 years , I worked on DNC system. On that time I used PhoneNumber as Primary key with Bigint data type.Then you will have no need to take unique key. – Vikas Kumar Jun 21 '14 at 11:23
  • actually i shorten my column list here. –  Jun 22 '14 at 17:44

2 Answers2

2

Try to use INSERT ... ON DUPLICATE KEY UPDATE Syntax like this:

$import="INSERT INTO lead141(urn,phone)values('".addslashes($data[0])."','".addslashes($data[1])."') ON DUPLICATE KEY UPDATE phone= '".addslashes($data[1])."'";

Because phone column is UNIQUE KEY.

TotPeRo
  • 6,561
  • 4
  • 47
  • 60
0

You can use INSERT IGNORE INTO ... too, but I prefer the ON DUPLICATE KEY UPDATE ... functionality, because:

"INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE"

Community
  • 1
  • 1
lopo
  • 306
  • 1
  • 4