Randomizing a MYSQL Result

[basicwe]

(I know this is a very odd request)
If I wanted to display random information from a database I'd write something like the code below.

PHP Code:

<? 
$cnx = mysql_connect("localhost", "root", ""); 
mysql_select_db("entire", $cnx); 
$sql = mysql_query("SELECT table FROM entire") or die (mysql_error()); 
while($row = mysql_fetch_array($sql)){ 
    $row_array[] = $row['row']; 
} 
mysql_close($cnx); 
$random_row = $row_array[rand(0, count($row_array) - 1)]; 
echo $random_row; 
?>

----------------------------------
What I'm wondering is if it is possible to determine how long the random information is displayed? I was wondering if it were possible to set a specific ammount of time, and display the database information for that set time?

For example, if I wanted to randomly pull out information using the code above is there any way I'd be able to let it be displayed for 24 hours?

And as a bonus I was wondering if there is a way to not let the same piece of information be displayed twice until all of the other information in the database has been displayed once?

If anyone has any tips, or php functions that may be of any use I'd be very glad to hear them.


Thanks.

[Jolt]

You'd have to add a date field or time stamp to the DB then you could add a display flag as well. when the page displays run another query to update the timestamp and set the display flag to yes. Then change the query to pull data with the display flag set to no, and the time stamp after today.

[notjustgraphics]

Jolt's method will work, but it may create some unnecessary MySQL Queries...

There may be a way to select a record based on the Current Date...

Since Dates are expressed in a Julian method, no second ever has the same time stamp... if you keyed based on the date itself, there would be an automatic 24 hour period... when the next day starts, it will show the next record...

Is there a reason that your selection of records has to be randome? Can it not be sequential?

Just curious..

Mike.

[basicwe]

Originally Posted by notjustgraphics

Jolt's method will work, but it may create some unnecessary MySQL Queries...

There may be a way to select a record based on the Current Date...

Since Dates are expressed in a Julian method, no second ever has the same time stamp... if you keyed based on the date itself, there would be an automatic 24 hour period... when the next day starts, it will show the next record...

Is there a reason that your selection of records has to be randome? Can it not be sequential?

Just curious..

Mike.

It could be sequential it wouldn't be a big deal to just let it be some sort of loop.

It's not pulling the info out of the database, or the order that is important to me. It's finding a way to display the info for one full day.

[notjustgraphics]

Ok...

Lets say you have 365 records in your database, each with a numberic field numbered 0 to 365....

If you create an if statement like this:

PHP Code:

$todaysdate = date("z");
$query="select * from tablename where id = " . $todaysdate; 


In the code above, replace tablename with the name of your table... I've assumed you've already established a connection to your database.

What this is selecting one record from the database that corresponds with the numerical day of the year (Ie: New Years day, the first day of the year, is 1 and New Years Eve, the last day of the year, is 365)

Now, this method is simple, but requires you to have 1 and only one entry for each day of the year.... if you have say 183 entries (half the days in a year) you could divide the $todaysdate by 2 to get your id number, but this would cause the same record to be displayed for 48 hours.

The other method, which requires an update command, is to set a 'displayed' flag that marks a record as already used until the entire database is marked as 'displayed' then you have to clear all the flags, and begin the process again...

Are you comfortable coding in PHP?

Mike.

[basicwe]

Thank you very much. You have given me a very good idea on how to accomplish my goal! And yes, I am very comfortable coding inphp. Thanks again.