How to avoid duplicated entry when adding on database

user_11936150 0
Viewed 168

how can i avoid to add duplicated entry in my database that is based on date? where instead of inserting again the duplicate entry, i will just update the old data to be deactivated and insert the new duplicate entry.

Here is the structure Price table

id        date_created        Value            is_active
1       2019-10-01 1:00:00      25                 0
2       2019-10-05 2:00:00      30                 0

but imagine that the user added a duplicated data again for the Price table which is like this.

2019-10-05 3:00:00

so what i want to do is to update the old entry that has the same date of the user entry to be is_active 1 and insert the new entry.

is there a way to do this? doing this on PHP is really complicated for me because of the use of looping, but i think there is a way in MYSQL which i cant figure it out.

codeigniter mysql php database
3 Answers

You could first try to fetch the entry with the specific date and then update, otherwise insert.

You should also set the date_created column to UNIQUE.

user_121537000

As in the official documentation:

If you specify an ON DUPLICATE KEY UPDATE clause and a row to be inserted would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row occurs.

In your case I suggest to you to do these steps:

  • create a new column date_time_created as DATETIME

  • convert date_created as DATE and save in it only the date

    e.g. $date = date('Y-m-d', strtotime($yourDateTime));

  • set date_created as UNIQUE index:

    CREATE UNIQUE INDEX date_created ON Price(date_created);

INSERT INTO Price
(Value, date_created, date_time_created, is_active)
VALUES
('$theValue', '$date', '$dateTime', 1)
ON DUPLICATE KEY UPDATE is_active = 0

The conversion of date_created in DATE and the creation of date_time_created as DATETIME are necessary because is useless in your case to make a DATETIME unique because you need to check the value only by date. This also improve the readability and maintenance of your script.

user_23425580

Query the table and check if that date exists and is_actice = 1 and use the results to first update existing row then insert

user_13905480
Related