Random image help

calavera

I'm have a database of images that looks like this:

Image | Title | URL

img1 til1 url1
img2 til2 url2

I'm trying to show a random image on my front page, but everytime the page loads, I get the same image. Even if i went to the page a day later.
Any ideas what i'm doing is wrong?

Here's the code:
<?php

$result = mysql_query("SELECT *,RAND() AS ran FROM posters ORDER BY ran LIMIT 1 ",$db);

$myrow=mysql_fetch_array($result);

printf("<img src=\"%s\">",$myrow["img"]);

Thanks on advance for the help.
-Manuel

[deleted]

try
SELECT * FROM posters ORDER BY rand() LIMIT 1;

calavera

That dowan't work, I tried before and got the following error:
Warning: Supplied argument is not a valid MySQL result resource

rrijnders

I suspect that your use of rand() and LIMIT are your problems. When you SELECT and LIMIT=1, only one record is ruturned. Only then is the RAND() function used to generate a floating point value between 0 - 1.0 and put it into the 'ran' variable.

You will probably have better luck picking a random number in php and then selecting that record from your db.

HTH

-- Rich Rijnders
-- Irvine, CA US

[deleted]

Ehm.. excuse me, what I posted was a quote from the mysql manual. :-)

I guess Manuel is using an older version of MySQL that does not yet support order by rand().

rrijnders

I was referring to Manuel's original example, not your's, Vincent.

Manuel was using "rand() AS ran", thus putting it in a column that he later uses ORDER BY to sort.

Among the problem used in combination of 'LIMIT 1' that I mentioned; from mySql.com:

"You can't use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times."

The manual does say, however;

"In MySQL Version 3.23, you can, however, do: SELECT * FROM table_name ORDER BY RAND()"

which was your example. This may work (I haven't tried it) but all of the example code I have seen use limits of, say, 1000 after narowing the range with WHERE clauses.

Sory for the confusion...

-- Rich Rijnders
-- Irvine, CA US

[deleted]

Okie! :-)

The example from the manual does work, but indeed it's smart to put a limit on it if you only need one result.