SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Thread: Exclusion List

  1. #1
    SitePoint Member
    Join Date
    Jul 2004
    Location
    My Desk
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclusion List

    In the end, I'm looking to create a vocabulary quiz program, a sort of masochist's digital flash card.

    Right now (this part works), the program randomly selects a vocab word from the database by its id. I can't seem to figure out how to have the program only select "cards" that aren't excluded. In each user's table, there is a field called vocab_exclude. The data in that field could be, for example: "5231,5233,5234" without the quotes. I planned to just explode this array, and if a "card" with an id on the list is selected, the script will go back and select another random card. I don't know much about arrays, and the methods that I tried didn't work. Maybe it would be possible to not randomly grab a "card" with an excluded id in the first place. I don't really know. That's why I'm here.

  2. #2
    SitePoint Guru
    Join Date
    Jul 2004
    Location
    Raleigh, NC
    Posts
    783
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    your method will be very time consuming the longer it runs. eventually you're do not choose list will be almost everything and it will take a long time to find a valid one. instead, start with a temp duplicate list of IDs, either in a db or in an array. pull at random from this duplicate list, not the master list, and remove items after they're used

    for db: delete the row
    for array: unset($array['$id']);

  3. #3
    SitePoint Member
    Join Date
    Jul 2004
    Location
    My Desk
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Unfortunately, that won't be possible because the number of rows in the voacb table will always be changing.

    Is there another option?

  4. #4
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    Parry Sound, ON
    Posts
    725
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Set up a separate match table with nothing but user_id and excluded vocab_id pairs. When selecting from the vocab list, left join it to this match table and test that vocab_exclude is NULL. i.e.

    SELECT * FROM vocab
    LEFT JOIN user_vocab
    ON vocab_id = vocab_exclude
    AND user_id = '$user_id'
    WHERE vocab_exclude IS NULL
    ORDER BY RAND()
    LIMIT 1

  5. #5
    SitePoint Wizard Lats's Avatar
    Join Date
    Jun 2003
    Location
    Melbourne, AU
    Posts
    1,142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As you're already building/storing your exclude list, grab that field and use it in your query...
    Code:
    SELECT 
    	fieldx, fieldy,... 
    FROM 
    	tablename
    WHERE 
    	card_id NOT IN 
    			($not_in)
    I use this method to exclude text values from a general ledger with the resulting sql statment looking like this...
    Code:
    AND reason NOT IN ('Adv Pay','ALFA','ANN 1/2Pay','AnnLve Red','AnnLve Ter','AnnLveTkn'...) AND...
    works well.
    Lats...

  6. #6
    SitePoint Member
    Join Date
    Jul 2004
    Location
    My Desk
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Lats
    SELECT
    fieldx, fieldy,...
    FROM
    tablename
    WHERE
    card_id NOT IN
    ($not_in)
    They're in two different tables...

    I'm going to go try to look this up.

  7. #7
    SitePoint Member
    Join Date
    Jul 2004
    Location
    My Desk
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So far I've had no luck. Suggestions?

  8. #8
    SitePoint Wizard Lats's Avatar
    Join Date
    Jun 2003
    Location
    Melbourne, AU
    Posts
    1,142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Post your tables, and some sample data - wel'll go from there.
    Lats...


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •