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.
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.
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
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.
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.
…