CODEWITHSUNDEEP
mysqlsql
suyilmaz
suyilmaz

Reputation: 155

duplicate rows MySQL

Hey i am using a query like this:

INSERT INTO likes( 
         likes_memory_id, 
         likes_comment_id, 
         likes_owner_id, 
         likes_like
 ) VALUES (
         :likes_memory_id, 
         :likes_comment_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. to prevent this i may use a select statement and i might succeed in two queries but i assue 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?

Upvotes: 2

Views: 81

Answers (4)

Gorkem Yontem
Gorkem Yontem

Reputation: 398

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)

Upvotes: 2

potashin
potashin

Reputation: 44581

INSERT INTO likes( 
     likes_memory_id, 
     likes_comment_id, 
     likes_owner_id, 
     likes_like
SELECT * 
FROM (:likes_memory_id, :likes_comment_id, :likes_owner_id ,:likes_like) tmp
WHERE NOT EXISTS ( SELECT * 
                   FROM `likes` 
                   WHERE `likes_memory_id` =  :likes_memory_id
                     AND `likes_comment_id` = :likes_comment_id
                     AND `likes_owner_id` = :likes_owner_id
                     AND `likes_like` = :likes_like
                 ) LIMIT 1;

Upvotes: 1

Dileep Kumar
Dileep Kumar

Reputation: 510

You can accomplish this either disable like button after hit or 
using the following query

     INSERT INTO likes 
             (likes_memory_id, 
              likes_comment_id, 
              likes_owner_id, 
              likes_like)
SELECT t1.likes_owner_id
FROM likes t1
WHERE NOT EXISTS(SELECT likes_owner_id
                FROM likes t2
               WHERE t2.likes_owner_id = t1.likes_owner_id);

and make sure that your likes_owner_id is primary or a unique key

Upvotes: 0

Savion Smith
Savion Smith

Reputation: 46

Great question! You can simply collect the IP Address of the user and if this user tries to "like" twice it wont record.

STEP 1: Create a new colum called IP (or whatever you like) [this will be used to collect the user IP Address.)

STEP 2: Set IP to the PRIMARY KEY (This is what prevents duplicate entries)

STEP 3: add it to your query.

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

STEP 4: Now all you have to do is add a line of code to collect the user IP and submit that along with the other info.

Upvotes: -1

Related Questions