MySQL Query ORDER BY RAND() time interval question

You can try do it with sessions and session expire, which expires in 24 hours. This is a concept and may not work as I am unable to test, but modify accordingly and see if you can get it working.

hope this helps.

<?php
session_start();
if(session_is_registered("results"))
{
	// result set which displays the same result
	$result = $_SESSION["results"];
}
else 
{
	$result = mysql_query("select bizName,bizAddr,bizCity,bizZip,bizPhone from biz_cons WHERE bizState ='$bizState' AND bizLive='1' ORDER BY RAND() LIMIT 2");
	session_cache_expire(1440);
	$_SESSION["results"] = $result;
}
 
 
while($r=mysql_fetch_array($result))
                {
                $bizName = $r['Name'];
                $bizAddr = $r['Addr'];
                $bizCity = $r['City'];
                $bizZip = $r['Zip'];
                $bizPhone = $r['Phone'];
 
$row = mysql_fetch_row($result);
}
?>
<?php echo $row[0];?>, <?php echo $row[1];?>, <?php echo $row[2];?>, <?php echo $row[3];?>, <?php echo $row[4];?>
<?php session_write_close(); ?>

Select allOpen in new window

Thank you for the code, I have tried it, but their is an empty output after the first refresh (no output at all).

Mya I ask why you retrieve the result twice? why you have a $r and a $row (mysql_fetch_array() and mysql_fetch_row() which is essentially the same thing?
Anyway give the below code a try, not sure if it will output what you expect.

<?php
session_start();
if(session_is_registered("results"))
{
        // result set which displays the same result
        $row = $_SESSION["results"];
}
else 
{
        $result = mysql_query("select bizName,bizAddr,bizCity,bizZip,bizPhone from biz_cons WHERE bizState ='$bizState' AND bizLive='1' ORDER BY RAND() LIMIT 2");
        session_cache_expire(1440);
        while($r=mysql_fetch_array($result))
                {
                $bizName = $r['Name'];
                $bizAddr = $r['Addr'];
                $bizCity = $r['City'];
                $bizZip = $r['Zip'];
                $bizPhone = $r['Phone'];
 
		$row = mysql_fetch_row($result);
		}
        $_SESSION["results"] = $row;
}
 
 
 
?>
<?php echo $row[0];?>, <?php echo $row[1];?>, <?php echo $row[2];?>, <?php echo $row[3];?>, <?php echo $row[4];?>
<?php session_write_close(); ?>

Select allOpen in new window

You may have to close and open your browser to reset the session.

seopti, when you say you want to refresh them any 24 hours you mean for the specific user ? or everyone should get the same results in those 24 hours ?

There may be better ways, but I can think of this one for now:

clear from the cache if the data is older than 24 hours (i.e. 1 day)
insert into the cache if it is empty  (i.e. first time or cleared)
select the actual return result from the cache

Note: if you encounter any minor sql errors, I hope you can fix it - I wrote this from memory for concept only!

<?php
$result = mysql_query("
delete biz_cons_cache where cached_time < datesub(now(), interval 1 day);
insert into biz_cons_cache
select now(), bizName,bizAddr,bizCity,bizZip,bizPhone from biz_cons WHERE bizState ='$bizState' AND bizLive='1'
where not exist (select * from biz_cons_cache)
ORDER BY RAND() LIMIT 2;
select bizName,bizAddr,bizCity,bizZip,bizPhone from biz_cons_cache
");
while($r=mysql_fetch_array($result))
                {
                $bizName = $r['Name'];
                $bizAddr = $r['Addr'];
                $bizCity = $r['City'];
                $bizZip = $r['Zip'];
                $bizPhone = $r['Phone'];
 
$row = mysql_fetch_row($result);
}
?>
<?php echo $row[0];?>, <?php echo $row[1];?>, <?php echo $row[2];?>, <?php echo $row[3];?>, <?php echo $row[4];?>

Select allOpen in new window

imitchie concept will work fine but you have to break up the queries as php's mysql_query only allows 1 query to be excecuted.
If you are using php5 you could always run mysqli_multi_query and run all of the together.

Thanks, but all those untested solutions did not produce any usable results.

seopti using imitchie example ... you need to create a cache table ....  

biz_cons_cache with the exact same fields as your biz_cons table plus one datetime field called cached_time

then you need to break the queries as shown below ... and it should work fine.

mysql_query("delete biz_cons_cache where cached_time < datesub(now(), interval 1 day);");

mysql_query("insert into biz_cons_cache select now(), bizName,bizAddr,bizCity,bizZip,bizPhone from biz_cons WHERE bizState ='$bizState' AND bizLive='1'
where not exist (select * from biz_cons_cache) ORDER BY RAND() LIMIT 2;")

$result = mysql_query("select bizName,bizAddr,bizCity,bizZip,bizPhone from biz_cons_cache");

steelseth12, thank you for explaination, now I understand it.

I didn't mention that my Databases have more than 500k - 1M entries, co copying the whole DB just to create a cache field would not make a lot of sense.

No it doesn't copy the whole database it would just copy that one record into the table every 24 hours.... it should actually give you a performance boost.

Once again thanks for all your suggestions, but this solution simply won't work. No insert takes place at all.

Is there a different solution anyone could come up with?

seopti, are you after some help to guide you on your way or are you after the whole thing done and spelt out with i's dotted and t's crossed? your comments have been downright unpleasant

steelseth12: Thank you, I will be testing it right now.

imitchie: It seems to me  like you can't handle that you provided a solution full of syntax errors?
Either provide or correct solution or no solution, my time is valueable and I can't handle half-hearted attemps like yours.

I have thanked everyone here, so just tell me boy, where have I been unpleasant?

>>> full of syntax errors?
you're so full of it. I took your code as IS, on the good faith that it WAS working. I replaced only the SQL part with a concept that will solve your problem.  That's it, how is it _FULL_ of syntax errors? Are you telling me you posted a BROKEN piece of code that needs fixing as well as a solution to the actual problem at hand of caching?

>> Thanks, but all those ___untested___ solutions did not produce any usable results.
Well, I figured you were, you know, a GURU on the subject and can handle concept to completion. Guess I was wrong.  I told you in plain english that it is untested.

Comment for people want to use the accepted solution:

Insert works fine, but this code is stil 80%  buggy, it inserts the year 2007 instead  of a real date and does not delete the inserts From DB. So you need to find out for yourself. I'm still fighting with the code ...


Cheers.

>>I'm still fighting with the code
So you're accepting code you have not tested and are now crying wolf?

This was an information for all people trying to use the code.

imitchie: Just shut the fuck up and leave my thread.

"you're not a bad person, but what you did was.. really, really bad". obscenity does not a gentleman become.
grow up and learn some proper php/mysql skills