0

This is my MySQL table:

enter image description here

  • The left column auto increments.
  • The center column is the id# of the user.
  • The right column is the id# of a post that they like.

I am inserting data into the table like so:

$stmt = $db->prepare("INSERT INTO likes (user_id,post_id) VALUES (?,?)");
$stmt->execute(array($_SESSION['user'],$_POST['id']));

This works fine, but as you can see the MySQL table has two identical rows (id's 3 & 4) which both have a user_id of 5 and a post_id of 196. In other words my MySQL table records that the user with id = 5 likes the post with id = 196 two times. how do I disallow a situation where there are two rows that have identical user_id's and identical post_id's

Web_Designer
  • 64,966
  • 87
  • 197
  • 254
  • 1
    Do you need the auto incrementing `id` column? I'd just have a composite PK on `user_id,post_id` – Martin Smith Apr 02 '11 at 22:24
  • @Martin @inquisitive There is rarely a case when you DON'T need an ID column, and this is certainly not one of those times. [See this question and answer](http://stackoverflow.com/questions/5496008/when-we-dont-need-a-primary-key-for-our-table/5496048#5496048) – rockerest Apr 02 '11 at 22:29
  • @rockerest - You seem to be confusing `PK` with `Id` column. They are not the same thing. I suggested the OP has a composite primary key on the 2 columns. – Martin Smith Apr 02 '11 at 22:32
  • @martin, it's pretty clear that `id` is his `PK` – rockerest Apr 02 '11 at 22:39
  • 3
    @rockerest - But it is not remotely clear that the `id` column serves any useful purpose at all (I suspect it doesn't). I suggest ditching it and making a composite primary key over the over 2 columns. – Martin Smith Apr 02 '11 at 22:42
  • @martin +1, I agree now that I'm clear on what you mean. – rockerest Apr 02 '11 at 22:46

3 Answers3

4

You need to set a unique constraint on the user_id and post_id columns

Chris Ballard
  • 3,731
  • 4
  • 26
  • 40
  • This is **wrong**. A UNIQUE constraint will only allow a user to "like" one thing. EVER. Don't do this. – rockerest Apr 02 '11 at 22:30
  • @rockerest - Not true at all. The unique constraint [can be on multiple columns](http://stackoverflow.com/questions/2504007/mysql-unique-constraint-on-multiple-fields). – Martin Smith Apr 02 '11 at 22:35
  • I meant across both columns. Actually you may need an index - see http://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql – Chris Ballard Apr 02 '11 at 22:35
  • Excellent, Thank you. I just got it to work. ...I'm a little slow. – Web_Designer Apr 13 '11 at 14:54
1

Update

The below answer could result in Race Conditions and possibly duplicate database entries in some cases.

Thanks to @Martin for finding the major flaws in the below system:

This is something you should do in business logic. The way I have implemented an almost identical system is this (in psuedo-code):

Show Blog/Video/Post/Etc.

Has user "liked" this?
    Yes = show "unlike" button (on server, not JS)
    No  = show "like" button (on server, not JS)

Add or delete "like" as necessary

The code to show either button is on the server, so the user can't intercept it and manually pick which one to do.

The only flaw is that there has to be a way to identify which button the user clicks, which means the user could spend a lot of time digging and fiddling and possibly change the button/link/whatever on the page.

I overcome this by testing on the server for whether the user has already liked or not, and then inserting only when they have NOT.

rockerest
  • 9,847
  • 3
  • 33
  • 67
  • 1
    Enforcing unique constraints should be done in the database. Anywhere else you will get race conditions and duplicates. – Martin Smith Apr 02 '11 at 22:39
  • @martin, could you explain? I am unclear how you could get race conditions OR duplicates in this scenario. – rockerest Apr 02 '11 at 22:40
  • If I submit multiple requests to your server then 2 concurrent transactions could both check for the existence of the row find its not there and proceed to do the insert. Having unique constraints in the database is also a good thing as the query optimiser can use this knowledge to make more efficient query plans. – Martin Smith Apr 02 '11 at 22:50
0
CREATE UNIQUE INDEX user_post on likes (user_id, post_id)

this prevent to have same (user_id, post_id) values more than one time...

bicccio
  • 384
  • 1
  • 18