php and mysql site design question

Question

I am trying to build a website with mysql and php. This is the first site I have attempted so I want to write a little plan and get some feedback.

The site allows users to add some text in a text field as a “comment”. Once the comment has been entered into the site it is added to the database where it can be voted for by other users.

When a new comment has been added to the database it needs to create a new page, e.g. www.xxxxx.com/commentname or www.xxxxxx.com/?id=99981.

There will be a list of "Comments" in the database along with the number of votes for each comment.

The home page will have two functions.

1) Allow users to add a "comment"

2) Display two tables, each with 20 rows containing most "popular comments" and "recent comments"

Each comment will generate its one page where the comment will be displayed. Here users can read the comment and Vote for the comment if they wish.

Please help me out by explaining how to do the following.

-Generate a new page whenever a comment is added to the database

-Add a vote to the vote count in the comment database.

-Display the top 20 most popular comments as per number of votes.

Answer 1

-Generate a new page whenever a comment is added to the database

You only need a comment.php file with a MySQL query getting the given comment out of the database. I would recommend to use the comments primary key to get the comment. Using rewrites you can have a URL like this: www.xxxx.com/comment/1. If you need the redirect for a specific link structure ask again.

-Add a vote to the vote count in the comment database.

Just add a column to your table holding the votes. If you have logged in users and you want then to check their votes, create a new table for the votes and another table for the many to many realtion.

-Display the top 20 most popular comments as per number of votes.

This is simply done by sorting in the MySQL queries and selecting only 20 results:

// For the recent 20 comments
SELECT * FROM comments ORDER BY id DESC LIMIT 0,20
// For the 20 most popular comments
SELECT * FROM comments ORDER BY votes DESC LIMIT 0,20

Any further questions?

Answer 2

This is a pretty broad question, i don't think we'll be able to help you fully here at stack without making a full blown php blog tutorial!

I'll try and point you in the right direction however. Firstly i'd say take a look at wordpress, even though i presume you want to make your own custom one, wordpress would be a good starting point for code inspiration? (Just a thought)

The way i'd generate a new page, would be to make a php page, say comments.php, which using the $_GET variable, gets the related record in the database and displays it.

Adding a vote up or down is as simple as adding form to the page with two submit buttons, one with a value of 1 one with a value of -1, upon submit it sends its value to the database, and takes the existing vote value say 25 and adds its value so, if u up voted 25 + 1 = 26 if you downvoted 25 + -1 = 24.

Displaying the 20 most popular comments is just a case of using some SQL sorting, something like this would work

SELECT * FROM comments ORDER BY votes DESC LIMIT 0, 20

That statement selects all the columns from the comments table, sorts it by the votes column decending, so highest value first, and then limits the number of records it fetches by 20, from there its a case of looping through each record and displaying it how you wish.

I hope this atleast gets you started on the right path :)