[Resolved] Random selection according to date

kcgame

Hi,

I have a problem here which can't seem to think of a solution out of it :bemused:
I would like to have something like a 'Thought of the day' on my website and is wondering that is there any query that can select a random record according to today's date?

The RAND() outputs differently everytime i query the database..so its really not that suitable for me 🙁

Any help / comment will be appreciated. Thank you 🙂

leatherback

Hi,

have you tried what happens if you seed the rand() with the date? Does it then still give you a different value every time?

Drakla

Or does the database have loads of quotes in, and for each day has a few and you only want to pick between the ones for today so you get a "Someone today said: ..."? If that were the case you could use a

WHERE date=$today ORDER BY RAND()

Along with the query, but you'd have to tailor it to the day part of the date with something like the %j option in DATE_FORMAT.

kcgame

Thanks for the help so far, unfortunately, my database does not have any date associated fields. Basically, i have three tables of products and would like to show the info of a random product each day..(this is getting more complicated than what i thought at first :bemused: )

For the seeding of the mt_srand(date of a day), it will output the same number. However, it will not output consistently for the query:

SELECT * FROM tb ORDER BY RAND() LIMIT 1

I know its getting complicated now as it involves three tables...

Any help / comment will be appreciated.

liquorvicar

why not generate the random number/entry every day at midnight and then store it in a table or flat text file. all you need to do is to refer to the pre-generated number each time and your random entry will remain constant for every 24 hrs.

mrhappiness

//seed value based on current date
mt_srand(date('Ymd'));

//generate random value
//this value is the same as long as the day stays the same...
$random = mt_rand(0, 100000);

$sql = 'SELECT *
FROM tablename
ORDER BY RAND('.$random.')
LIMIT 1';

kcgame

Thanks mrhappiness, the query you provided is actually the thing i'm looking for initially. However, as things are getting complicated here (three tables with no-fixed number of records), the RAND ($var) query can't get me what i want so dynamically..🙁

Think i will adopt what liquorvicar has suggested ( currently doing that 😉 ) Storing the date, table id and item id to my database and display from there 🙂

Thanks guys for your valuable inputs 😃