Prevent Duplicate Entry using php and mysql in codeigniter

Question

Hi i am having trouble in inserting a tracking number in database. it seems that in some cases it generates a duplicate entry. I am generating the tracking number base on the last entry in my first_track table and increment it by 1. now my problem is that when ever the user clicks at the same time. it generates the same tracking number. how do i prevent it? btw here is my code in generating the tracking number. i am also returning the count to 0001 every 1st entry of each month.

<!----------Model-------->
            $this->db->order_by("first_trackid", "desc");
        $query = $this->db->get('first_track');
        if($query->num_rows() > 0)
        {
            $result = $query->result();
            if(date('m') != substr($result[0]->dtsno,2,2)){
                $dtsno = date('ym').'0001';                 
                }
            else{
                $dtsno = $result[0]->dtsno+1;
            }
            return  $dtsno;

        }
        else
        {
            return  $dtsno = date('ym').'0001';                 
        }
<!--- END model------->
<!---controller----------->
//call the model for generating dtsno
$firsttrack->dtsno = $this->user_information_model->dtsno();
//insert to table first_entry
$this->user_information_model->first_track($firsttrack);

you want to generate track id in serial format or any random no ? — Denis Bhojvani
– Denis Bhojvani, Commented Feb 16, 2017 at 8:36
i want to generate it in increment per month. for example for january 2017 the first entry would be 17010001 then the next entry for january would be 17010001+1. the 17 would be the year and the 01 would be the month then the next 4 digits would be the count of tracking number per month — PiDO
– PiDO, Commented Feb 16, 2017 at 8:37
So you are code make duplication when more than one user submit for at same time ? — Denis Bhojvani
– Denis Bhojvani, Commented Feb 16, 2017 at 8:41
yup. that is my main problem. 2 different users submits entry at the same time or maybe with a very few seconds interval. — PiDO
– PiDO, Commented Feb 16, 2017 at 8:42

Heartine Lazar · Accepted Answer · 2017-02-16 08:42:32Z

1

First of all, in order to ensure that you do not get duplicated values in the database, make sure you index(Set it as unique) the column ("first_trackid") which is holding the tracking number in the table first_track.

Second, you make use of a temporary track sequence number based on timestamp, when the user initiates the process.

The actual generation of tracking number should take place when the user goes to complete the whole process or in other words, saves the record. At that time, generate that number and display to the user accordingly. In that way, you can ensure that the values will never be duplicated in your schema.

Regards

answered Feb 16, 2017 at 8:42

Heartine Lazar

1538 bronze badges

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

5 Comments

PiDO Over a year ago

Hi thank you for your answer. what i did is i generate the tracking number after the user submits the inputs but still some time it generates duplicate tracking number.

Heartine Lazar Over a year ago

Is the column indexed with "unique" mode? If it is defined, then you can trap the mysql exception when you try to save the code which sometimes is getting duplicate(as you are saying). Once the exception is caught, regenerate a new tracking number and save the record again.

PiDO Over a year ago

Looks like i forgot to make the field as unique. I will try to make it as you said. Thank you! will vote this answer up.

PiDO Over a year ago

hi. looks like its trapping the duplicate entry. now my problem is how do I increment the value after the error so that we can insert the record.

Heartine Lazar Over a year ago

Once, you are able to trap the exception, in the exception handling section, increment the tracking id or get a new tracking number from the database and use that one to update back your record. If this does not help, try to generate the tracking number in the database via trigger and let MySQL do it for you. You can later retrieve the updated tracking number using your model and display to the user once the record is saved. Try using "BEFORE INSERT" trigger with "For EACH Row" mode(Row level trigger).

Collectives™ on Stack Overflow

Prevent Duplicate Entry using php and mysql in codeigniter

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related