views:

102

answers:

7

Hi,

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

+4  A: 

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

Mitch Wheat
Agreed, ORDER BY RAND() doesn't scale well when there 100,000+ records involved. Below that is fine.
OMG Ponies
A: 

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.

Will A
For small tables, this is okay. This will be very slow on huge tables.
Konerak
Good point - should be good for several years worth of items-of-the-day though.
Will A
+1  A: 

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
OMG Ponies
ORDER BY RAND() does not scale for tables with more than 100,000 records. Below that is fine.
OMG Ponies
A: 

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; }
}

?>

Liutas
A: 

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;
Mike
This sure looks promising! However, I never really worked with stored procedures. I just pasted you code into my the phpMyAdmin SQL input field, which went well, but I cannot seem to find the stored procedure anywhere ?
Lars
@Lars: I'm a little rusty on phpMyAdmin, as I rarely use it. I have a feeling that stored procedures are not really supported by phpMyAdmin. You can create them from the SQL tab, but some versions return an error when you try to run the stored procedure. Are you using phpMyAdmin on a shared hosting server, or are you connecting to your own copy of MySQL? If it's the latter, you're best off using the MySQL command line interface. Will you ultimately be calling this from PHP?
Mike
@Lars: I've added some example PHP code which grabs a random item of the day from the stored procedure.
Mike
I am using phpMyAdmin on a shared hosting server. And yes, I will ultimately be using it from php.. :)
Lars
@Mike I usually query mySQL via php using mysql_query which I have wrapped in a function. Is it necessary to use mysqlli in order to use the stored procedure from php?
Lars
@Mike. I just tried the php code you suggested. It gives me the following error: Number of bind variables doesn't match number of fields in prepared statement in
Lars
@Lars: It works with `mysql_query` too. I just prefer mysqli and prepared statements.
Mike
Which was because I had added SELECT * which conflicted with $id, $entry. It seems to be working now. Thanks :)
Lars
@Mike However, it doesn't really account for the fact that I wanted to display one item for one whole day (or 24 hrs probably)..
Lars
@Lars: I see. That complicates things a little ;-) I'll have a think about that.
Mike
@Mike. Excellent, looking forward to that :-) Two other things, if you have the time for it. 1) If I try to use mysql_query, I get the error "can't return a result set in the given context"2) Using the $mysqli version, it works, but it displays weird characters in firefox (small rectangles with 01's..). I presume it has to do with encoding, but not sure.Thanks in advance !
Lars
@Mike. A third issue is that the method you suggested using mysqli both outputs the entry it self (though with weird characters), but it also outputs a string of metadata (like the database name and such)..
Lars
@Lars: I've tried both the `mysqli` and `mysql` methods, and both work correctly. I'm not sure why you are getting those results. [This thread](http://forums.mysql.com/read.php?52,59458,59458) might help. I've updated my answer with a stored procedure which *I think* should provide just one result per day, but I haven't got time to do any proper testing.
Mike
@Mike. Thanks a lot. Trying that right now. Which field type should 'ts' be in the table?
Lars
@Lars: Sorry, I forgot that bit - it's a `DATE` type. I've updated the answer.
Mike
The stored procedure itself seems to be working perfectly (using mysqli). Now I just need to be able to get rid of that weird formatting issue. It must be something that goes wrong in the "$stmt->bind_result($id, $entry);" since the echo of $entry comes out wrong.. It shows some of the parameters from new mysqli(...) along with these weird boxes with numbers and a questionmark in a black box. Perhaps I should try getting it out as an array instead using mysqli_fetch_array ?
Lars
@Mike: when you test it, doesn't echo $item return more than just the actual string, it's supposed to return? No ".. et_db <tablename>..." and stuff like that?
Lars
@Lars: No, `$id` and `$item` are bound to the two values returned by `SELECT id, item FROM iotd WHERE id = ?`. The number of variables in `bind_result` should match the number of values returned by the query (the stored procedure in this case). Have you changes the procedure to return a different number of values?
Mike
Just a thought: could it be that the table I query is not InnoDB, but myISAM ?
Lars
@Mike: The stored procedure matches the php code and the stored procedure works for sure. It's just that I get these strange extra strings, when I echo $item and view the output in Firefox.. Things that usually indicate charset problems.
Lars
A: 

Why don't you use sequence?

Sequence serves your purpose easily...

Dinesh Atoliya
perhaps because I don't know about it? Could you explain?
Lars
A sequence is a database object that is used to generate unique number. Oracle support it. But not sure about mysql.
Dinesh Atoliya
+1  A: 

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.

onedaywhen