How to check for a duplicate row before inserting using PHP

Question

MYSQL (attendance) Table:

--------------------------------------------------------
| ID | EmployeeID | Name | TimeIn | TimeOut | Date     |
+----+------------+------+--------+---------+----------+
| 4  | 7          | Jack |   4    |    16   | 18-11-16 |
+----+------------+------+--------+---------+----------+
| 5  | 7          | Jack |   4    |    16   | 19-11-16 |
+----+------------+------+--------+---------+----------+
| 6  | 8          | Dave |   4    |    16   | 19-11-16 |
+----+------------+------+--------+---------+----------+

PHP code to insert a new row:

$SQL = "INSERT INTO attendance (ID, EmployeeID, Name, TimeIn, TimeOut, Date) VALUES ('$ID', '$EmployeeID', '$Name', '$TimeIn', '$TimeOut', '$Date');";

$Results = mysql_query($SQL);

if (!$Results){
echo "Error inserting data.";
}

According to my code, the user can record same row (same attendance) for the same employee and in the same time (duplicate) like the following:

--------------------------------------------------------
| ID | EmployeeID | Name | TimeIn | TimeOut | Date     |
+----+------------+------+--------+---------+----------+
| 6  | 8          | Dave |   4    |    16   | 19-11-16 |
+----+------------+------+--------+---------+----------+
| 7  | 8          | Dave |   4    |    16   | 19-11-16 |
+----+------------+------+--------+---------+----------+

How to make sure I'm not inserting the same row twice?

Please let me know if you need more information. I appreciate your help.

set a UNIQUE constraint or check if it exists first – Funk Forty Niner Nov 19 '16 at 16:54 — Funk Forty Niner, Nov 19 '16 at 16:54

score 1 · Answer 1 · answered Nov 19 '16 at 16:56

You should make sure your ID column is a unique primary key. And then you use ON DUPLICATE KEY UPDATE

SQL = "INSERT INTO attendance (ID, EmployeeID, Name, TimeIn, TimeOut, Date) VALUES ('$ID', '$EmployeeID', '$Name', '$TimeIn', '$TimeOut', '$Date') ON DUPLICATE KEY UPDATE (EmployeeID=VALUES(EmployeeID),Name=VALUES(Name);";

...and so on...

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

0

The way to go here is to set a sql unique constrain on the columns that should be unique. You need to do this in the database. Then Check for an exception in your PHP Code, when you try to insert a new row.

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 19 '16 at 16:55

zabeltech

963
11
27

score 0 · Answer 3 · answered Nov 19 '16 at 16:59

0

First of all, you shouldn't be using mysql_query as it's deprecated. Instead, you can use mysqli_query (for example).

To answer your question, your ID should be unique (this is a common thing). Once it's unique, you can then check if your insertion was successful or not (with if, for example).

answered Nov 19 '16 at 16:59

Gynteniuxas

7,035
18
38
54

The ID is unique already. Even if the ID is unique, the user will still be able to duplicate the same row but only with a different ID, for example, the user will be able to record the attendance of the same employee in the same date. – the7k Nov 20 '16 at 16:36
1

@the7k What will definitely work, is to use `select` query first instead of `insert into`. If you get at least one row, then it means all values are duplicated and you skip the insertion process. – Gynteniuxas Nov 20 '16 at 16:41

How to check for a duplicate row before inserting using PHP

3 Answers3