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