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
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 */;
(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)
NOT IN (?)
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.
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