CODEWITHSUNDEEP
phpmysqlsql
TheHerndog
TheHerndog

Reputation: 67

How to order comments by likes/dislikes in MySQL

On my website people can thumbs up or thumbs down a comment. To do this I use two tables:

$sql = "CREATE TABLE content
(
id INT NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(id),
content TEXT NOT NULL,
date date,
time time
)";

and

$sql2 = "CREATE TABLE ratings 
(
rating_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL ,
rating VARCHAR (10) NOT NULL ,
id INT NOT NULL ,
ip VARCHAR (50) NOT NULL
)";

The data stored in the ratings would be as follows:

Comment ID   like/dislike    user IP
1            l               86.42.173.83
1            d               86.42.173.43
2            l               86.42.173.79
2            l               86.42.173.34
2            d               86.42.173.22

The problem I'm having is that I'm finding it extremely difficult to create a SQL statement to order the comments by the amount of likes they have.

If anyone has any ideas on how to do this it would be greatly appreciated.

Upvotes: 0

Views: 310

Answers (2)

Seain Malkin
Seain Malkin

Reputation: 2303

It would be easier if you stored likes as integers and not letters.

I added up the likes using a case statement and grouped by comment.

SELECT C.content, 
    SUM(CASE WHEN R.rating = 'l' THEN 1 ELSE -1 END) AS overallRating
FROM content C
LEFT JOIN ratings R ON R.id = C.id
GROUP BY C.content
ORDER BY overallRating

Upvotes: 2

Dan Bracuk
Dan Bracuk

Reputation: 20804

something like this will work

select content.text, count(*) likes
from content join ratings on content.id = ratings.id
group by context.text
order by likes

Upvotes: 0

Related Questions