Randomize the order of an array after x amount of time

[Novocaine88]

Hi, I'm not necessarily looking for code examples for my problem, (they are welcome but its not my explicit goal). I'm looking more for advice on how to go about tackling the problem i have.

 

what i already have

Currently i have a search getting results from a MySQL database.

The results are ordered by one field that signifies their importance.

Many of these results have the same value in this one column, and i have a switch statement to distinguish different content/styles for the different values. (there are just over 10 different bands)

 

So the most important results are shown first and with a different style to the ones below it. (some bands have the same styling)

 

what i need

I would like to be able to randomize the results but only randomize the results within their specified bands.

e.g. lets say theres 100 results in band A and 100 in band B. I still want everything in band A to be listed above band B, but i would like to randomly change the order of the results in band A and band B, (in this case once a week). I need to save the order of this randomization (client/server side?) for a week, then run the script again to set a new order.

 

 

Now I would still class myself as an amateur php'er so at this stage i dont even know how i would go about separating the bands and randomizing their results independantly, let alone causing this functionality to run only once a week.

 

 

 

So can anyone suggest a way i might go about this? I'm pretty stuck right now.

 

Cheers

 

[btherl]

Since you're only doing this once a week, you can do it a simple way:

 

SELECT * FROM table ORDER BY band, RAND()

 

The primary order is on band, and secondary order is random.

 

Then you can loop through the results assigning values to a new column "rand_order" in your table, starting at 1 and going upwards.  Then to fetch them in your random order for that week, you just need to order by that column: "ORDER BY rand_order"

 

Does that sound like what you need?

[btherl]

Or even simpler:

 

UPDATE table SET rand_order = RAND()

 

once a week.  Then you can order by "band, rand_order" when fetching the data.

[Novocaine88]

So would

SELECT * FROM table ORDER BY band, RAND()

 

randomize the order of the results within their separate bands?

If thats the case, then that sounds good.

 

But how would i go about doing this automatically only once a week? It looks like your suggesting a manual once a week "re-order"

 

[spiderwell]

create an event on mysql directly to trigger once a week?

[Novocaine88]

Right, I've got what i wanted. Thanks for all your suggestions.

 

What I did was use the

UPDATE table SET rand_order = RAND()

and the

SELECT * FROM table ORDER BY band, RAND()

(thanks btherl)

 

which did exactly what i wanted. Randomized the results completely, then when printing them out ordered them initially by their band and then their random number.

 

 

And I've been told I can set up a cron job to run the randomization script once a week to changg the order again. (not sure if this is sort of what you meant spiderwell)

 

 

Once again, Cheers for the help

[btherl]

That will give you a different random order every single time.  The select should be

 

SELECT * FROM table ORDER BY band, rand_order

 

in order to use the stored order that is updated each week with cron.

[requinix]

RAND() allows you to seed the number generator. You don't need cron or a rand_order field:

"SELECT * FROM table ORDER BY band, RAND(" . date("YW") . ")"

date(YW) will spit out a different number each week (which starts on Monday) and within that week it will always be the same number. Same RNG seed means you get the same numbers in the same sequence each time.

[spiderwell]

 

 

And I've been told I can set up a cron job to run the randomization script once a week to changg the order again. (not sure if this is sort of what you meant spiderwell)

 

 

Once again, Cheers for the help

 

maybe, I not sure i know what a cron job is, but event is created on the mysql database and is a bit of SQL that is trigger at times you specify it for example:

 

CREATE EVENT `myevent`
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
  ON COMPLETION PRESERVE 
DO 

UPDATE `mytable` 
SET `myfield` = NULL;

this sets a column to null once a day.

 

events usually drop after execution the on preserve bit means it doesnt drop the event once executed, so will run daily

 

find out more here http://dev.mysql.com/doc/refman/5.1/en/create-event.html

 

[Novocaine88]

That will give you a different random order every single time.  The select should be

 

SELECT * FROM table ORDER BY band, rand_order

 

in order to use the stored order that is updated each week with cron.

 

Yeh sorry, that is what i have used. Didnt check what i wrote

 

RAND() allows you to seed the number generator. You don't need cron or a rand_order field:

"SELECT * FROM table ORDER BY band, RAND(" . date("YW") . ")"

date(YW) will spit out a different number each week (which starts on Monday) and within that week it will always be the same number. Same RNG seed means you get the same numbers in the same sequence each time.

 

Thanks for this, ill give it a go. It sounds like a better solution

[Novocaine88]

RAND() allows you to seed the number generator. You don't need cron or a rand_order field:

"SELECT * FROM table ORDER BY band, RAND(" . date("YW") . ")"

date(YW) will spit out a different number each week (which starts on Monday) and within that week it will always be the same number. Same RNG seed means you get the same numbers in the same sequence each time.

 

I've given this a whirl and it seems to do the job very nicely.  Cheers