Reply To: User voting system problem

[Josh Johnson]

@jurotek, @traq Hey! Okay so I took both of your advice. My user voting table now looks like this:

+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| vote_id     | int(11) | NO   | PRI | 0       |       |
| voted_by_id | int(11) | NO   | MUL | NULL    |       |
+-------------+---------+------+-----+---------+-------+

`

And I am using the following function for grabbing trial users:

public function getTrialUsers() {

            $query = $this->db->prepare("
                    SELECT 
                    user_types.user_type,
                    users.user_id,
                    users.firstname, 
                    users.lastname, 
                    users.location, 
                    user_experience.experience,
                    users.portfolio,
                    Count(user_votes.voted_by_id) 
                    AS CountOfvote_id, 
                    freelancers.jobtitle, 
                    freelancers.priceperhour
                    FROM users 
                    AS voters
                    RIGHT JOIN 
                    ((((users LEFT JOIN user_votes 
                    ON users.user_id = user_votes.vote_id) 
                    LEFT JOIN freelancers 
                    ON users.user_id = freelancers.freelancer_id) 
                    LEFT JOIN user_experience
                    ON users.user_id = user_experience.experience_id) 
                    LEFT JOIN user_types
                    ON users.user_id = user_types.user_type_id) 
                    ON voters.user_id = user_votes.vote_id
                    WHERE user_types.user_type != ?
                    GROUP BY
                    users.user_id,
                    users.firstname, 
                    users.lastname, 
                    users.location,
                    user_experience.experience,
                    users.portfolio,
                    freelancers.jobtitle, 
                    freelancers.priceperhour
            ");
            $query->bindValue(1, 'employer');
            try{
                $query->execute();
            }catch(PDOException $e){
                die($e->getMessage());
            }
            # We use fetchAll() instead of fetch() to get an array of all the selected records.
            return $query->fetchAll();
        }

At the moment, this grabs the all the relevant data for users. I then use the following function to add a vote:

public function addVote($user_id, $votedBy) {
            $query = $this->db->prepare("INSERT INTO " . DB_NAME . ".user_votes (vote_id, voted_by_id) VALUES (?, ?)");

            $query->bindValue(1, $user_id);
            $query->bindValue(2, $votedBy);

            try{
                $query->execute();
                $rows = $query->rowCount();
                header("Location:" . BASE_URL . "trials?success");
            }catch(PDOException $e){
                die($e->getMessage());
            }   
        }

So in terms of what actually happens. I can add a vote once which works fine and if I try to vote again I get a SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2' for key 'PRIMARY'error. I’m still not really sure how I test for users with less than 10 votes or no votes at all though?