Duplicate Records on Insert

Question

I have a database table that records which site_id is being used for each user_id. I am getting duplicates everytime I save the settings. I want it so I can query the table for user_id = 99 and see which site_id is associated with that user_id.

SQL:

  $sql = "INSERT INTO user_sites (rooftop_id, site_id, created_at, active)
                VALUES ('$rooftop_id','$key', now(), '$value')
                ON DUPLICATE KEY
                UPDATE updated_at = IF(active=VALUES(active),updated_at,NOW()),
                active = VALUES(active)
               ";

Current Output:

+----+---------+---------+--------+
| id | user_id | site_id | active |
+----+---------+---------+--------+
|  1 |      99 |      30 |      1 |
|  2 |      99 |      20 |      1 |
|  3 |      99 |      40 |      1 |
|  4 |      32 |      30 |      1 |
|  5 |      32 |      20 |      1 |
|  6 |      32 |      40 |      1 |
|  7 |      32 |      30 |      0 |
|  8 |      32 |      20 |      0 |
|  9 |      32 |      40 |      0 |
+----+---------+---------+--------+

Desired Output:

+----+---------+---------+--------+
| id | user_id | site_id | active |
+----+---------+---------+--------+
|  1 |      99 |      30 |      1 |
|  2 |      99 |      20 |      1 |
|  3 |      99 |      40 |      1 |
|  4 |      32 |      30 |      0 |
|  5 |      32 |      20 |      0 |
|  6 |      32 |      40 |      0 |
+----+---------+---------+--------+

Answer 1

Have you defined a unique key on the table? For what you want, I think it is:

create unique index admin_sites_userid_siteid on admin_sites(user_id, site_id);

This is then used to define the when an insert matches an existing row.