[sovled] Complex database selection

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

[sovled] Complex database selection

Post by josh »

I'm writing a script to display one quiz item to a user at a time, the items will be displayed in a random order and the same item will not be displayed twice, I was wondering the best way to select a random question given that I have an array of questions that have already been selected, should I loop through the array building a huge WHERE clause? Would it be possible to have a table named `viewed` that has has a user id column, then do something like (dont even know if this is valid mySQL or not):

Code: Select all

SELECT FROM `questions` ORDER BY rand() WHERE `id` != (SELECT `id` FROM `viewed` WHERE `user` = '$user') LIMIT 1 ;
Are there any other options on the table? Thanks for your input.
Last edited by josh on Sun Aug 07, 2005 10:05 pm, edited 1 time in total.
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

If MySQL allows subqueries then:

Code: Select all

SELECT * FROM questions WHERE id NOT IN (SELECT id FROM viewed WHERE user='$user') ORDER BY rand() LIMIT 1;
If it doesn't support it, then

Code: Select all

SELECT * FROM questions WHERE id NOT IN (<comma seperated list of viewd>) ORDER BY rand() limit 1;
and you'll have to retrieve the CSL with a previous query or via session variables.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

Thank you for the thorough explanation, you answered all my questions.

EDIT: sorry for posting in the wrong forum, I figured it should have gone in theory since the original question I asked was "how do I do this, should I use PHP to build a CSL from an array, or use a subquery" (which is not a question about databases, but rather a question of design), since it was not "how do i write a subquery" I thought it belonged in there.

By the way, I used an inner join because my mysql didn't support sub queries.
Post Reply