DaVuLf Posted June 1, 2006 Share Posted June 1, 2006 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 hourif($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] Quote Link to comment Share on other sites More sharing options...
fenway Posted June 1, 2006 Share Posted June 1, 2006 I'm not sure I understand what you're trying to accomplish -- please clarify. Quote Link to comment Share on other sites More sharing options...
DaVuLf Posted June 1, 2006 Author Share Posted June 1, 2006 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.. Quote Link to comment Share on other sites More sharing options...
fenway Posted June 2, 2006 Share Posted June 2, 2006 Why can't you do this client-side (e.g. JavaScript)? Quote Link to comment Share on other sites More sharing options...
DaVuLf Posted June 2, 2006 Author Share Posted June 2, 2006 [!--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. Quote Link to comment Share on other sites More sharing options...
fenway Posted June 2, 2006 Share Posted June 2, 2006 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. Quote Link to comment Share on other sites More sharing options...
DaVuLf Posted June 3, 2006 Author Share Posted June 3, 2006 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--] Quote Link to comment Share on other sites More sharing options...
fenway Posted June 3, 2006 Share Posted June 3, 2006 You are right about table locking -- it just has the potential to generate a back-log of queries. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.