Solved

MySQL Query ORDER BY RAND()  time interval question

Posted on 2007-12-01
21
655 Views
Last Modified: 2013-12-12
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
Comment
Question by:seopti
  • 8
  • 5
  • 5
  • +1
21 Comments
 
LVL 21

Expert Comment

by:nizsmo
Comment Utility
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
 

Author Comment

by:seopti
Comment Utility
Thank you for the code, I have tried it, but their is an empty output after the first refresh (no output at all).
0
 
LVL 21

Expert Comment

by:nizsmo
Comment Utility
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
 
LVL 21

Expert Comment

by:nizsmo
Comment Utility
You may have to close and open your browser to reset the session.
0
 
LVL 20

Expert Comment

by:steelseth12
Comment Utility
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
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
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
 
LVL 20

Expert Comment

by:steelseth12
Comment Utility
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
 

Author Comment

by:seopti
Comment Utility
Thanks, but all those untested solutions did not produce any usable results.
0
 
LVL 20

Expert Comment

by:steelseth12
Comment Utility
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
 

Author Comment

by:seopti
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 20

Expert Comment

by:steelseth12
Comment Utility
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
 

Author Comment

by:seopti
Comment Utility
Once again thanks for all your suggestions, but this solution simply won't work. No insert takes place at all.
0
 

Author Comment

by:seopti
Comment Utility
Is there a different solution anyone could come up with?
0
 
LVL 20

Accepted Solution

by:
steelseth12 earned 500 total points
Comment Utility
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
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
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
 

Author Comment

by:seopti
Comment Utility
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
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
>>> 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
 

Author Comment

by:seopti
Comment Utility
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
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
>>I'm still fighting with the code
So you're accepting code you have not tested and are now crying wolf?
0
 

Author Comment

by:seopti
Comment Utility
This was an information for all people trying to use the code.

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

Expert Comment

by:imitchie
Comment Utility
"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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now