Solved

MySQL Query ORDER BY RAND()  time interval question

Posted on 2007-12-01
21
657 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
ID: 20390115
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
ID: 20390170
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
ID: 20390180
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
ID: 20390182
You may have to close and open your browser to reset the session.
0
 
LVL 20

Expert Comment

by:steelseth12
ID: 20390381
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
ID: 20390625
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
ID: 20390638
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
ID: 20391090
Thanks, but all those untested solutions did not produce any usable results.
0
 
LVL 20

Expert Comment

by:steelseth12
ID: 20391302
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
ID: 20391693
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 20

Expert Comment

by:steelseth12
ID: 20391704
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
ID: 20391850
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
ID: 20391851
Is there a different solution anyone could come up with?
0
 
LVL 20

Accepted Solution

by:
steelseth12 earned 500 total points
ID: 20391925
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
ID: 20392131
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
ID: 20392695
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
ID: 20392725
>>> 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
ID: 20393241
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
ID: 20393323
>>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
ID: 20393394
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
ID: 20393425
"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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

930 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

11 Experts available now in Live!

Get 1:1 Help Now