How to avoid duplicate insert to table in codeigniter? [duplicate]

Question

This seems an old question but i didn't find any correct solution to my problem.I will explain the problem i am facing.

In my code part i have checked the table before inserting, whether the table has already has the value for the particular column.

select vid from mytable where vid=$vid;

if the vid value is not there then i have to execute the insert query in mytable else there is no need to insert.

But the the problem is in some how, in some scenario there is synchronous requests has been sent to the application and the above code has been executed more than one time. So there are some duplicate entries for the same vid. How can i prevent with this problem. Can i use begintransaction and endtransaction methods or is there any lock methods available?.

There is no constraints set for vid in during table creation except key.the table has 5 million records. So constraint update is not possible.

synchronous requests has been sent to the application Do you mean Asynchronous request? — Avinash
– Avinash, Commented Mar 2, 2018 at 5:11
Why not set the vid to unique so that it won't duplicate. — Alex
– Alex, Commented Mar 2, 2018 at 5:13
@Alex He mentioned that he cannot add any constrain to the Database — Avinash
– Avinash, Commented Mar 2, 2018 at 5:22
Ah well if modifying the db is out of the question then a script fix is the only method. Maybe try something from here: https://stackoverflow.com/questions/11541171/how-to-lock-tables-with-codeigniter?lq=1 .. — Alex
– Alex, Commented Mar 2, 2018 at 5:26

Usama Khalid · Accepted Answer · 2018-03-02 07:30:28Z

0

Yes you can do this in by using very simple logic. follow these steps

Check item exist in table or not
if exist then generate error

In code igniter all of this will be done in model

Controller

$array = array("database column name" = $vid) // make that array according to you database
$this->modle_class_name->data_checker($array);

Model

// check whole row in database
public function data_checker($info_check){
    $this->db->select("*");
    $this->db->from("table name");
    $this->db->where($info_check); // will check whole row in database
    $this->result = $this->db->get();

    if($this->result->num_rows() > 0){
       // you data exist
       return false;
    } else {
       // data not exist insert you information
       $this->db->insert("table name", $info_check);
       return true;
    }
}

OR

Model

// check row by column in database
public function data_checker($info_check){
    $this->db->select("*");
    $this->db->from("table name");
    $this->db->where("column name", $info_check["column name"]); // will check row by column name in database
    $this->result = $this->db->get();

    if($this->result->num_rows() > 0){
       // you data exist
       return false;
    } else {
       // data not exist insert you information
       $this->db->insert("table name", $info_check);
       return true;
    }
}

I hope this will solve your problem. Because this works for me.

answered Mar 2, 2018 at 7:30

Usama Khalid

95 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ravichandran Jothi Over a year ago

I already have this check before insert. But it fails some times. thats what my issue.

Usama Khalid Over a year ago

ok then to avoid duplication then why don't you try counter method. In controller class set $counter = 0 and set the check in the start of in controller if($counter == 1){ // then generate error } else { // perform check (call model) }

Usama Khalid Over a year ago

OR You can avoid this duplication by using counter method But using database. Here are the steps STEP 1 Create one column (check_status) in database table. STEP 2 Now in model after check the record if data exist then set value to true in-front of that record. else insert the record. So through this when second time request come. System will check status if status is true then don't check. If status is false then check that record.

Oussama · Accepted Answer · 2018-03-02 10:05:31Z

0

You just need to make the field "vid" as unique in your table:

ALTER IGNORE TABLE mytable ADD UNIQUE (vid);

For MySQL 5.7.4 or later:

ALTER TABLE mytable ADD UNIQUE (vid);

answered Mar 2, 2018 at 10:05

Oussama

6447 silver badges21 bronze badges

Collectives™ on Stack Overflow

How to avoid duplicate insert to table in codeigniter? [duplicate]

2 Answers 2

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Linked

Related