Tracking Votes and only allowing 1 vote per member

Question

What I'm trying to do is count the votes when someone votes on a "page". I think I lost myself trying to figure out how to track when a member votes or not. I can't seem to get the code to tell when a member has voted.

//Generate code ID
    $useXID = intval($_GET['id']);
    $useXrank = $_GET['rank'];

    //if($useXrank!=null && $useXID!=null) {
        $rankcheck = mysql_query('SELECT member_id,code_id FROM code_votes WHERE member_id="'.$_MEMBERINFO_ID.'" AND WHERE code_id="'.$useXID.'"');
            if(!mysql_fetch_array($rankcheck) && $useXrank=="up"){
                $rankset = mysql_query('SELECT * FROM code_votes WHERE member_id="'.$_MEMBERINFO_ID.'"');
                $ranksetfetch = mysql_fetch_array($rankset);
                $rankit = htmlentities($ranksetfetch['ranking']);
                $rankit+="1";
                mysql_query("INSERT INTO code_votes (member_id,code_id) VALUES ('$_MEMBERINFO_ID','$useXID')") or die(mysql_error());
                mysql_query("UPDATE code SET ranking = '".$rankit."' WHERE ID = '".$useXID."'");
            }
            elseif(!mysql_fetch_array($rankcheck) && $useXrank=="down"){
                $rankset = mysql_query('SELECT * FROM code_votes WHERE member_id="'.$_MEMBERINFO_ID.'"');
                $ranksetfetch = mysql_fetch_array($rankset);
                $rankit = htmlentities($ranksetfetch['ranking']);
                $rankit-="1";
                mysql_query("INSERT INTO code_votes (member_id,code_id) VALUES ('$_MEMBERINFO_ID','$useXID')") or die(mysql_error());
                mysql_query("UPDATE code SET ranking = '".$rankit."' WHERE ID = '".$useXID."'");
            }
            // hide vote links since already voted
            elseif(mysql_fetch_array($rankcheck)){$voted="true";}
    //}

The amount of duplicate code and *RAW SQL QUERIES* makes me want to cry. Take a step back from the SQL DQL (data-query language), and first describe the structures and relationships -- e.g. what are the tables, and how do they relate? E.g. what are the restrictions on the `code_votes` table? How does `code` fir into this? How are transactions/ACID handled? — , Jan 15 '11 at 04:33

score 1 · Accepted Answer · answered Jan 15 '11 at 21:08

You are making it too hard. Use a numeric value for the vote, +1,-1, and enforce a unique constraint on the table:

Expanded a bit:

create table votes (
  pageId    int references pages (pageId)
 ,memberId  int references members (memberId)
 ,value     int check (value in (-1,1))
 ,constraint votes_unique unique (pages,members)
)

Now you can "Select sum(value)..." to get what a user is up to, what's going on with a page, etc.

score 0 · Answer 2 · edited May 23 '17 at 12:11

0

something like this should work for you - just replace all things image with all things code.

PHP: Star rating system concept?

edited May 23 '17 at 12:11

Community

1
1

answered Jan 15 '11 at 02:48

Jon Black

16,223
5
43
42

score 0 · Answer 3 · answered Jan 15 '11 at 02:50

0

Your first query is invalid as it has two WHERE clauses.

answered Jan 15 '11 at 02:50

Spechal

2,634
6
32
48

Yea, I fixed that but still can't seem to get why it's not working. – MikeAdams Jan 15 '11 at 03:12

Tracking Votes and only allowing 1 vote per member

3 Answers3