User voting system problem

[Josh Johnson]

So for users to gain privileges on a PHP application I am working on, they have to achieve 10 votes from existing users: http://imgur.com/bdbxnzE

At the moment I have a “votes” column in the users table which, by default, is 0 and if an existing user adds a vote, the column is updated by 1. Although this works, what I am really after is having the functionality so an existing user can only vote once, and if they have already voted for a user, the vote up button becomes disabled (to stop the user voting for someone twice).

I am gathering that to achieve this I am going to need a “votes” database table which holds the user id of the user getting a vote, the user id of the user voting for the said user as well as the number of votes (?). But I’m not really how this would work because a user with no votes would not have an entry in the “votes” table?

Help!

[__]

I am gathering that to achieve this I am going to need a “votes” database table which holds the user id of the user getting a vote, the user id of the user voting for the said user as well as the number of votes (?).

You don’t need a “number of votes” column, because that can be determined from the other two columns. The only possible use for it would be to make mistakes.

But I’m not really how this would work because a user with no votes would not have an entry in the “votes” table?

Right…

So if you count how many such entries there are, you’d get 0.

Right?

[Josh Johnson]

@traq But say I wanted to query the database for all users with <10 votes, surely that wouldn’t work or is that just me being stupid?

[__]

But say I wanted to query the database for all users with <10 votes, surely that wouldn’t work or is that just me being stupid?

I don’t know exactly how your tables are/will be designed, but here’s a possible solution.

For more specific advice, please share your DB schema.

[__]

Here’s the SQL I had in mind…

Cool, that works too. I couldn’t figure out how to do it without using a subquery.

[__]

That alias UserName was calculated control using concat. function to get first and last name of the user cause normally the name should be broken up to first and last.

Yes, I saw (and agree). I only changed it to work with the (simplistic) example I already had. @JoshJohnson, if you need help adapting this to your DB schema, please let us know.

Specifying INNER JOIN wasn’t probably necessary if just JOIN defaults to INNER, but it’s a good habit to have.

Indeed, especially if you ever work on more than one type of database.

[Josh Johnson]

@jurotek Thanks. I’m still not sure the SQL you provided would solve the issue of a user with 0 votes. If a user has no votes, there is no entry in the votes table therefore ON users.user_id = user_votes.user_id would fail surely?

What would be the easiest way to share my DB schema?

[Josh Johnson]

@jurotek, @traq
Okay so here is my DB schema to make things a bit more clearer. When a user signs up, the correct user type is set. If that user type is freelancer, their job title and price per hour is inserted into the freelancers table.

What I ideally need is an SQL statement that will grab the first name, last name, location, experience from the users table, job title and price per hour from the freelancers table only if the user has < 10 votes (including users with 0 votes and therefore no entries in the user_votes table.

users
user_id (PK)
firstname
lastname
email
email_code
time_joined
location
password
experience
bio
confirmed
generated_string
ip
user_type

freelancers
freelancer_id (PK)
user_id (FK)
jobtitle
priceperhour

user_votes
vote_id (PK)
user_id (FK)
voted_by_id (FK)
votes

[__]

What would be the easiest way to share my DB schema?

I would actually suggest using show create table your_table_name_here;, which will include all relevant information. The column types, for example, are important.

user_votes
vote_id (PK)
user_id (FK)
voted_by_id (FK)
votes

Didn’t you say earlier that each user gets only one vote? If that is the case, then you don’t need a vote_id: you can use the vote_by_id to identify the vote.

What is the votes column for?

[Josh Johnson]

@traq The votes column is mainly for testing whether a user has voted. If a user tries to delete a vote and the field is set to 1, the entry will be removed. I suppose I could do that without it though!

For users table then:
CREATE TABLE users (
user_id int(11) NOT NULL AUTO_INCREMENT,
firstname varchar(100) COLLATE utf8_unicode_ci NOT NULL,
lastname varchar(100) COLLATE utf8_unicode_ci NOT NULL,
email varchar(100) COLLATE utf8_unicode_ci NOT NULL,
email_code varchar(100) COLLATE utf8_unicode_ci NOT NULL,
time_joined int(11) NOT NULL,
location varchar(100) COLLATE utf8_unicode_ci NOT NULL,
password varchar(100) COLLATE utf8_unicode_ci NOT NULL,
portfolio varchar(100) COLLATE utf8_unicode_ci NOT NULL,
experience varchar(100) COLLATE utf8_unicode_ci NOT NULL,
bio varchar(1000) COLLATE utf8_unicode_ci NOT NULL,
confirmed int(11) NOT NULL,
generated_string varchar(35) COLLATE utf8_unicode_ci NOT NULL,
ip varchar(32) COLLATE utf8_unicode_ci NOT NULL,
user_type enum(‘designer’,’developer’,’employer’) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (user_id)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_c

user_votes table
CREATE TABLE user_votes (
vote_id int(11) NOT NULL AUTO_INCREMENT,
user_id int(11) NOT NULL,
voted_by_id int(11) NOT NULL,
votes tinyint(1) NOT NULL,
PRIMARY KEY (vote_id),
UNIQUE KEY vote_id (vote_id),
KEY user_id (user_id),
KEY voted_for_id (voted_by_id),
KEY voted_by_id (voted_by_id),
CONSTRAINT user_votes_ibfk_1 FOREIGN KEY (user_id) REFERENCES users (user_id),
CONSTRAINT user_votes_ibfk_2 FOREIGN KEY (voted_by_id) REFERENCES users (user_id)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

freelancers table
CREATE TABLE freelancers (
freelancer_id int(11) NOT NULL AUTO_INCREMENT,
user_id int(11) NOT NULL,
jobtitle varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
priceperhour int(11) NOT NULL,
PRIMARY KEY (freelancer_id),
KEY user_id (user_id),
CONSTRAINT freelancers_ibfk_1 FOREIGN KEY (user_id) REFERENCES users (user_id)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

[__]

Yeah… the user_votes table is a mess. Too many indexes, as well.

Here’s what I understand about your goals:

• users can vote for any other user.
• users can vote only once.
• users can remove their vote (though I’m not clear whether this would allow them to vote again?)

create table user_vote(
  user_id int(11) not null,
  vote_id int(11),
  primary key(user_id), 
  -- key(vote_id),
  foreign key(user_id) references users(user_id),
  foreign key(vote_id) references users(user_id)
)engine=innodb;

user_id is the user voting; vote_id is the user voted for.

The primary key ensures there will be no more than one vote per user.

The foreign keys enforce that both the voter and the votee are valid users.

If you plan to search for records by the user voted for (likely, I think), uncomment the key(vote_id) line.

You know if a user has not yet voted simply because they will have no entry in the table.

When a user removes their vote, you have two options: if they are allowed to vote again, just delete the record; if not, set the vote_id to NULL (so the vote still exists, but counts for no one).

[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?

[__]

I’m still not really sure how I test for users with less than 10 votes or no votes at all though?

I think @jurotek pointed this out earlier, but you’d simply use left join instead of a regular join. This way, you get all results from the user table that don’t have a matching vote.

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.

Which points out how you can manage errors: if you get an SQL error, check if the error code was 23000. If so, you can display a message to the user along the lines of “You’ve already voted.”

[Josh Johnson]

@traq The voting twice is all sorted now, I change the “add vote” button to “remove vote” once a user has voted.

To try and get users with less than 10 votes I have added this to the end of the SQL above (which uses the left join you mentioned): HAVING CountOfvote_id > 10 – this returns no results though.

[__]

> 10

should be < 10.

Also, I don’t see where you counted/grouped the votes in your query above…? Did you add that too?

[Author]

Posts