Need help with random query

[rakuci]

Hi,
I have a table with fields id,date,name,image and I want to display one record on my home randomly every week not every time page refreshes.

Thanx

[NogDog]

I think you could do something like this:

SELECT * FROM table_name ORDER BY RAND(DATE_FORMAT(CURDATE(), '%U')) LIMIT 1;

[rakuci]

And wat abbout displaying every 24 hour do I need to use '%T' in place of '%U'

[Sxooter]

I'd create a simple table to store a reference to the one row you'll be displaying for a week. then, have a cronjob or something run every sunday morning to pick a new random record to show, put the id in that table, and use it to select the random record each time with something simple, like

select * from maintable m join sampletable s on (m.id=s.mid) limit 1;