Solved

PHP Timer to execute MySQL queries 5 minutes apart

Posted on 2009-03-31
24
1,499 Views
Last Modified: 2013-12-12
I want to write a script, so when I call example.php that a MySQL query will execute, then the page will countdown 5 minutes (visibly) and then execute another MySQL query.  I need to stagger these queries because of the strain it puts on the database.  And I only want these queries to happen when someone calls up the web page example.php.  This can not be done using Cron as I only want this to happen when someone calls the page.

I'm familiar with PHP somewhat and I know all my MySQL queries that I want to run, just not sure how to implement this timer.  

Thanks for the input/help with this.
0
Comment
Question by:kevandju
  • 11
  • 7
  • 4
  • +1
24 Comments
 
LVL 11

Expert Comment

by:BrianMM
ID: 24030578
Hi,

Not a PHP Q. More JS....

Just about to go home for the day, but this will do it (used for similar) it displays it in the Status Bar, but should be easy to modify to write to a div.

http://www.javascriptkit.com/script/script2/autofresh.shtml

- B
0
 
LVL 2

Accepted Solution

by:
zeroo earned 250 total points
ID: 24031010
I made a quick script for you but isn't the best solution, because the php script runs during the countdown and if you have big traffic on example.php, you'll have many connections open.
<?php

set_time_limit(320); // the maximum allowed time for your script to run

//your 1st mysql query here
 

countdown(300); // 300 seconds = 5 mins

sleep(300); // php script sleeps for 300 seconds

//your 2nd mysql query here
 

function countdown($secs){

  $output = '<div id="timer"></div>'; 

  $output .= '<script type="text/javascript"> ';

  $output .= 'function timer(secs){ var el = document.getElementById("timer");';

  $output .= 'var m = parseInt(secs/60); var s = secs - m*60; m=checkt(m); s=checkt(s);';

  $output .= 'el.innerHTML=m+":"+s; newsecs = secs - 1;';

  $output .= 'if (newsecs > 0) setTimeout("timer(newsecs)",1000);';

  $output .= 'else el.innerHTML="ready"; }';

  $output .= 'function checkt(i){ if (i<10) i="0" + i; return i; }';

  $output .= 'timer('.$secs.');';

  $output .= '</script>';

  echo $output;

  flush();

  }
 

?>

Open in new window

0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24031237
First, you will need a temporary table and a FIELD to store an UPDATE time in. Or you can use a text file if you have CHMOD 777 in directory.

Let me know when this is done first.

=NerdsOfTech
0
 

Author Comment

by:kevandju
ID: 24031612
zeroo, I'm going to give that a try here and let you know how it works.  That script will only have max 1 connection to the example.php.  It is just something I want so I don't have to manually enter these queries and walk away and then remember to go back and enter the next one, etc...  
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24032040
kevandju, if you want your script to load only on page visit AND after a time interval has past then you can do it a much better way.
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24032117
Here is a file version. I have a database version also but this one doesn't require db load. CHMOD 777 in folder is needed for the .txt file location

=NerdsOfTech
<?php
 

// time interval load by NerdsOfTech

// updates a value to LOCAL DIRECTORY file
 

 

$file='value.txt'; 	// file for temporary storage of data

$update=0; 		// flag to update file

 

if (file_exists($file)) 

{

 $startdate = filemtime($file);	// last update time

 $nowdate = time();

 $interval = ($nowdate - $startdate) / (60);	// minutes = 60, hours = 60*60, days = 60*60*24

 

 if ($interval >= 5){

  $update=1; // more than 5 minutes passed flag for update

 }

}

else

{

 $update=1; // file does not exist update flagged

}

 

 

if ($update){

  $fil = fopen($file, 'w');

  fwrite($fil, $data)); 	// create value to write (optional)

  fclose($fil);
 

  // create code to run if update is true HERE
 
 

 }

}

 

?>

Open in new window

0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24032150
Basically it USES the file for LAST UPDATE and updates (excutes your query) on PAGE LOAD on IF 5 minutes have passed from the MODIFICATION DATE of the file updated every update.

ALSO, you could use the file to store a number such as a counter for cycles etc. the $data is not important - becuase the file is being used just for its MODIFICATION DATA attribute as MEMORY

=NerdsOfTech
0
 

Author Comment

by:kevandju
ID: 24032542
zeroo, I tried the code below and got nothing, just a blank page and neither of queries executed.
<?

	include ('./includes/handle.inc.php');

	

	session_name ('CableNOW'); 

	session_start();  

	

	if (!isset($_SESSION['usr_id']) || ($_SESSION['usr_type'] != "admin")) {

		$url = 'http://' . $_SERVER['HTTP_HOST'] . dirname($_SERVER['PHP_SELF']);

		

		if ((substr($url, -1) =='/') OR (substr($url, -1) == '\\')) {

			$url = substr ($url, 0, -1); //chop off the slash

		}

	

		$url .= '/index.php';

		header("Location: $url");

		exit();

	}

	

	require_once ('./includes/mysql_connect.php'); //connect to the databse

	

	set_time_limit(1520); // the maximum allowed time for your script to run

	$query1= "UPDATE gn_config.cfg_headends SET hed_force_refresh=1, WHERE hed_key =< 10";

	$result = mysql_query ($query1);

 

	countdown(300); // 300 seconds = 5 mins

	sleep(300); // php script sleeps for 300 seconds

	$query2= "UPDATE gn_config.cfg_headends SET hed_force_refresh=1, WHERE hed_key =< 20 AND hed_key > 10";

	$result = mysql_query ($query2); 
 

	function countdown($secs){

	  $output = '<div id="timer"></div>'; 

	  $output .= '<script type="text/javascript"> ';

	  $output .= 'function timer(secs){ var el = document.getElementById("timer");';

	  $output .= 'var m = parseInt(secs/60); var s = secs - m*60; m=checkt(m); s=checkt(s);';

	  $output .= 'el.innerHTML=m+":"+s; newsecs = secs - 1;';

	  $output .= 'if (newsecs > 0) setTimeout("timer(newsecs)",1000);';

	  $output .= 'else el.innerHTML="ready"; }';

	  $output .= 'function checkt(i){ if (i<10) i="0" + i; return i; }';

	  $output .= 'timer('.$secs.');';

	  $output .= '</script>';

	  echo $output;

	  flush();

	}

?>

Open in new window

0
 

Author Comment

by:kevandju
ID: 24032560
Nerdsoftech can you show me your example with executing 4 queries?  Again I only want this to run when i call the example.php page.
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24032905
Yes. I understand.

replace  // RUN YOUR QUERIES HERE

with your code to run the queries
<?php

 

// time interval load by NerdsOfTech

// updates a value to LOCAL DIRECTORY file

 

 

$file='value.txt';      // file for temporary storage of data

$update=0;              // flag to update file

 

if (file_exists($file)) 

{

 $startdate = filemtime($file); // last update time

 $nowdate = time();

 $interval = ($nowdate - $startdate) / (60);    // minutes = 60, hours = 60*60, days = 60*60*24

 

 if ($interval >= 5){

  $update=1; // more than 5 minutes passed flag for update

 }

}

else

{

 $update=1; // file does not exist update flagged

}

 

$data=1;

 

if ($update){

  $fil = fopen($file, 'w');

  fwrite($fil, $data));         // create value to write (optional)

  fclose($fil);

 

  // RUN YOUR QUERIES HERE

  

 

 }

}

 

?>

Open in new window

0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24032954
Every page load the script will CHECK the time between LAST UPDATE to the .txt "MEMORY"

If the specified time interval (which is 5 minutes in this script) has past SINCE LAST PAGE LOAD,  the memory file will been WRITEN TO which will subsequently change modification date of the file ***** AND ***** THE QUERIES ARE EXECUTED.

If the PAGE LOADS before the time interval has past (from the modification time of the memory file) then the QUERIES ARE NOT EXECUTED.

Mission complete.

=NerdsOfTech

0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24032980
The above solution will use a .txt file for memory

For a database run solution Reference this EE question:

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_24266715.html?cid=1066#a23991008

Sincerely,
=NerdsOfTech
0
 
LVL 2

Expert Comment

by:zeroo
ID: 24033191
@kevandju,
I don't know why my solution doesn't work for you. I tested it locally just before I pasted it here.
It's cool because it echos a javascript code that triggers a countdown and you can see how much time you have to wait until the 2nd query is executed.

If you say none of your queries have been executed, then it must be an error somewhere in the script.
You can put error_reporting(E_ALL); at the top of your php script to see all errors/warnings/notices.
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24033922
zeroo I recomment that the asker NOT continually run that script as output will suck the RAM dry from output...
0
 
LVL 19

Assisted Solution

by:NerdsOfTech
NerdsOfTech earned 250 total points
ID: 24033938
Again, I would run the script UPON PAGE LOAD and then on each SUBSEQUENT page load IF THE TIME HAS PASSED the INTERVAL SPECIFIED.

=NerdsOfTech
<?php

 

// time interval load by NerdsOfTech

// updates a value to LOCAL DIRECTORY file

 

 

$file='value.txt';      // file for temporary storage of data

$update=0;              // flag to update file

 

if (file_exists($file)) 

{

 $startdate = filemtime($file); // last update time

 $nowdate = time();

 $interval = ($nowdate - $startdate) / (60);    // minutes = 60, hours = 60*60, days = 60*60*24

 

 if ($interval >= 5){

  $update=1; // more than 5 minutes passed flag for update

 }

}

else

{

 $update=1; // file does not exist update flagged

}

 

$data=1;

 

if ($update){

  $fil = fopen($file, 'w');

  fwrite($fil, $data));         // create value to write (optional)

  fclose($fil);

 

  // RUN YOUR QUERIES HERE

  

 

 }

}

 

?>

Open in new window

0
 
LVL 2

Expert Comment

by:zeroo
ID: 24034553
@NerdsOfTech,
> Output will suck the RAM dry from output...

What are you talking about ? Did you analyze my code ?
The php script will echo a html & javascript code only once and after that will sleep for 5 mins.
During this time, the javascript code will update a <div> with the time you need to wait until second query will be executed.

I came up with this solution because the Asker (kevandju) wants a visible countdown.
0
 

Author Comment

by:kevandju
ID: 24035552
zeroo,  I have your script working on the first query, but it does not show any countdown timer and never executes the second query.  Also while this script is running, my web server is unable to load any other pages.

NerdsOfTech, I am open to your idea, but don't quite follow your script enough to edit it.  I have multiple queries that need to run one at a time, with a 5 minute gap in between each query.  I just don't follow how I will be able to do that with your script.
0
 

Author Comment

by:kevandju
ID: 24035565
zeroo, I was wrong, both of my queries are running properly, there is no countdown, which isn't a huge deal, just would be nice to know the script is still running and not locked up and also it does lock up my Apache web server to the point where it is unable to server other pages while this is going.
0
 
LVL 2

Expert Comment

by:zeroo
ID: 24036752
@kevandju,

I'm sorry about your trouble with my script. I have on a VPS (Virtual Private Server) more than 15 scripts that run forever (set_time_limit(0)) executing different queries all the time and the Apache serves tens of pages every second.
0
 

Author Comment

by:kevandju
ID: 24038483
zeroo, do you have any ideas on why the timer wouldn't show or why it would lock up my web server while it is running?
0
 

Author Comment

by:kevandju
ID: 24097308
Anyone else have an idea?  zeroo's solution works for executing queries with a time gap, but it locks up my entire web server in the process.  Never heard back from nerdsoftech on how to implement his script.  
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24100989
You use my script on the loading of the page.

Simply put, the script will check to see if the time from the last update has PASSED. If so, it executes your code.

=NerdsOfTech
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24101010
if you want a countdown you can use a javascript timer and HTML headers to refresh your page and then run the time check.

That way you don't freeze your server's memory up.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

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 …
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
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…
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.

920 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

17 Experts available now in Live!

Get 1:1 Help Now