Jump to content


Photo

Page Refresh With Query.


  • Please log in to reply
7 replies to this topic

#1 DaVuLf

DaVuLf
  • Members
  • PipPipPip
  • Advanced Member
  • 124 posts

Posted 01 June 2006 - 08:50 PM

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:

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


#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 01 June 2006 - 09:12 PM

I'm not sure I understand what you're trying to accomplish -- please clarify.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 DaVuLf

DaVuLf
  • Members
  • PipPipPip
  • Advanced Member
  • 124 posts

Posted 01 June 2006 - 09:16 PM

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

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 02 June 2006 - 02:23 AM

Why can't you do this client-side (e.g. JavaScript)?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 DaVuLf

DaVuLf
  • Members
  • PipPipPip
  • Advanced Member
  • 124 posts

Posted 02 June 2006 - 01:18 PM

[!--quoteo(post=379231:date=Jun 1 2006, 10:23 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jun 1 2006, 10:23 PM) View Post[/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.

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 02 June 2006 - 02:58 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 DaVuLf

DaVuLf
  • Members
  • PipPipPip
  • Advanced Member
  • 124 posts

Posted 03 June 2006 - 04:24 AM

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



#8 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 03 June 2006 - 04:31 PM

You are right about table locking -- it just has the potential to generate a back-log of queries.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users