Table set up

[squiblo]

I'm having trouble thinking of the best way to set up a table for a like a dislike function, a user can like or dislike something but only once, they cannot like the same thing twice. What will be the best way to set up this table?

 

The data recorded will be: Id (auto increment), thing being like, user id, like or dislike.

 

Thanks

 

 

[attachment deleted by admin]

[RussellReal]

things like this make mysql kind've obsolete, basically mysql being a relational database, needs things to relate to other things.. basically to have data work together you need to bounce back and forth..

 

the correct way to do this with mysql would be to have a "like" table..

 

with the structurelike so:

 

id | like | dislike | user_id | media_id | comment_id

 

and use this table for everything..

 

the drawbacks of this.. is you will run outta available ids in the long run.. even though its like some super large never to be reached number on 64bit servers.. ( I don't have this number off the top of my head )

 

but.. you could do it the WRONG way.. and have it comma seperated... in the particular table

 

for example..

 

id | media_name | media_source_path | media_description | media_likes | media_dislikes | voters

 

and pull a result.. say I'm voting on video with the id number 37

 

SELECT * FROM media WHERE id = 37;

 

we now have the row.. pretend I do all the php to get that result set..

if (in_array($_SESSION['id'],explode(',',$resultSet['voters']))) {
  // hes in here screw him!
} else {
  // hes not!! WOO HOO!
  // set voters to include his id..
  $x = explode(',',$resultSet['voters']);
  $x[] = $_SESSION['id'];
  $x = implode(',',$x);
  if ($_GET['like']) {
    // 1 or 0 expecting lets say
    $set = "media_likes = media_likes + 1";
  } else {
    $set = "media_dislikes = media_dislikes + 1";
  }
  mysql_query("UPDATE media SET {$set}, voters = '{$voters}' WHERE id = '{$_GET['media_id']}");
  // update that row!
}

 

but the WRONG way has some drawbacks aswell.. there is a limit on text fields aswell, but that is a couple gigabytes for large blobs.. its really up to you what you want to do.. those are two ways to go about doing it

 

[jayarsee]

To mitigate the issue that RussellReal mentions about running out of ID numbers eventually, that has more to do with it being sometimes or even often incorrect, as it would be in this case, to use auto-incrementing integers as your method of generating unique IDs. A more appropriate method of generating unique row IDs for this sort of table would be to create a hash out of some combination of the user's id and the media id, especially since each combination of those will always have to be unique.

 

You could also set like or dislike as a single column containing a 1 or 0 to save space. comment_id would also be useful if there is more than 1 comment per like/dislike vote, else the comment could be stored there directly.

[DavidAM]

Use a multi-column primary key.  You do not need an auto_increment on this table.

 

CREATE TABLE WhoLikes (
  ThingID  INTEGER NOT NULL,
  UserID   INTEGER NOT NULL,
  LikesIt   BOOLEAN NOT NULL DEFAULT 0,
  PRIMARY KEY (ThingID, UserID)
)

 

Insert a record when the user votes. Set LikesIt to 1 (one) for Likes or 0 (zero) for Dislikes. If the user does not have an entry, they have not voted.  With this arrangement, the user can change their mind or vote multiple times

 

INSERT INTO WhoLikes (ThingID, UserID, LikesIt) VALUES (21, 7, 1) 
ON DUPLICATE KEY SET LikesIt = 1

User ID #7 now Likes ThingID #21. If the user votes again, you are just updating the same value, so nothing changes.  If the user votes Dislikes later,

INSERT INTO WhoLikes (ThingID, UserID, LikesIt) VALUES (21, 7, 0) 
ON DUPLICATE KEY SET LikesIt = 0

User ID #7 now DIS-Likes ThingID #21.  And he still has only one vote in the table.

 

To get the number of Likes and Dislikes (just in case you are wondering):

SELECT SUM(LikesIt) AS LikesVotes, 
  SUM(IF(LikesIt = 0, 1, 0)) AS DislikeVotes,
  COUNT(UserID) AS TotalVotes
FROM WhoLikes
WHERE ThingID = 21

[squiblo]

Thanks DavidAM, just one thing, how do I get 'LikesVotes', 'DislikeVotes' and 'TotalVotes' into php variables

[DavidAM]

Using the standard process - something like this maybe:

$thingID = 21;
$sql =sprintf('SELECT SUM(LikesIt) AS LikesVotes,
  SUM(IF(LikesIt = 0, 1, 0)) AS DislikeVotes,
  COUNT(UserID) AS TotalVotes
FROM WhoLikes
WHERE ThingID = %d', $thingID);
$res = mysql_query($sql);
if ($res) {
    // We only have one row so we don't need a loop
    $row = mysql_fetch_assoc($res);
    if ($row) {
        $VotesForLike = $row['LikesVotes'];
        $VotesForDislike = $row['DislikeVotes'];
        $VotesTotal = $row['TotalVotes'];
    } else {
        // No row returned - Either ThingID does not exist or it has no votes
        $VotesForLike = 0;
        $VotesForDislike = 0;
        $VotesTotal = 0;
    }
} else {
    // The Query Failed, so do something 
    // you must have typed something wrong because my code is always perfect
}

It is not strictly necessary to select the COUNT(UserID) because the total votes should always be equal to LikeVotes plus DislikeVotes.