MySQL Question
How to pick eight random items, each one conforming to a specific condition, in MySQL?
I have a table of banners. Banners are marked with size id and slot id (position on page).
I need to pick 8 banners of one size, 1 for each slot between 11-18(slot id) and keep randomness each page view, so that each time banners were different if there any different for required slot.
The only solution I have found is this:
SELECT *
FROM banners
WHERE slot IS NOT NULL AND slot > 10 AND slot < 19
AND NOT IN (?)
GROUP BY slot
LIMIT 8
Keep the trace of pulled banners id and use them in next query.
But the problem is to calculate if there are 8 banners(maybe there is more banners for some slot id that in others). When to stop gathering ids for "NOT IN" and reset rotation.
Is there another solution to keep good random rotation of banners in this case?
Test table:
CREATE DATABASE IF NOT EXISTS `test123` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `test123`;
CREATE TABLE IF NOT EXISTS `test` (
`banner_id` int(11) NOT NULL DEFAULT '0',
`slot` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40000 ALTER TABLE `test` DISABLE KEYS */;
REPLACE INTO `test` (`banner_id`, `slot`) VALUES
(284, 11),
(283, 12),
(282, 13),
(280, 14),
(281, 14),
(278, 15),
(279, 15),
(277, 16),
(276, 17),
(274, 18),
(275, 18);
/*!40000 ALTER TABLE `test` ENABLE KEYS */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
Solution from Lieven:
(SELECT * FROM test WHERE slot = 11 ORDER BY RAND() LIMIT 1)
UNION ALL (SELECT * FROM test WHERE slot = 12 ORDER BY RAND() LIMIT 1)
UNION ALL (SELECT * FROM test WHERE slot = 13 ORDER BY RAND() LIMIT 1)
UNION ALL (SELECT * FROM test WHERE slot = 14 ORDER BY RAND() LIMIT 1)
UNION ALL (SELECT * FROM test WHERE slot = 15 ORDER BY RAND() LIMIT 1)
UNION ALL (SELECT * FROM test WHERE slot = 16 ORDER BY RAND() LIMIT 1)
UNION ALL (SELECT * FROM test WHERE slot = 17 ORDER BY RAND() LIMIT 1)
UNION ALL (SELECT * FROM test WHERE slot = 18 ORDER BY RAND() LIMIT 1)
What if I'll pick ids of all banners for slot 11 - 18 and set a flag in a session, so it wouldn't fetch all ids all the time. Then pick random ids via PHP and get banners for those ids, then remove those ids from session array and when array if empty, I'm repeating all steps?
This will produce 1 query per session and 1 query for picking banners itself.
Of course this is not a solution for big tables with thousands of banners per 1 slot.
Or limit first query by 32 and then use all 32 ids in next query inside
NOT IN (?)Any better solutions? :)
Answer Source
You can make the order of returned results random by using MySQL's RAND() function.
How good the rotation would be depends on the implementation of randomization in MySQL.
I have no experience whatsoever with that.
edit
I think I finally understand the requirement. Unfortunately, I have no idea if following query works in MySQL.
SELECT * FROM Banners WHERE Slot = 11 ORDER BY RAND() LIMIT 1
UNION ALL SELECT * FROM Banners WHERE Slot = 12 ORDER BY RAND() LIMIT 1
UNION ALL SELECT * FROM Banners WHERE Slot = 13 ORDER BY RAND() LIMIT 1
UNION ALL SELECT * FROM Banners WHERE Slot = 14 ORDER BY RAND() LIMIT 1
UNION ALL SELECT * FROM Banners WHERE Slot = 15 ORDER BY RAND() LIMIT 1
UNION ALL SELECT * FROM Banners WHERE Slot = 16 ORDER BY RAND() LIMIT 1
UNION ALL SELECT * FROM Banners WHERE Slot = 17 ORDER BY RAND() LIMIT 1
UNION ALL SELECT * FROM Banners WHERE Slot = 18 ORDER BY RAND() LIMIT 1