Jump to content

Archived

This topic is now archived and is closed to further replies.

DaVuLf

Page Refresh With Query.

Recommended Posts

I'm looking to make a page that accesses a database where I have a 'time()' string stored, so I can figure out how much time has elapsed. Now, I'm not sure if having the page refresh every 1 second is a good thing to do, as I don't want to throttle the database with a select query every second.

What do you guys think? I can't do a while loop because it just keeps on looping forever and never updates.

I'm using the current code:

[code]
<?
include("includes.php");

$query = "LOCK TABLES time READ";
mysql_query($query);

$current_time = mktime(); // produces time in secconds : 1148786809
//echo $current_time.'</br>';

//Find the starting time from our database.
    $query="SELECT * FROM time";
    $result1=mysql_query($query);
    $num1=mysql_numrows($result1);
    $find_start="SELECT start FROM time WHERE id = '$num1'";
    $find_time= mysql_query($find_start);
    $str_start= mysql_result($find_time,0,0);

//Determine the time number
$start_time = strtotime($str_start); // The start date becomes a timestamp
$end_time = $start_time + (60 * 60); //add one hour
if($end_time <= $current_time){
    $time = 60;
}else{

    $time = ($end_time - $current_time) / 60;    
    $time = $time-60;
    $time = -$time+1;
    $base = floor($time);
    $time = round($time*100)/100;
    $seconds = $time-$base;
    $seconds = round($seconds * 60);
    $minutes = floor($time);
    if ($seconds <10){
        $seconds = "0".$seconds;
    }
    $counter= $minutes.":".$seconds;
}
$query = "UNLOCK TABLES";
mysql_query($query);

mysql_close();
?>
<head>
<meta http-equiv="refresh" content="1;url=http://10.0.0.100/wdt/timer.php">
<title>WDT Competition - Counter</title>
</head>
<center>
<size="30"><strong><? echo $counter; ?></strong></size>
</center>
[/code]

Share this post


Link to post
Share on other sites
I'm not sure I understand what you're trying to accomplish -- please clarify.

Share this post


Link to post
Share on other sites
Sorry about that Fenway, it's a little difficult to explain.

Basically, I want a page to show the current time in real time. Currently, I'm accomplishing this by having a page auto-refresh every 1 second. As a second has passed, the $current_time from my code is augmented by 1 second, and thus it shows an increase of 1 second.

However, as you will note, I run a SELECT query on my database every time this refreshes. This means that every second, I'm asking the database to give me information, and for that period, I'm locking the table.

I was wondering if there is an easier way to do this, that doesn't rely on me constantly querying the database. Because the $start_time never changes, I'd like to be able to get it out of the database once, and then run with it from there. I hope that makes sense..

Share this post


Link to post
Share on other sites
Why can't you do this client-side (e.g. JavaScript)?

Share this post


Link to post
Share on other sites
[!--quoteo(post=379231:date=Jun 1 2006, 10:23 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jun 1 2006, 10:23 PM) [snapback]379231[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Why can't you do this client-side (e.g. JavaScript)?
[/quote]

It is being done client side. Well, the refresh is done using the meta tag after 1 second.

It was more a theoretical capacity issue. "Is it alright to query a database every 1 second" should be more to the question I'm asking. I'm just worried that my database will be overloaded with queries is all. I'm not sure what the average time it takes to run a query is (most of my tables have 60 or less records), but I have to assume that it is somewhere around 1-2 seconds or something like that.

Share this post


Link to post
Share on other sites
A few comments -- DBs can handle lots of queries with lots of connections. However, in principle, LOCK-ing the tables could be problematic because of deadlocks (even though it's a read lock). Also, your first query should simply be a COUNT(*) -- currently, you're retrieving ALL the fields for ALL the records and throwing them out, which isn't optimal. Last, if this was just a proof-of-principle, then it's fine... but as I said earlier, this isn't "client-side" if you hit the DB all the time (and neither is a meta-refresh). I guess I just didn't understand what you were trying to accomplish.

Share this post


Link to post
Share on other sites
No worries. Thanks for the information. I had read that locking tables was a good bet because the queries get queued up and they run in the order they were submitted. Also, I read that if you lock tables, they can't get updated before the query is finished.

What is this 'deadlocking' you speak of?

I hope to show you guys what all this amounts to once I'm done. If you will be in London around March you should come see this thing show off ;). [!--sizeo:1--][span style=\"font-size:8pt;line-height:100%\"][!--/sizeo--]That is London, Ontario of course.[!--sizec--][/span][!--/sizec--]

Share this post


Link to post
Share on other sites
You are right about table locking -- it just has the potential to generate a back-log of queries.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.