avoid duplicate entry INSERT SQL

Question

Im inserting the daily attendance. I want to avoid duplicate entries. The unique columns should be the month, day and ID altogether. My condition is "if month and day exist for that employee ID,(For example, if there's already an attendance for March 22 for employee#40) it should return an error and should not proceed to inserting. This is my code.

    $avoid_double_entry = query("SELECT * FROM attendance WHERE 
    employee_id = '$employee_id' AND month = '$month' AND day = '$day' ");
    confirm($avoid_double_entry);

    if($row = fetch_array($avoid_double_entry)) {
            echo "already exists";
    } else {
        echo "no records found";
     <--proceed to insert query-->

However, when I run this code, and when there's an entry for DAY 22, even the month is not March and the employee ID is not 40, it says already exists. It also happens when the day is not 22 but the month is March, vice versa.

It only says no records found when the three conditions are not met. I kind of understand this part. What I don't understand is why it says already exists even only one condition is met. Thanks in advance for your help.

I already tried not query WHERE NOT, DUPLICATE KEY UPDATE, but still cant figure it out..

By the way, this is my confirm function which is working fine, but just in case.

 function confirm($result) {

global $connection;

if(!$result) {

    die("Query failed" . mysqli_error($connection)); 
}

}

Make id, month, day a key on your table, no need for any PHP here at all. — Kaylined, Apr 20 '18 at 22:16

Don't Panic · Answer 1 · 2018-04-20T22:24:01.470

0

You don't need to execute a SELECT query before executing an INSERT query to prevent duplicate entries.

You need to create a unique index over the multiple columns that must be unique in your table.

Then, execute your INSERT query. Duplicates will not be inserted. If you have other columns that should be updated if duplicates are encountered, you can use ON DUPLICATE KEY UPDATE. Otherwise INSERT IGNORE will prevent you from getting any errors when you try to insert duplicates (while still not inserting them).

Also, I would highly recommend storing your dates in a single date type column rather than storing the separate date parts in different columns.

edited Apr 20 '18 at 22:24

answered Apr 20 '18 at 22:18

Don't Panic

41,125
10
61
80

I guess Tim lost his keys again. – Don't Panic Apr 20 '18 at 22:28
Maybe Tim thought that what you posted was plagiarizing on an existing Q&A? Btw, my name's not Tim ;-) – Funk Forty Niner Apr 20 '18 at 22:44
Plagiarizing? Really, @Funk? Is there a certain one you're thinking of? – Don't Panic Apr 20 '18 at 23:44
I don't know, I can't speak for the guy (or gal). Kind of seems like it to me in a way. – Funk Forty Niner Apr 20 '18 at 23:54
@Funk Well, I hope not. Plagiarism is a serious accusation. I'm not one to get my feelings hurt over a random downvote, but if someone really thinks I've plagiarized something, I'd like to know more about it so I can address it. If my answer resembles another, it's purely coincidental. Hopefully it doesn't resemble something else enough for someone to have that doubt. – Don't Panic Apr 21 '18 at 00:42
Oh I never said *you* plagiarized. Coincidences can and do happen, no doubt about that. – Funk Forty Niner Apr 21 '18 at 00:44

avoid duplicate entry INSERT SQL

1 Answers1