0

I have website about dog breeds which has a 'dog of the day' section. I'd like 'dog of the day' to display a new dog (from a mysql database) once a day. Using php and mysql, I'd imagine the operation is actually quite simple. 

$connection = mysqli_connect("localhost", "username", "password");

$randomdog = mysqli_query($connection, "SELECT * FROM dogtable WHERE id >= RAND() * (SELECT MAX(id) FROM dogtable) LIMIT 1"));

echo $randomdog

Of course, this gives me a random dog every-time the page is reloaded. I want $randomdog to only change once a day.

Would a cron job be the answer? I've never implemented one before, but I have cpanel installed on my site, so I don't think it would be too hairy.

If I was to use cron job, I'm assuming I'd save 

$connection = mysqli_connect("localhost", "username", "password");

$randomdog = mysqli_query($connection, "SELECT * FROM dogtable WHERE id >= RAND() * (SELECT MAX(id) FROM dogtable) LIMIT 1"));

as a .php file to be run once a day by the cron job, updating $randomdog once a day. But then how could I pass $randomdog to a separate page to be echoed?

I've asked a similar question here before, and the answers usually go along the lines of "store a unixtimestamp, at a week to it, execute again when current time == time + 1 week".

That's impossible with php? It's only executed when a user requests it, surely? Meaning the 'time + 1 week' variable cannot possibly be stored somewhere.

Anyway, by all means correct me.

Improve this question
1
  • How about seeding a random number generator in PHP with the current week? Commented Mar 10, 2013 at 0:42

4 Answers 4

Reset to default
4

Yes, you can use rand() to get a random item. You want it to be seeded so it returns the same initial rand value each time it is called. You want it seeded with the same seed every day. You can use dayofyear() to get the day of the year (1-366). The format is:

select * from TABLE order by rand(dayofyear(CURRENT_DATE)) limit 1
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Could you explain 'seeding' please?
Wow, just tried this out: srand(floor(time() / (60*60*24*7))); echo rand() % 100; Do you know how this works? Where is this number stored? *edit Actually, I just tried this in a different browser and got a different number. It needs to be the same for every user.
Pseudo-random number generators (PRNGs) are used to produce what we call "random numbers" in computers. They are not really random. They are sequences of numbers produced by starting with a seed value and running a function on it over and over. A simple (and stupid) one would be starting with a seed and running "seed = seed*13%7" to get the next random number. By using the same seed, you get the same sequence every time you ask for random numbers. That is why you SHOULD seed the random number generator before using it.
Excellent stuff. I've set it up to echo a random number between 0 and $number_of_database_rows. It works perfectly, but what with it running over the same function again and again, do you reckon it would start to get really, really slow with huge numbers of database rows? Just interested really, as this'll be perfect for my project!
Ordering by rand is known to cause sluggish behavior in MySQL when you have large table sizes. But, I work with tables that have millions of records and I can pull a random record with order by rand in under a second - but, that is on a server tailored for MySQL (tons-o-ram and tons-o-spindles). I don't think you'd see noticeable slowdown in normal usage.
0

You can create another table, probably consisting of just single row which will store currently chosen dog of the day - it may just have single unique id pointing to dogtable.

Overnight cron job will update this table. It does not have to be just random, it can use more advanced techniques, for example look into site usage history to actually select more popular dogs.

Website will use this dogoftheday table to display dog of the day.

Improve this answer

1 Comment

probably better to chose the less popular dog
0

I can see many ways to do so, and the simplest (not the easiest) way is as following:

Use the cron job (you can install it very easily from cPanel), the cron job will pick a random choice and stored, I would propose on of the following ways to store it

  1. add a new fiel in your table, not null, default value to 0, and only the selected dog for the day will have the value of 1 (will be updated via the cronjob script)
  2. if you are interested in more advanced stuff such as keeping history of the daily selected dog (e.g: to not select the same twice a week or a month), create a new table where you put the select dog/day, if you order by time, todays dog is the newest inserted record
  3. if you are not interested in keeping trace, or any advanced stuff, and wish to have your own way (to optimize some stuff regarding performance and memory), make the cron job write to a file

I hope this helps

Improve this answer

Comments

0
  1. Create a table to store the random id and initialise it

i.e.

CREATE TABLE random_dog
(
     INT id
);

INSERT INTO random_dog VALUES(RAND());

  1. Check that you have events running - see this

  2. Create an event

i.e.

CREATE EVENT
ON SCHEDULE EVERY DAY STARTS '00:00:00'
ON COMPLETION PRESERVE
DO UPDATE random_dog SET id = RAND();
  1. For the query simply use the random_dog table

i.e.

SELECT * FROM dogtable 
JOIN random_dog ON (random_dog.id = dogtable.id)
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.