"Liking" individual comments in PHP/MySQL

Question

I built a website for the fantasy movie league that I run (similar to fantasy football but for motion pictures) using PHP and MySQL. Each movie's profile page has a place where you can leave comments and I have that part working properly. Now, I want to add the ability to "like" individual comments but I'm having some trouble. I think I have the "likes" table set up properly and I'm able to add a like to each individual comment, but displaying the likes inline with each comment is another story.

Here is the code for the "comments" table:

CREATE TABLE `comments` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`movie_id` int(11) NOT NULL,
`player_id` tinyint(2) NOT NULL,
`date_time` datetime NOT NULL,
`comment` text CHARACTER SET utf8 NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Here is the code for the "likes" table:

CREATE TABLE `likes` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`player_id` int(2) NOT NULL,
`comment_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `player_comment` (`player_id`,`comment_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

Here is the code for the "players" table:

CREATE TABLE `players` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(10) NOT NULL,
`username` char(10) NOT NULL,
`password` char(32) NOT NULL,
`email` varchar(100) NOT NULL DEFAULT '',
`active` tinyint(1) NOT NULL,
`admin` tinyint(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Now, here is the SQL statement that I use to grab all the comments for any particular movie:

SELECT comments.id as comment_id, comments.player_id, comments.date_time, comments.comment,
players.first_name
FROM comments
LEFT JOIN players on players.id = comments.player_id
WHERE movie_id = $movie_id
ORDER BY date_time ASC

My problem is, I'm not sure how to grab all of the likes for each comment in the same statement. I want to be able to display each player's first name that has liked that particular comment. So I want it to look like this:

• Line 1: $comment
• Line 2: $commenter_first_name
• Line 3: $liker_first_name1, $liker_first_name2, $liker_first_name3 likes this

I'm able to get all of the likers for an individual comment like this:

SELECT first_name
FROM likes
LEFT JOIN players on players.id = likes.player_id
WHERE comment_id = 264
ORDER BY first_name ASC

But I don't know how to incorporate this without using the actual comment_id.

Hope this makes sense, I welcome any possible help.

Answer 1

How about something like:

SELECT comments.id as comment_id, comments.player_id, comments.date_time, comments.comment, likers.names
FROM comments
LEFT JOIN
    (SELECT comment_id, GROUP_CONCAT(first_name) AS names FROM likes 
    INNER JOIN players ON likes.player_id = players.id GROUP BY comment_id) AS likers 
ON comments.id = likers.comment_id
WHERE movie_id = $movie_id
ORDER BY date_time ASC

The "likers" table should have something like:

| (comment_id) | Bob,Jim,Kathy |

For better list formatting, you can specify a separator:

GROUP_CONCAT(first_name SEPARATOR ', ') 

Answer 2

Cant you just add the linkes to the comments table?

CREATE TABLE `comments` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`movie_id` int(11) NOT NULL,
`player_id` tinyint(2) NOT NULL,
`date_time` datetime NOT NULL,
`comment` text CHARACTER SET utf8 NOT NULL,
`likes` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And then when somebody likes the comment, you just also add +1 to the likes in comment DB, not only in likes DB. And when somebody removes his like, you juset change the number to -1

Like this:

"UPDATE comments SET likes=likes+1 WHERE id='$comment_id'";

The problem I see is, that you want to show the likers...

So maybe something like this:

"SELECT comments.*, likes.* FROM comments INNER JOIN likes comments.id=likes.comment_id WHERE movie_id='$movie_id'";

Your select:

"SELECT comments.id as comment_id, comments.player_id, comments.date_time, comments.comment, players.first_name, likes.player_id
FROM comments
LEFT JOIN players ON players.id=comments.player_id
INNER JOIN likes ON comments.id=likes.comment_id 
WHERE movie_id='$movie_id'
ORDER BY date_time ASC";

The other thing is, that it will only return you the ID's of the peoples that liked the comment. So I would suggest you to save also users name to the likes table.