Random Image

rivka

I swear, this is about to drive me mad -- I've got some info in a database that includes links to images and some text for each image -- and I just want to pull a random row from the database, and display the image with the text. I've been reading through the forums, and found the following way to pull a random row from my table:

$result = mysql_query("SELECT * FROM random_block ORDER BY RAND() LIMIT 1");
$block = mysql_fetch_array($result);

echo "$block[image] $block[text]";

The thing that's driving me crazy is that although there are 5 items in the database, it keeps pulling up only the first one. Sometimes, if I hold down shift+refresh, I can get it to pull another row (image+text) but not always. Is this a caching problem? And is there anyway to force it to display a different item each time?

planetsim

Hmmm.. Weird that this is happening. I would presume you answered your question.

rivka

Well, not really. I still don't know how to prevent it 😉

rivka

Well, I don't know what the deal is but no matter what I do, I can't get rand() within the sql query to actually pull out a random row. So instead, I'm generating a random number, and pulling out the item that has that (autoincremented) id number. Only problem is, what if a row is deleted and there's nothing in the table with that id anymore? It then pulls up nothing, which is not good. Anyone have any ideas to work around that?

rivka

In case this is of use to anyone, I found the following comment on the mysql doc pages:

As of MySQL 3.23.52, MySQL changed the way
RAND() functions, such that you MUST supply a
SEED to get an actual random number. If you do
not, each new connection will return close to the
same number as the previous new connection. An
example to test this would be to run the follow
command multiple times in a row. mysql -u
username -p -e'select rand()' A basic way to seed
this would be to run RAND(NOW()).

I did the following:

$seed = srand((double) microtime() 1000000);
$result = mysql_query("SELECT FROM random_block ORDER BY RAND($seed) LIMIT 1");

And it now works. Without the seed it did not.