• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 683
  • Last Modified:

MySQL Query ORDER BY RAND() time interval question

Hi!

I have a code which outputs some random words from my DB, it works very fine. Every time the URL is loaded the page refreshes with the new random words.

Question:
How can I change this query or code so new words are not loaded every time when the URL is refreshed? I would like them to refresh every 24 or 48 hours instead, but not every time.

I have been trying to code this, but no success so far.

Thanks for your help!

<?php
$result = mysql_query("select bizName,bizAddr,bizCity,bizZip,bizPhone from biz_cons WHERE bizState ='$bizState' AND bizLive='1' ORDER BY RAND() LIMIT 2");
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];?>

Open in new window

0
seopti
Asked:
seopti
  • 8
  • 5
  • 5
  • +1
1 Solution
 
nizsmoDeveloperCommented:
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(); ?>

Open in new window

0
 
seoptiAuthor Commented:
Thank you for the code, I have tried it, but their is an empty output after the first refresh (no output at all).
0
 
nizsmoDeveloperCommented:
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(); ?>

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
nizsmoDeveloperCommented:
You may have to close and open your browser to reset the session.
0
 
steelseth12Commented:
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 ?
0
 
imitchieCommented:
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];?>

Open in new window

0
 
steelseth12Commented:
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.
0
 
seoptiAuthor Commented:
Thanks, but all those untested solutions did not produce any usable results.
0
 
steelseth12Commented:
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");
0
 
seoptiAuthor Commented:
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.
0
 
steelseth12Commented:
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.
0
 
seoptiAuthor Commented:
Once again thanks for all your suggestions, but this solution simply won't work. No insert takes place at all.
0
 
seoptiAuthor Commented:
Is there a different solution anyone could come up with?
0
 
steelseth12Commented:
There seem to be some of syntax error in imitchie example. I corrected them and tested on some sample data and it now seems to work fine.

Ohh didnt understand why you had

 $bizName = $r['Name'];
                $bizAddr = $r['Addr'];
                $bizCity = $r['City'];
                $bizZip = $r['Zip'];
                $bizPhone = $r['Phone'];
 
$row = mysql_fetch_row($result);

as those fields dont correspond to the ones in the table so i removed that bit.


mysql_query("delete biz_cons_cache where cached_time < datesub(now(), interval 1 day);");
 
mysql_query("insert into biz_cons_cache(cached_time,bizName,bizAddr,bizCity,bizZip,bizPhone,bizState,bizLive) (SELECT NOW(), bizName,bizAddr,bizCity,bizZip,bizPhone,bizState,bizLive FROM biz_cons WHERE bizState ='$bizState' AND bizLive='1' AND NOT EXISTS (select * from biz_cons_cache WHERE bizState ='$bizState' AND bizLive='1') ORDER BY RAND() LIMIT 2)") or die(mysql_error());
 
$result = mysql_query("select bizName,bizAddr,bizCity,bizZip,bizPhone from biz_cons_cache");
while($row = mysql_fetch_row($result))
                {
 
echo $row[0];?>, <?php echo $row[1];?>, <?php echo $row[2];?>, <?php echo $row[3];?>, <?php echo $row[4]; 
 
}

Open in new window

0
 
imitchieCommented:
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
0
 
seoptiAuthor Commented:
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?

0
 
imitchieCommented:
>>> 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.
0
 
seoptiAuthor Commented:
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.

0
 
imitchieCommented:
>>I'm still fighting with the code
So you're accepting code you have not tested and are now crying wolf?
0
 
seoptiAuthor Commented:
This was an information for all people trying to use the code.

imitchie: Just shut the fuck up and leave my thread.
0
 
imitchieCommented:
"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
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 5
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now