Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1808
  • Last Modified:

PHP Timer to execute MySQL queries 5 minutes apart

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
kevandju
Asked:
kevandju
  • 11
  • 7
  • 4
  • +1
2 Solutions
 
BrianMMCommented:
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
 
zerooCommented:
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
 
NerdsOfTechCommented:
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
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.

 
kevandjuAuthor Commented:
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
 
NerdsOfTechCommented:
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
 
NerdsOfTechCommented:
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
 
NerdsOfTechCommented:
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
 
kevandjuAuthor Commented:
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
 
kevandjuAuthor Commented:
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
 
NerdsOfTechCommented:
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
 
NerdsOfTechCommented:
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
 
NerdsOfTechCommented:
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
 
zerooCommented:
@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
 
NerdsOfTechCommented:
zeroo I recomment that the asker NOT continually run that script as output will suck the RAM dry from output...
0
 
NerdsOfTechCommented:
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
 
zerooCommented:
@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
 
kevandjuAuthor Commented:
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
 
kevandjuAuthor Commented:
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
 
zerooCommented:
@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
 
kevandjuAuthor Commented:
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
 
kevandjuAuthor Commented:
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
 
NerdsOfTechCommented:
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
 
NerdsOfTechCommented:
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 11
  • 7
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now