detect database changes

Jinks

im looking for a way for a browser to detect a realtime database time.. assumingly using javascript.. has anyone seen one or heard of one? or hell even written one yourself? Help would be greatly appreciated :p

nemonoman

The browser has to pull data from the server...there is no push based on an event such as a db change.

The only way to accomplish this is to refresh/reload a page or frame on a regular interval and check for changes.

This isn't the best way to do business, so perhaps you'd like to restructure the way you frame the problem to see if might suggest a more efficient and workable solution.

Jinks

yeah, i realize it's not a simple problem. The biggest problem is how much load it will take to do it on a somewhat large scale.. bah.

having a page sit there and refresh every second isnt really an option.

Jinks

anyone else have any input on this?

nemonoman

The question is asked and answered.

having a page sit there and refresh every second isnt really an option <<

You're SOL.

psycorpse

if the database is being changed by a webpage then you could write a script to email you. Then when a change has been made you will recieve an email. That is about the only way that I can think of other than the option that was posted earlier. You would have to put the script on every page that updates though.

nemonoman

You'd have to check your email every second, however.

psycorpse

what is the purpose of finding out the database changes?

Jinks

so that people can interact.. at basically realtime in my game :p So you can see why it would be insane to have it refresh every second.. or even every 5 seconds.. my bandwidth would go through the roof.. as it is its about 10-15k a refresh.. doesnt take long to add up.

I just thought there might be a way to run a javascript loop in the background that keeps querying the database.. guess not.

nemonoman

If that's your concern (bandwidth), you can do the refresh by refreshing an iframe on the page. This wouldn't use much bandwidth at all.

The architecture you're describing isn't very different than a chatroom, if you think about it...Why don't you review some available chatroom scripts and see how these manage the refresh problem.

This problem (refreshing for interacting in real time) is different than the question you originally asked: How to update the browser when a database is changed.

You're describing now:
regular refreshes via a browser
looking at a specific table or tables, probably for new records created through a specific application, rather than attempting to see whether there have been any changes to a database (which could include not only record inserts, updates, deletes, but also new user creation, table alterations, etc.).

Jinks

well no.. idealy it would only refresh the page when someone did something that in some way effects you locally.

I have looked at uhm.. phpopenchat... so yay maybe ill just try the iframe refresh.. should be able to make it pretty small.

psycorpse

Not to mention the ideal you had about the javascript wont work because javascript is only client side. It will not query your database or anything.

nemonoman

You can use javascript in conjunction with a hidden iframe to query the database in the background and update the text on the main page.

weedpacket earlier posted this link
http://dynarch.com/mishoo/rpc.epl#gen4

Jinks

yeah unfortunately the only problem with that is you can only update form elements .. you cant update any plain text on the page. unless they came out with something new i havent heard about.

nemonoman

I guess they have:

You can update text contained in <span>, <div> <td> as well as forms.

I do this all the time.

Jinks

ahh frig that's awesome thanks!! I've been looking for something like that forever :/

crimaniak

Here is simple solution used in web chat rooms. In browser you have separate frame (in chat this is messages frame) with opened connection. Yes, when page is loads, connection is opened but not closed. Because on server side you have script with infinite loop. In loop you:

check connection status (and exit when connection broken).
ping browser one in five minutes to prevent timeout. (I am using <ping> tag for it)
Check your database status and send script to browser depends of events
```
if($text=get_new_message())
{
   echo "<script>add_record(\"".jsQuote($text)."\")</script>"
   flush();
}
```
(add_record is javascript function loaded before to browser)
sleep(one_second)

so:
- database is changed
- function get_new_message detect it and return data
- PHP script output javascript to browser
- browser executes javascript and modify data on screen (use DOM to modify any element of page)

One big problem of such method: animation on right top corner! 😉

nemonoman

Do you have an example of this code that you could post?

I can't figure out from your description how this would work.

I don't know how to make a server ping a browser, and I can't find any reference to this in the PHP manual.

Infinite PHP loops on servers are an interesting concept. How do you get around the timeout problem?

I am very interested and excited by your answer.

crimaniak

Originally posted by nemonoman
Do you have an example of this code that you could post?

I can't figure out from your description how this would work.

I don't know how to make a server ping a browser, and I can't find any reference to this in the PHP manual.

Infinite PHP loops on servers are an interesting concept. How do you get around the timeout problem?

I am very interested and excited by your answer.

Fragment stripped from real chat code. You can see this chat on address http://www.crimaniak.com/chat/ but:
1. Russian only
First time you will be prompted to enter your nickname. Fill first field, select IRC in WWW/IRC select control, ignore rest of form, submit form and reload chat page.
2. Please use IE (I didn't update this site long time and don't sure about Mozilla etc.)
3. You can't use this chat throw Apache 1.x proxy module (bottom of page delayed in proxy buffer until buffer is filled).
It's just a chat but you can use this method to organize any work with server-generated events.

Idea was my own but then I found other chat rooms with such method.

		// set id of last printed message
		if(!isset($lid)) $lid=0;
		// infinite loop
		while(!connection_status())
		{	
			// prevent timeout on server, 
			// if PHP is in safe mode you need to talk to admin about this
			set_time_limit(60);
			// extract new messages
			$list=$db->executeQuery('SELECT * FROM ChatMessages WHERE '.WhereName($Name)." AND MessageID>$lid ORDER BY MessageID");
			// no alert need
			$alertFlag=false;
			// for each new message...
			while($m=$list->fetch_object())
			{
				// no comments :)
				printMessage($m);
				// update last message id
				$lid=$m->MessageID;
				// update last output time
				$lastTime=time();
				// new messages - may be alert need
				$alertFlag=true;
			}
			$list->close();
			// send <BGSOUND> tag if need
			if($alertFlag) alertIfNeed();
			// if no messages long time...
			if($lastTime+$server->pingPeriod<time())
			{
				// "ping" client to prevent browser timeout
				echo "<ping/>";
				// update ping count and last output time
				$cnt++;$lastTime=time();
			}
			// update information about active user
			StoreUser($ip,$Name,$Color,$RefreshTime,$session_id);
			// generate diff for list of users and send <script> tags to draw or erase users in users frame
			echo $userList->generateDiffEach(2);
			// flush output and cool CPU
			flush();sleep($server->sleepTime);
		}
		// connection broken -> just exit.
		exit;

To phpbuilder admins: please don't touch PHP code in messages (see string "no comments")

And sorry for my English.

nemonoman

Thanks. That's very interesting code, and very different from what I expected. I apologize for my previous skeptical response.