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

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?

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.

The data type can be anything you like. CHAR, VARCHAR, TINYINT, or BOOL are all popuar choices. Personally, I'd go with TINYINT.

As you say, this is a simple task. I'm sure that there is discussion out there on assigning actions to buttons.

You're asking different questions with some underlining implications here. I'll try to help you figure it out.

First, the database.

There are several things to consider here :

  • Do you have users ? If you do, you will have to create a voting table linking your users with their votes, as suggested in a previous answer. You do not want to use COUNT to know how many votes there are though. This is time consuming on a big database. Just create a counter field in your comments table and increment/decrement it so you don't have to count every time.
  • If you don't have users and anyone can vote on the site, you may want to consider using a cookie or some kind of temporary identifier (like their IP address, even though it isn't reliable at all) to prevent bots from spamming the votes. It is, in itself, quite a complex problem and I would advise you to limit the votes to registered users.
  • The "vote" field in itself will be a TINYINT, as suggested.

Now the query in itself.

Once again, several possibilities depending of your knowledge.

  • The easiest way is to create a form, send the results to the same page and have php check is post data is set when loading the page. If so, send a query with the post data. There are lots of tutorials explaining how to do that properly (with data sanitation and such).
  • However, the aforementionned solution will reload the whole page. This is messy and not really useful. To creater a smoother user experience, you should do this in AJAX. If you're new to it but want to try it out, you should check out jquery, which is a good javascript library.