Solved

MySQL Query ORDER BY RAND()  time interval question

Posted on 2007-12-01
21
671 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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
 
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

Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

Question has a verified solution.

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

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
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 create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

691 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