SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Addict
    Join Date
    Jul 2007
    Posts
    233
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select random items with specific category preferences?

    Hello,

    I have a site where members can sell items and place them in categories and sub-categories.

    When a buyer views an item page, I would like to display 10 random items being sold by the same member. So far, everything is working fine:

    PHP Code:
    $sql "SELECT * FROM items WHERE seller = '$seller' ORDER BY RAND() LIMIT 10"
    Question:
    I would like to show similar items related to the category that the item page is listed in, rather than totally random, un-related items.

    For example, here are some categories:

    Collectibles > Bottles
    Collectibles > Coins/Banknotes > Coins
    Collectibles > Coins/Banknotes > Banknotes
    Collectibles > Comic Books
    Movies > DVDs
    Movies > VHS
    etc...

    Let's say this member has thousands of items listed in all of these categories. When a buyer looks at an item listed in: "Collectibles > Coins/Banknotes > Coins" , I would like to show random "Coins" items from this seller. If he has less than 10 coins for sale, I would like to then show random "Coins/Banknotes" items. If there are still less than 10 items, show random "Collectibles". Basically the randomness starts specifically and then grows more general.

    I hope this description is understandable. Please let me know how this could be done.

    Thanks

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    How are you establishing the relationship between categories and their parents/children in the database?
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  3. #3
    SitePoint Addict
    Join Date
    Jul 2007
    Posts
    233
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In my items table, I have columns for the categories with names like:
    cat1, cat2, cat3

    cat1 is the main category such as "Collectibles". cat2 is a sub-category such as "Coins/Banknotes". cat3 is a sub-sub-category such as "Coins".
    Collectibles > Coins/Banknotes > Coins

    Note that these columns in the items table stores the id numbers referencing category row in a separate categories table.

  4. #4
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    Hrm. Not the most common model of mapping, but we'll roll with it for the moment.

    This is going to be easier to do in a mix of PHP and SQL i believe... unless the guys have some tricks up their sleaves... *pauses, thinks about that*

    Okay... tricksy time. Completely untested and probably can be done better! (I really dislike ORDER BY RAND(), btw)

    Code MySQL:
    SELECT *, 
    CASE 
    WHEN col3 = $thiscol3 THEN 3000 
    WHEN col2 = $thiscol2 THEN 2000 
    WHEN col1 = $thiscol1 THEN 1000 
    ELSE 0 
    END AS weight 
    FROM items 
    WHERE seller = '$seller' 
    ORDER BY (RAND()*999)+weight DESC
    LIMIT 10;

    EDIT: Wait.. no, not null doesnt work.. gotta be equals. *fixes*
    EDIT2: Wow. Brain not fully engaged this morning. Fixed it more, and put the order in the right direction
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  5. #5
    SitePoint Addict
    Join Date
    Jul 2007
    Posts
    233
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks StarLion, this works great and is VERY COOL! I love learning such great information here at SitePoint.

    The SQL works perfect and I got the PHP to output similar items very nicely.

    Thanks for the help!
    Kind regards

  6. #6
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    65 Post(s)
    Tagged
    2 Thread(s)
    Now about your table structure ...

  7. #7
    SitePoint Addict
    Join Date
    Jul 2007
    Posts
    233
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What's wrong with the table structure?

  8. #8
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    65 Post(s)
    Tagged
    2 Thread(s)
    You generally want to use a "parentId" column and then a PHP recursion to create a tree such as this. It enables you to have an infinite amount of levels as well as becomes easier to look at / maintain.

  9. #9
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,810
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by StarLion View Post
    I really dislike ORDER BY RAND(), btw
    I did some testing to compare ORDER BY RAND() with the alternative of counting how many entries meet the criteria in one query and then retrieving using a random number between 1 and that count to retrieve the record at that specific location within the results. For retrieving just one result where less than about 700 entries satisfy the condition the ORDER BY RAND is faster while with 1000 or more entries the other way is faster. As you want to retrieve more results the ORDER BY RAND() approach is the more efficient way for larger numbers in the results. For example if you are retrieving 30 random results then ORDER BY RAND() was more efficient if the condition is met by less than about 50,000 entries.

    With the OP's situation where 10 results are to be returned at random using ORDER BY RAND() would probably be more efficient as long as there are less than say 6,000 entries to select those 10 from. As few sellers would be selling that many items this would make ORDER BY RAND() the more efficient alternative to use for this particular situation.

    here's an outline of the code for the alternative that I tested that doesn't use the ORDER BY RAND() for retrieving one random result:

    Code:
    SELECT COUNT(*) FROM tablename WHERE condition
    
    $limit = rand(1, $myrow[0]);
    
    SELECT * FROM tablename WHERE condition LIMIT 1 OFFSET $limit
    For retrieving more than one the second line would be run as many times as necessary loading the results into an array and dropping any duplicates and then the third line would be run for each entry in the array.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">


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
  •