Jump to content

Update on dulipcate entry while Import Excel file - php mysql


shashib

Recommended Posts

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

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);
      
    if($col1 == $pfetech['prdid']){
 
        $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>";                            
     }
    }

    }
Edited by shashib
Link to comment
Share on other sites

any one here ....only my UPDATE QUERY IS NOT WORKING ... SELECT QUERY IS PERFECT ...IT TAKES ME IN IF CONDITION IF == else it take me in else loop

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

this is what echo/print my update query shows....but in database value is not update ....

Link to comment
Share on other sites

That's not the same query you have in the code, if you print the query in the code it will be different than what you showed. But the easiest way to do that is to set a primary key on the table and use INSERT ... ON DUPLICATE KEY UPDATE to update if it already exists, then you only need 1 query and don't have to look anything up.

 

Also, your code won't run in the current version of PHP, because you're using database code that is 12 years old and has been removed. You should use PDO instead of the mysql extension, and you should use prepared statements to make sure that your queries aren't going to break based on the data.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...