I am looking to create a function that gets me a random item from a mySQL table, but let's me keep the returned as the "item of the day". In other words, the item that was "the item of the day" yesterday should not be shown again until all other items have been shown as item of the day.
Any suggestions on how to do this in an elegant way?
Thanks
Add a bool column "UsedAsItemOfTheDay" set to false (0). Update to true when item is picked. Exclude already used items from the picking process.
SELECT * FROM `table`
WHERE UsedAsItemOfTheDay = 0
ORDER BY RAND() LIMIT 1;
(Note: this is not the fastest way to return a random row in MySql; it will be slow on huge tables)
See also: quick selection of a random row from a large table in mysql
SELECT <fields> FROM <table> WHERE <some logic to exclude already used> ORDER BY RAND() LIMIT 1 will get you a random row from the table.
Add a column to store whether the item has been used:
ALTER TABLE your_table ADD COLUMN isused BOOL DEFAULT 0;
Get a random item of the day:
SELECT t.*
FROM your_table t
WHERE t.isused = 0
ORDER BY RAND()
LIMIT 1
Now update that record so it can't be used in the future:
UPDATE your_table
SET isused = 1
WHERE id = id_from_select_random_statement
People who "know" SQL will look for declarative solutions and will shun procedural code. Flagging rows is a "smell" for procedural code.
Is the set of Items static (never changes) or stable (rarely changes)? If yes, it would be easier to do a one-off exercise of generating a lookup table of values from now until the end of time, rather than scheduling a proc to running daily to look for unused flags and update the flag for today and clear all flags if all have been used etc.
Create a table of sequential dates between today and a far future date representing the lifetime of your application (you could consider omitting non-business days, of course). Add a column(s) referencing the key in you Items table (ensure you opt for ON DELETE NO ACTION referential action just in case those Items prove not to be static!) Then randomly assign the whole set of Items one per day until each has been used once. Repeat again for the whole set of Items until the table is full. You could easily generate this data using a spreadsheet and import it (or pure SQL if you are hardcore ;)
Quick example using Standard SQL:
Say there are only five Items in the set:
CREATE TABLE Items
(
item_ID INTEGER NOT NULL UNIQUE
);
INSERT INTO Items (item_ID)
VALUES (1),
(2),
(3),
(4),
(5);
You lookup table would be as simple as this:
CREATE TABLE ItemsOfTheDay
(
cal_date DATE NOT NULL UNIQUE,
item_ID INTEGER NOT NULL
REFERENCES Items (item_ID)
ON DELETE NO ACTION
ON UPDATE CASCADE
);
Starting with today, add the whole set of Items in random order:
INSERT INTO Items (item_ID)
VALUES ('2010-07-13', 2),
('2010-07-14', 4),
('2010-07-15', 5),
('2010-07-16', 1),
('2010-07-17', 3);
Then, starting with the most recent unfilled date, add the whole set of Items in (hopefully a different) random order:
INSERT INTO Items (item_ID)
VALUES ('2010-07-18', 1),
('2010-07-19', 3),
('2010-07-20', 4),
('2010-07-21', 5),
('2010-07-22', 2);
...and again...
INSERT INTO Items (item_ID)
VALUES ('2010-07-23', 2),
('2010-07-24', 3),
('2010-07-25', 5),
('2010-07-26', 1),
('2010-07-27', 4);
..and so on until the table is full.
Then it would then simply be a case of looking up today's date in the lookup table as and when required.
If the set of Items changes then the lookup table would obviously need to be regenerated, so you need to balance out the simplicity of design against the need for manual maintenance.
If you have fixed items you can add column
ALTER TABLE your_table ADD COLUMN item_day INT DEFAULT 0;
then selecting item use
WHERE item_day = DATE_FORMAT('%j')
If you get empty result then you can format new list of day items:
<?php
$qry = " UPDATE your_table SET item_day = 0";
$db->execute($qry);
// You only need 355 item to set as item of the day
for($i = 0; $i < 355; $i++) {
$qry = "UPDATE your_table SET item_day = ".($i+1)." WHERE item_day = 0 ORDER BY RAND() LIMIT 1";
$rs = $db->execute($qry);
// If no items left stop update
if (!$rs) { break; }
}
?>
Here's a stored procedure which selects a random row without using ORDER BY RAND(), and which resets the used flag once all items have been used:
DELIMITER //
DROP PROCEDURE IF EXISTS random_iotd//
CREATE PROCEDURE random_iotd()
BEGIN
# Reset used flag if all the rows have been used.
SELECT COUNT(*) INTO @used FROM iotd WHERE used = 1;
SELECT COUNT(*) INTO @rows FROM iotd;
IF (@used = @rows) THEN
UPDATE iotd SET used = 0;
END IF;
# Select a random number between 1 and the number of unused rows.
SELECT FLOOR(RAND() * (@rows - @used)) INTO @rand;
# Select the id of the row at position @rand.
PREPARE stmt FROM 'SELECT id INTO @id FROM iotd WHERE used = 0 LIMIT ?,1';
EXECUTE stmt USING @rand;
# Select the row where id = @id.
PREPARE stmt FROM 'SELECT id, item FROM iotd WHERE id = ?';
EXECUTE stmt USING @id;
# Update the row where id = @id.
PREPARE stmt FROM 'UPDATE iotd SET used = 1 WHERE id = ?';
EXECUTE stmt USING @id;
DEALLOCATE PREPARE stmt;
END;
//
DELIMITER ;
To use:
CALL random_iotd();
The procedure assumes a table structure like this:
CREATE TABLE `iotd` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`item` varchar(255) NOT NULL,
`used` BOOLEAN NOT NULL DEFAULT 0,
INDEX `used` (`used`),
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
Here's one way to get the result from PHP (to keep things simple, error checking has been removed):
$mysqli = new mysqli('localhost', 'root', 'password', 'database');
$stmt = $mysqli->prepare('CALL random_iotd()');
$stmt->execute();
$stmt->bind_result($id, $item);
$stmt->fetch();
echo "$id, $item\n";
// 4, Item 4
UPADATE
This version should return the same result repeatedly on a given date. I've not really had time to test this, so be sure to do some testing of your own...
DELIMITER //
DROP PROCEDURE IF EXISTS random_iotd//
CREATE PROCEDURE random_iotd()
BEGIN
# Get today's item.
SET @id := NULL;
SELECT id INTO @id FROM iotd WHERE ts = CURRENT_DATE();
IF ISNULL(@id) THEN
# Reset used flag if all the rows have been used.
SELECT COUNT(*) INTO @used FROM iotd WHERE used = 1;
SELECT COUNT(*) INTO @rows FROM iotd;
IF (@used = @rows) THEN
UPDATE iotd SET used = 0;
END IF;
# Select a random number between 1 and the number of unused rows.
SELECT FLOOR(RAND() * (@rows - @used)) INTO @rand;
# Select the id of the row at position @rand.
PREPARE stmt FROM 'SELECT id INTO @id FROM iotd WHERE used = 0 LIMIT ?,1';
EXECUTE stmt USING @rand;
# Update the row where id = @id.
PREPARE stmt FROM 'UPDATE iotd SET used = 1, ts = CURRENT_DATE() WHERE id = ?';
EXECUTE stmt USING @id;
END IF;
# Select the row where id = @id.
PREPARE stmt FROM 'SELECT id, item FROM iotd WHERE id = ?';
EXECUTE stmt USING @id;
DEALLOCATE PREPARE stmt;
END;
//
DELIMITER ;
And the table structure:
CREATE TABLE `iotd` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`item` varchar(255) NOT NULL,
`used` BOOLEAN NOT NULL DEFAULT 0,
`ts` DATE DEFAULT 0,
INDEX `used` (`used`),
INDEX `ts` (`ts`),
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
Why don't you use sequence?
Sequence serves your purpose easily...
