单击HTML按钮和php [关闭]更新SQL行

Question

I am making a simple comment system with rate for each comment.

(Did you like this comment?) question with two buttons "Yes" and "No".

I am trying to find a simple way that on clicking the button it updates in the database the number of votes if said yes or no.

I looked in several places over the internet but there was nothing so specific except that I know it is not a big problem and can be done easily.

what is the column type supposed to be and how to write the query on the html button?

Answer 1

I would suggest that you create two tables:

Comments:

• CommentId,
• CommentText,
• ... other columns

Votes:

• VoteId,
• CommentId (on which comment this vote),
• UserId (how rated the comment),
• Liked (a flag 0 (no) or 1(yest)).

When clicking the HTML button, insert a new row in the votes table.

Then to get a number of votes, you can join the two tables and count the number from the second table with a GROUP BY:

SELECT 
  c.CommentId,
  COUNT(COALESCE(v.VoteId, 0)) AS TotalVotes
FROM Comments AS c
LEFT JOIN votes AS v ON c.commentId = v.CommentId
GROUP BY c.CommentId;

You can also query for the likes = 0 or likes = 1 individually if you want, this just an example.