Solved

PHP Timer to execute MySQL queries 5 minutes apart

Posted on 2009-03-31
24
1,479 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 19

Expert Comment

by:NerdsOfTech
Comment Utility
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
Comment Utility
@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
Comment Utility
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
Comment Utility
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
Comment Utility
@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
Comment Utility
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
Comment Utility
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
Comment Utility
@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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

743 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

15 Experts available now in Live!

Get 1:1 Help Now