- This topic is empty.
-
AuthorPosts
-
March 8, 2014 at 1:58 pm #165158
Josh Johnson
ParticipantSo 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!
March 8, 2014 at 4:19 pm #165159__
ParticipantI 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?
March 9, 2014 at 4:25 am #165177Josh Johnson
Participant@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?
March 9, 2014 at 11:53 am #165192__
ParticipantBut 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.
March 9, 2014 at 6:37 pm #165221__
ParticipantHere’s the SQL I had in mind…
Cool, that works too. I couldn’t figure out how to do it without using a subquery.
March 10, 2014 at 6:44 pm #165332__
ParticipantThat 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.
March 12, 2014 at 5:24 am #165515Josh Johnson
Participant@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?
March 12, 2014 at 3:59 pm #165583Josh Johnson
Participant@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_typefreelancers
freelancer_id (PK)
user_id (FK)
jobtitle
priceperhouruser_votes
vote_id (PK)
user_id (FK)
voted_by_id (FK)
votesMarch 12, 2014 at 6:31 pm #165601__
ParticipantWhat 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 thevote_by_id
to identify the vote.What is the
votes
column for?March 13, 2014 at 2:27 am #165622Josh Johnson
Participant@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 TABLEusers
(
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_cuser_votes
table
CREATE TABLEuser_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 KEYvote_id
(vote_id
),
KEYuser_id
(user_id
),
KEYvoted_for_id
(voted_by_id
),
KEYvoted_by_id
(voted_by_id
),
CONSTRAINTuser_votes_ibfk_1
FOREIGN KEY (user_id
) REFERENCESusers
(user_id
),
CONSTRAINTuser_votes_ibfk_2
FOREIGN KEY (voted_by_id
) REFERENCESusers
(user_id
)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_cifreelancers
table
CREATE TABLEfreelancers
(
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
),
KEYuser_id
(user_id
),
CONSTRAINTfreelancers_ibfk_1
FOREIGN KEY (user_id
) REFERENCESusers
(user_id
)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ciMarch 13, 2014 at 11:21 am #165684__
ParticipantYeah… 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).March 20, 2014 at 6:08 am #166336Josh Johnson
Participant@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?March 20, 2014 at 12:20 pm #166401__
ParticipantI’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 regularjoin
. 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.”March 20, 2014 at 1:03 pm #166415Josh Johnson
Participant@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.March 20, 2014 at 1:18 pm #166423__
Participant> 10
should be
< 10
.Also, I don’t see where you counted/grouped the votes in your query above…? Did you add that too?
-
AuthorPosts
- The forum ‘Back End’ is closed to new topics and replies.