creating a voting system

so users can rank articles out of 5

[benihana]

i want my users to be able to rate articles out of 5 so i can display the average ranking and ideally the number of votes.

a colleague gave me a little php to help:

SELECT AVG(vote) AS average, COUNT(vote) AS votescast WHERE article_id = 5;

but i dont really understand how to setup the sql.

i had intended to have a table with 4 columns: - one for article id, one for the sum of all votes, one for the number of votes and one that would contain the average (worked out in php when a vote is submitted to save doing the calculation for every page view).

but now i dont know how to approach this?

any pointers appreciated

thanks
ben

[coopster]

ben,

First, some clarification, then some questions to help you accomplish the task at hand.

>>a colleague gave me a little php to help:
>>SELECT AVG(vote) AS average, COUNT(vote) AS votescast WHERE article_id = 5;
>>but i dont really understand how to setup the sql.

Actually, this is not PHP, this is the SQL (Structured Query Language). This is a standard query to access data within a database file. PHP is a server-side scripting language that, among other things, allows you to process data and requests from user input. jatar_k has some great links in this thread [webmasterworld.com...]
that come as highly recommended reading.

>>...and one that would contain the average (worked out in php when a vote
>>is submitted to save doing the calculation for every page view).

The calculation is not worth saving, in my own humble opinion. This can be done in the SQL extremely fast, or within the PHP code, extremely fast. Take advice from someone with experience, don't save data in a row that is calculated from other data in the same row. It's redundant and if you get out of synch, you have issues.

OK, question number 1:

>>i want my users to be able to rate articles out of 5 so i can
>>display the average ranking and ideally the number of votes.

Do you care if they vote over and over again? For example, I visit this page and vote for my favorite 722 times. Is this acceptable? If not, you'll have to reconsider your table structure to contain a userid or unique identifier so that my vote is only counted once. Then you would record the article_id and my unique identifier as a unique record along with my vote weight. If this is the route, we'll need to do something a bit different.

If it is acceptable, then I would create the table as follows (article_id is set to allow 1 million articles (integer 7)):

CREATE TABLE article_votes (article_id INT(7) NOT NULL, sum_of_votes INT(7), nbr_of_votes INT(7), PRIMARY KEY(article_id));

Then, use your HTML to accept the votes for any particular article_id. If the article_id does not exist in the table yet, you will need to INSERT a new row for that article_id with the sum_of_votes = to the score selected and of course the nbr_of_votes = 1 (for the first vote!). If the article_id does indeed exist, then you will need to read it first, use PHP to calculate the new information, and then UPDATE the row.

Before we go into details on this, let's see which route you wish to take ;)

[benihana]

thanks for your reply coopster.

in the cold light of day i understand the difference between sql and php, but when i posted it was quite late and id had a few in the pub before :~) what i meant is i dont know how to setup the db to deal with this query.

The calculation is not worth saving,

good to know. cheers.

ideally, i dont want people to vote more than once per session , but if they come back tommorrow and vote again - thats ok (i dont think this will be an issue in my target market). I guess im going to have to get the I.P and store it temporarily? or would a cookie be better?

confused
ben

[coopster]

>>what i meant is i dont know how to setup the db to deal with this query.

Are you saying you don't have a database yet, such as MySQL or otherwise? Or you do have a database and access to it, but you need help creating the tables and understanding how to work with them?

[benihana]

yep i have access to sql, and have done basic things in the past, but i dont know the best way to setup the table for this part, and the AVG in the original query trhrew me a bit..
cheers

[coopster]

OK. The query your friend offered and what you are describing are for two different approaches to the same issue. That's why I was wondering how you were going to store your user-submitted information. Your friend is offering a query based on a table structure where one row would be one vote from each visit to the page. Anybody could come here and vote, come back and vote again, and again, each time adding a new row to your table. You would have many records for each article_id along with the weight of that particular vote. For example:

article_votes 
----------------- 
article_id...vote 
.........5......3 
.........5......2 
.........5......3 
.........5......5 
.........5......2 

If we ran the query offered...

SELECT AVG(vote) AS average, COUNT(vote) AS votescast WHERE article_id = 5; 

over this data, it would return...

average = 3 
votescast = 5 

Whereas, what you seem to be describing is a "summarized" version of these rows, stored in a single row in your table:

article_votes 
----------------- 
article_id...sum_of_votes...nbr_of_votes...avg_of_votes 
.........5.............15..............5..............3 

Make sense?

[benihana]

thats it! i didnt consider the possibility of having so many rows for each article. seems to me that one row per article id would be more efficient?

thanks for your help coopster - i think ill be able to move forward now.

ben

[coopster]

You're welcome. It's all about how you want to store and access the information. C'mon back to if you run into any obstacles.