CODEWITHSUNDEEP
phpmysqlcodeigniter
Rajan
Rajan

Reputation: 2425

how to handle duplicate records in SQL?

I am uploading a file to mysql database. Now the file contains records of login and logout of users. Below is its structure. strcture

I am using PHP codeigniter to upload that file and insert its data into SQL. Here date_data is Defined as UNIQUE So that i dont get any duplicate records for same day as it contains information of daily user's login and logout data.

Now in a case where i have uploaded a data of 1st Nov to 10th December. and then again i upload data from 1st December to 1St January i will get an error because it will give error for the duplicate data from 1st Dec and other consecutive days. Is it possible that i can skip the duplicate data and insert the remaining unique data??

As for my current code it stops the execution when it finds duplicate data. i want to insert only that data which is unique.

Below is my code to insert into SQL table:

Controller

public function upload()
{ 


    $file = rand(1000, 100000) . "-" . $_FILES['file']['name'];
    $file_loc = $_FILES['file']['tmp_name'];
    $file_size = $_FILES['file']['size'];
    $file_type = $_FILES['file']['type'];
    $folder = "uploads/";
    $location = $_FILES['file'];


    $new_size = $file_size / 1024; // new file size in KB
    $new_file_name = strtolower($file);
    $final_file = str_replace(' ', '-', $new_file_name); // make file name in lower case

    if (move_uploaded_file($file_loc, $folder . $final_file)) 
    {



            $handle = fopen($folder.$final_file, "r") or die("file cannot open");

            if ($handle) {
                while (($line = fgets($handle)) !== false) 
                {
                    $lineArr = explode("\t", "$line");    

                    $result = $this->attendance_m->insert_file_content($lineArr) ;    
                }
                if (fclose($handle)) {

                      $this->alert('successfully uploaded', 'admin/attendance.php?success');
                    redirect('admin/attendance');
                }

            } 
            else{
                echo "file cannot open";
            } 

    }

    }

The Model:

public function insert_file_content($lineArr) {

$data = array(
   'emp_id'     => $lineArr[0],
   'date_data'  => $lineArr[1],
   'abc'        => $lineArr[2],
   'def'        => $lineArr[3],
   'entry'      => $lineArr[4],
   'ghi'        => $lineArr[5],
);

  $this->db->insert('daily_data2', $data);
   }

Upvotes: 1

Views: 216

Answers (1)

Basheer Kharoti
Basheer Kharoti

Reputation: 4302

To ignore a duplicate record on insert 

 $data = array(
 'emp_id'     => $lineArr[0],
 'date_data'  => $lineArr[1],
 'abc'        => $lineArr[2],
 'def'        => $lineArr[3],
 'entry'      => $lineArr[4],
 'ghi'        => $lineArr[5],
);

$sql = "INSERT IGNORE INTO `daily_data2` 
(`emp_id`,`date_data`,`abc`,`def`,`entry`,`ghi`) 
 VALUES
(?,?,?,?,?,?)"; 
$this->db->query($sql, $data);

You could also try out this way

$result = $this->db->get_where('daily_data2', array('date_data' => $data['date_data'));

if(count( $result->result_array() ) < 1)
{
    //insert a new record
}

Upvotes: 2

Related Questions