user3209031 user3209031 - 5 months ago 20
SQL Question

Update on dulipcate entry while Import Excel file - php mysql

I need to check if value inserting in database while import excel file , if it has already value in database then it should get update.

Below is producttab table value in database

prdid | prdname
00A | prd1
00B | prd2
00C | prd3
00D | prd4


Below is EXCEL FILE data

prdid | prdname
00A | prdnew
00B | prd2new
00E | prd8
00H | prd9


So if i upload above excel file then ,

00A , 00B should get UPDATE IN producttab table as they are already present there... but 00E,00H should get insert

*below is what i have tried, value is getting insert properly
only UPDATE IS NOT HAPPENING, i means IF..ELSE condition is perfect, only UPDATE is not going proper *

if(isset($_POST["Upload"]))
{
$fileinfo = pathinfo($_FILES["uploadFile"]["name"]);

$filetype = $_FILES["uploadFile"]["type"];
$remark = NULL;

//Validate File Type
if(strtolower(trim($fileinfo["extension"])) != "csv")
{
$_SESSION['msg_r'] = "Please select CSV file";
header("location:importfile.php");
exit;
}
else
{
$file_path = $_FILES["uploadFile"]["tmp_name"];
}

$row = 0;

$tempFileName = time().".csv";

if ( is_uploaded_file( $file_path ) ) {
$fileCopied = copy( $file_path , $tempFileName);


if (($handle = fopen($tempFileName, "r")) !== FALSE) {
fgetcsv($handle);
while (($data = fgetcsv($handle, 6000, ",")) !== FALSE) {
$num = count($data);
for ($c=0; $c < $num; $c++) {
$col[$c] = $data[$c];
}

$col1 = $col[0]; // prdid
$col2 = $col[1]; // prdname

$sql = "SELECT prdid FROM producttab WHERE prdid = '".$col1."' ";
$query = db_query($sql);
$pfetech = db_fetch($query);

// ABOVE select QUERY IS PERFECT , it taking me to if conditions

if($col1 == $pfetech['prdid']){

// only below update not happening

$sqlup = "UPDATE producttab
SET prdid = ".$pfetech['prdid'].",
prdname = ".$col2." ";
$sqlup .= " WHERE prdid = ".$pfetech['prdid']." ";
$resultsqlupdate = mysql_query($sqlup);

}else{

$query = "INSERT INTO producttab(prdid,prdname) VALUES('".$col1."','".$col2.")";
$s = mysql_query($query);

}


}
fclose($handle);
}
echo "<center>File data imported to database!!</center>";
}
}

}


Below is print/echo of UPDATE query ,

update producttab set prdname='prdnew' WHERE prdid='00A'
update producttab set prdname='prd2new' WHERE prdid='00B'


Value for prdname is shown proper as per excel file .... only update is not happening ....

Answer

you can use exists query to check whether is exists or not in database , following is query

 select  exists(select 1 from producttab where prdid='".$col1."');

it will give u 1 if it exits and 0 if not then do further insertion or updation on the if else condition

and only set one thing not other like this

  "UPDATE producttab 
          SET  prdname = ".$col2."
  $sqlup .= " WHERE prdid = ".$pfetech['prdid']." "; 

oh sorry u need to update prdname not prdid . edited my answer

ps. u should use mysql import from csv query for this here is link for help http://www.mysqltutorial.org/import-csv-file-mysql-table/

Comments