How to select a random record from database only once

Question

I want to create a PHP page which selects a random record from MySQL database (a string, which will be used as a unique "token"). I want to select this random record only once and never again afterwards.

In order to do so, I add an extra field to the table (named 'numberfield', containing number 0), which is incremented at the moment the specific record has been selected. This way, only records with the value of 0 in the numberfield table field can be selected. As soon as the random record has been selected the numberfield field is incremented to 1 and can not be selected anymore. I'm thinking about using the following PHP code:

$result = mysql_query("SELECT token FROM table WHERE numberfield < **1** ORDER BY RAND() LIMIT 0,1");

if (!$result) {
    echo 'Could not run query: ' . mysql_error();
    exit;
}

$row = mysql_fetch_row($result);
echo $row[0];
mysql_query("UPDATE table SET numberfield=numberfield+1 WHERE token=" + $row[0] + "");

Will this be the right way to do it ? Any thoughts ?

Furthermore, I want to avoid a different random record is selected at page refresh. What will be the right and most simple way to keep the "token" in cache ? For example, is it possible to keep the "token" in a session which will not be overwritten during page refresh, or do I have to use other techniques like Ajax etc. Your help and comment is highly appreciated !

How big is your table? If it will get pretty big, you may want to consider not using `ORDER BY RAND()`. — Ja͢ck, Jan 19 '13 at 21:44
[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. — Kermit, Jan 19 '13 at 21:55

score 0 · Accepted Answer · edited May 23 '17 at 11:49

You should stop using mysql_ functions as they are deprecated.
You should avoid using ORDER BY RAND() in larger tables because of overhead incurred from seeking a random number.
To accomplish a SELECT ... UPDATE you would need some sort of locking. There is a slim chance that a row could be randomly selected twice. This can be prevented using a stored procedure.
You can just use the session id generated by PHP or a part of it. This topic talks about how unique a session id is and this topic discusses the varying lengths.
You can alternatively create a random string on the PHP side which would save overhead from connecting to the database.

How to select a random record from database only once

1 Answers1