douzhuang1900
douzhuang1900
2014-06-21 11:04

使用更新插入查询将.csv值插入数据库

已采纳

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

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • dongzhi6927 dongzhi6927 7年前

    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.

    点赞 评论 复制链接分享
  • douzhengnao8265 douzhengnao8265 7年前

    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"

    点赞 评论 复制链接分享