Clever Geek Handbook

INSERT INTO - ON DUPLICATE KEY

I got this request in a loop:

while ($row = mysql_fetch_array($query)) {
    INSERT INTO restaurant_views (date, restaurant_id, views) VALUES ('{$current_date}', '{$row['id']}', 1) ON DUPLICATE KEY UPDATE views=views+1
}

What this code should do is update the views column inside the restaurant_views table .
The problem is that it doesn't check if date and restaurant_id is duplicate, it just checks if the primary column in the database is duplicate.

Here is a screenshot of the table: enter image description here

As you can see, both date and restaurant_id are unique, but this code gives me this:

enter image description here

The request was looped 7 times, as you can see in the views. It is not right. I want 7 different rows with 1 view in each one.

...

I hope you understand what the problem is!

Thanks in advance,
Tompa

+3


source to share


1 answer


If you want uniqueness in the id / date of a restaurant, you need a unique index / constraint on these columns:

create unique index idx_restaurant_views_2 on restaurant_views(restaurant_id, date)


Then your code should work.

+2


source






More articles:


All Articles