SEO

Hey everyone,

My music site does a yearly "top 30 records" list. In previous years,
each dozen or so writers post a list numbered 1-30 of their top
records. Each one is assigned a score (eg, their #1 gets 30 points, #2
gets 29, etc) and we add up the overall scores for all records
nominated and get a top 30 for the whole site.

This obviously takes some time (especially when factoring in the actual
web design terms, such as formatting the lists properly and linking
them to our reviews of the records), so this year I decided to use a
combo of mysql and php to handle it. Here's my process:

Create a "nominations" table containing the record/band name and an ID
number as a foreign key linking it to the 'id' column of our "reviews"
table. I would just use the reviews table for this but there are some
records that we didn't review that people want on their list.

Create a 'vote.php' page that presents 30 dropdown boxes populated by
the contents of "nominations". Staff select their records and submit
the results:

A second table, "votes", contains 31 columns (a column for 'staff_id'
and a column for each rank of the vote). A sample row from that table:

author id | 1 | 2 | 3 etc
16 | 444 | 555 | 666

Now, my problem is exactly how to total up the scores from this
database schema. I thought about just adding a 'score' column to
"nominations" and when the vote.php page is submitted, it just adds the
relevant number onto the score. However, I _know_ my staff and
inevitably, someone will want to change their ordering before it goes
live in a month, so I need to have the ability to dynamically
recalculate rather than have to mess around subtracting totals here and
there.

Also, if I record each writer's votes, I can work out some more
interesting stats beyond just "overall top 30" (stuff like "most/least
nominated record" etc), which is always good.

I've tried to figure out exactly how to extract the overall scores but
it's beyond me - can anyone give me advice? Basically, I need to look
up a record id (say 444), log all the 'positions' it reaches and then
add up all the points. Does this make sense, and is it feasible?

Matt

guitarromantic@gmail.com wrote:
> Hey everyone,
>
> My music site does a yearly "top 30 records" list. In previous years,
> each dozen or so writers post a list numbered 1-30 of their top
> records. Each one is assigned a score (eg, their #1 gets 30 points, #2
> gets 29, etc) and we add up the overall scores for all records
> nominated and get a top 30 for the whole site.
>
> This obviously takes some time (especially when factoring in the actual
> web design terms, such as formatting the lists properly and linking
> them to our reviews of the records), so this year I decided to use a
> combo of mysql and php to handle it. Here's my process:
>
> Create a "nominations" table containing the record/band name and an ID
> number as a foreign key linking it to the 'id' column of our "reviews"
> table. I would just use the reviews table for this but there are some
> records that we didn't review that people want on their list.
>
> Create a 'vote.php' page that presents 30 dropdown boxes populated by
> the contents of "nominations". Staff select their records and submit
> the results:
>
> A second table, "votes", contains 31 columns (a column for 'staff_id'
> and a column for each rank of the vote). A sample row from that table:
>
> author id | 1 | 2 | 3 etc
> 16 | 444 | 555 | 666
>
> Now, my problem is exactly how to total up the scores from this
> database schema. I thought about just adding a 'score' column to
> "nominations" and when the vote.php page is submitted, it just adds the
> relevant number onto the score. However, I _know_ my staff and
> inevitably, someone will want to change their ordering before it goes
> live in a month, so I need to have the ability to dynamically
> recalculate rather than have to mess around subtracting totals here and
> there.
>
> Also, if I record each writer's votes, I can work out some more
> interesting stats beyond just "overall top 30" (stuff like "most/least
> nominated record" etc), which is always good.
>
> I've tried to figure out exactly how to extract the overall scores but
> it's beyond me - can anyone give me advice? Basically, I need to look
> up a record id (say 444), log all the 'positions' it reaches and then
> add up all the points. Does this make sense, and is it feasible?
>
> Matt

No, and no.

I think I'd probably structure the database something like this
(simplified obviously):

voters(voter_id*,firstname)
1 'Guy'
2 'Gwyneth'

records(record_id*,title)
1 'Like a Virgin'
2 'Hung Up'
3 'Clocks'
4 'Yellow'

votes(voter_id*,record_id*,points)
1, 1, 3
1, 2, 2
1, 3, 1
2, 3, 3
2, 4, 2
2, 1, 1

* = Primary Key

>From this it should be easy to see which songs Guy and Gwyneth like
best, which song is liked best overall (in this case a tie between
'Like a Virgin' and 'Clocks') and which song is nominated least often
(a tie between 'Hung up' & 'Yellow').

strawberry wrote:

> No, and no.
>
> I think I'd probably structure the database something like this
> (simplified obviously):
>
> voters(voter_id*,firstname)
> 1 'Guy'
> 2 'Gwyneth'
>
> records(record_id*,title)
> 1 'Like a Virgin'
> 2 'Hung Up'
> 3 'Clocks'
> 4 'Yellow'
>
> votes(voter_id*,record_id*,points)
> 1, 1, 3
> 1, 2, 2
> 1, 3, 1
> 2, 3, 3
> 2, 4, 2
> 2, 1, 1
>
> * = Primary Key
>
> >From this it should be easy to see which songs Guy and Gwyneth like
> best, which song is liked best overall (in this case a tie between
> 'Like a Virgin' and 'Clocks') and which song is nominated least often
> (a tie between 'Hung up' & 'Yellow').

Thank you, this is the solution that should've been obvious to me. I'm
on it!

Just an update:

I've been playing with this and wrote a php script to insert the values
as outlined above. My problem now is getting the scores back.

I have some messy php that currently totals up the scores and returns
them but it's not perfect and I can't figure out how to order it by
score. Here's my code:

-----------

$mainquery = "SELECT id FROM record_choices"; // grab all record ids
$mainresult = mysql_query($mainquery) or die('Error : ' .
mysql_error());

// populate $_data array from main query
while($row = mysql_fetch_array($mainresult, MYSQL_ASSOC))
{
$_data[] = $row;
}

echo "<ol>";

foreach($_data as $data) { //loop through all the ids


$query = "SELECT SUM(points) points FROM year_end_votes WHERE
record_id = '".$data['id']."' LIMIT 30"; //add up all scores for all
occurences of id
$result = mysql_query($query) or die('Error : ' . mysql_error());
if($result && mysql_num_rows($result) == 1) {
$row = mysql_fetch_assoc($result);
$all_points = $row['points'];
echo "<li>Score for id # ". $data['id'] ." =
<b>{$all_points}</b></li>"; //print total score for record id
}

}
echo "</ol>";

-----------------

Any better solutions/fixes for this?

guitarromantic@gmail.com wrote:
> Just an update:
>
> I've been playing with this and wrote a php script to insert the values
> as outlined above. My problem now is getting the scores back.
>
> I have some messy php that currently totals up the scores and returns
> them but it's not perfect and I can't figure out how to order it by
> score. Here's my code:
>
> -----------
>
> $mainquery = "SELECT id FROM record_choices"; // grab all record ids
> $mainresult = mysql_query($mainquery) or die('Error : ' .
> mysql_error());
>
> // populate $_data array from main query
> while($row = mysql_fetch_array($mainresult, MYSQL_ASSOC))
> {
> $_data[] = $row;
> }
>
> echo "<ol>";
>
> foreach($_data as $data) { //loop through all the ids
>
>
> $query = "SELECT SUM(points) points FROM year_end_votes WHERE
> record_id = '".$data['id']."' LIMIT 30"; //add up all scores for all
> occurences of id
> $result = mysql_query($query) or die('Error : ' . mysql_error());
> if($result && mysql_num_rows($result) == 1) {
> $row = mysql_fetch_assoc($result);
> $all_points = $row['points'];
> echo "<li>Score for id # ". $data['id'] ." =
> <b>{$all_points}</b></li>"; //print total score for record id
> }
>
> }
> echo "</ol>";
>
> -----------------
>
> Any better solutions/fixes for this?

Well, in this instance you're using two queries where one will do!

Maybe consider using a query like this:

SELECT record_id,SUM(points) total_points FROM year_end_votes
GROUP BY record_id
ORDER BY total_points

Note that this query only returns the score for records that have one!
If you really want to list all records - regardless of whether they
have a score or not - then you'll need a join (untested):

SELECT rc.id, SUM(yev.points) total_points FROM record_choices rc
LEFT JOIN year_end_votes yev ON yev.record_id = rc.id
GROUP BY rc.id
ORDER BY yev.total_points

strawberry wrote:

>
> Well, in this instance you're using two queries where one will do!
>
> Maybe consider using a query like this:
>
> SELECT record_id,SUM(points) total_points FROM year_end_votes
> GROUP BY record_id
> ORDER BY total_points
>
> Note that this query only returns the score for records that have one!
> If you really want to list all records - regardless of whether they
> have a score or not - then you'll need a join (untested):
>
> SELECT rc.id, SUM(yev.points) total_points FROM record_choices rc
> LEFT JOIN year_end_votes yev ON yev.record_id = rc.id
> GROUP BY rc.id
> ORDER BY yev.total_points

Sir, you are an absolute prince. This works (slightly modified the last
join query, basically needed to be ORDER BY total_points DESC) and will
save me tons of manual labour (last year I had to add each one by hand,
took 6 hours, not to mention producing HTML formatted output manually).
Thanks so much!

Matt