Professional Web Applications Themes

Recording votes. - MySQL

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 ...

  1. #1

    Default Recording votes.

    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 Guest

  2. #2

    Default Re: Recording votes.


    com 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
     
    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 Guest

  3. #3

    Default Re: Recording votes.

    strawberry wrote:
     
    > 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').[/ref]

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

    guitarromantic@gmail.com Guest

  4. #4

    Default Re: Recording votes.

    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 Guest

  5. #5

    Default Re: Recording votes.


    com 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

    strawberry Guest

  6. #6

    Default Re: Recording votes.

    strawberry wrote:
     

    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

    guitarromantic@gmail.com Guest

Similar Threads

  1. recording in mp3
    By hymanand in forum Macromedia Flash Player
    Replies: 0
    Last Post: March 21st, 09:27 AM
  2. Eliminating dupe votes
    By bateman in forum ASP Database
    Replies: 1
    Last Post: January 5th, 01:40 PM
  3. cd recording
    By James LeClair in forum Debian
    Replies: 2
    Last Post: July 5th, 06:30 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139