Select random items with specific category preferences?

[peppy]

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:

$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

[StarLion]

How are you establishing the relationship between categories and their parents/children in the database?

[peppy]

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.

[StarLion]

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)

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

[peppy]

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

[K. Wolfe]

Now about your table structure ...

[peppy]

What's wrong with the table structure?

[K. Wolfe]

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.

[felgall]

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:

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.