Creating a next feature without adding fields to a table?
I have a polling web application, which has a bunch of polls. There are two tables, one with poll question's and answers, and one with people's votes on those polls. For each user I want to provide next functionality, so that when they press a next button it will take them to a new poll that they have not answered. I don't want to add anymore fields to the table, and I am not sure how to "save" the poll id's for a specific user which have not been answere开发者_开发知识库d. This has definitely been done many times before, What are solutions/design patterns you have used?
Get a random poll user hasn't answered yet:
SELECT p.poll_id FROM polls p WHERE p.poll_id NOT IN
(SELECT r.poll_id FROM poll_to_user r WHERE r.user_id = [current_user_id])
ORDER BY RAND() LIMIT 1;
You will just have to query for a poll id that you have no vote on from this user.
SELECT p.id
FROM polls p
WHERE p.id NOT IN (SELECT v.poll_id FROM votes v WHERE v.user_id = $this_user_id)
ORDER BY p.date_added
LIMIT 1;
or ORDER BY RAND() LIMIT 1
if you prefer to get one at random.
If your table structure for votes
does not store poll ID and user ID then there must be some other way to uniquely identify poll and user.
精彩评论