CODEWITHSUNDEEP
phpmysqlsql
suyilmaz
suyilmaz

Reputation: 155

prevent multiple query PDO

Hey i am using a query like this:

INSERT INTO likes( 
     likes_memory_id, 
     likes_owner_id, 
     likes_like
 ) VALUES (
     :likes_memory_id, 
     :likes_owner_id, 
     :likes_like)

when ever an user click the like button, this query adds a new row. so this query allows to like multiple time. I want to prevent a row with the same owner_id and same memory_id. To prevent this i may use a select statement and I might succeed it in two queries but I am sure that there is a better way to do it. (I made research about if not exists statement but i didnt understand too much ) How do I avoid multiple likes only with a SQL query?

Upvotes: 2

Views: 136

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

The simplest is to create a unique index on your columns which you want unique;

CREATE UNIQUE INDEX uq_mem_own ON likes(
  likes_memory_id, likes_owner_id
);

...and insert likes using INSERT IGNORE, which will insert the value if it's not prevented by the index, otherwise just ignore it;

INSERT IGNORE INTO likes( 
     likes_memory_id, 
     likes_owner_id, 
     likes_like
 ) VALUES (
     :likes_memory_id, 
     :likes_owner_id, 
     :likes_like)

A simple SQLfiddle to test with. Note that the duplicate row is not inserted.

Upvotes: 4

mifi79
mifi79

Reputation: 1106

You'll need to do this programmatically by checking if the user has already liked the post. One way would be to set a "liked" flag on the page somewhere and disable the like button when the current user has already liked post.

You could either check before your insert by running the following query:

SELECT * FROM likes WHERE likes_comment_id = [comment_id] AND likes_owner_id = [owner_id]

If the query returns no results, execute your INSERT otherwise don't. You can also run this at page load so that you can mark the liked comments and liked comments cannot then be liked again.

Upvotes: 0

Related Questions