Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

PHP Timer to execute MySQL queries 5 minutes apart

Posted on 2009-03-31
24
Medium Priority
?
1,731 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
[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
  • 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 1000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
This article discusses how to implement server side field validation and display customized error messages to the client.
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 …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

721 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