duplicate rows MySQL

Question

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?

by checking before record insert.. Like_-First Select record, check if exist or not that do actions. — Akshay Paghdar
– Akshay Paghdar, Commented May 31, 2014 at 6:49

Gorkem Yontem · Accepted Answer · 2014-06-01 11:51:16Z

2

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)

answered Jun 1, 2014 at 11:51

Gorkem Yontem

3981 gold badge6 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

souslov · Accepted Answer · 2014-06-01 07:30:15Z

1

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;

edited Jun 1, 2014 at 7:30

answered May 31, 2014 at 6:49

souslov

44.7k11 gold badges92 silver badges113 bronze badges

3 Comments

suyilmaz Over a year ago

i got an error with not exists one "MySQL server version for the right syntax to use near 'WHERE NOT EXISTS ( SELECT * FROM likes WHERE likes_memory_id = ? AND" what is wrong with this?

suyilmaz Over a year ago

yes i did. it inserted the row alright, didnt give any error, however it allow duplicate rows.

suyilmaz Over a year ago

what are those? table_listnames (name, address, tele)

Dileep Kumar · Accepted Answer · 2014-05-31 07:02:38Z

0

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

answered May 31, 2014 at 7:02

Dileep Kumar

5101 gold badge4 silver badges20 bronze badges

1 Comment

suyilmaz Over a year ago

Well likes_owner_id cannot be primary or an unique key because a person might like more than one comment.

Savion Smith · Accepted Answer · 2014-05-31 06:56:00Z

-1

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.

edited May 31, 2014 at 6:56

answered May 31, 2014 at 6:51

Savion Smith

462 silver badges9 bronze badges

2 Comments

suyilmaz Over a year ago

IP? what if user uses another pc or his/her mobile phone?

Savion Smith Over a year ago

There is no effective way of determining if it's the same user if the user uses a different computer (unless you collect an email or phone number from them before processing the "like". If that is the case then you can use the email or phone # as the Primary Key). Other than that, the only thing that uniquely identifies a user is the IP. Hope this helps @suyilmaz

Collectives™ on Stack Overflow

duplicate rows MySQL

4 Answers 4

Comments

3 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related