Monitor New Records 1

[ArtWerk]

I need to be able to monitor new records in a table (in a live way).

The easiest way I figured to do that would be to run a loop and when the total number of records in a table changes, then proceed with whatever else the page needs to do, then, just start over.

The way I went about doing this is:

$sessionid = $_GET['session'];
mysql_select_db($database, $connection);
$query_getsession = "SELECT * FROM dialog WHERE `session` = '".$sessionid."'";
$getsession = mysql_query($query_getsession, $connection) or die(mysql_error());
$totalRows_getsession = mysql_num_rows($getsession);

$query_checksession = "SELECT * FROM dialog WHERE `session` = '".$sessionid."'";

for($i=0;$i<=3;$i++){
	mysql_select_db($database, $connection);
	$checksession = mysql_query($query_checksession, $connection) or die(mysql_error());
	$totalRows_checksession = mysql_num_rows($checksession);
	if($totalRows_checksession > $totalRows_getsession){
		$i = 5;
	} else {
		$i = 1;
	}
}

This pretty much works, but i'm nervous about this page going through a possibly infinite loop. Is this way dangerous or stupid? And if so, by what other means would be better to handle this?

I'm not familiar with AJAX to know a way to do it that way.

 

[jpadie]

at a brief glance, it doesn't look like your code will run infinitely.

using a select * query is not a good idea. use select count(*) if you must go this way.

but overall this doesn't feel like a clever thing to do. can you tell us why you need to monitor your tables and whether you have access to the site overall and can therefore do whatever you need to do by changing the code for your update/insert queries?

 

[ArtWerk]

i'm building a php/mysql chat script and i have 1 iframe that monitors when a new message has been added to the dialog. when it realizes that there is a new message for that session, it refreshes the dialog window. This way the dialog window doesn't have to keep refreshing every X seconds. It only updates when a new message has been inserted. The reason i have to do it this way is because when an admin is connected to the same session and they enter a message, there's no way to tell the client's machine to refresh.

 

[jpadie]

i'd do this in ajax for sure. i'm not sure that i'd use a database for storing the chat either. flat file's may be just as good: do some tests to decide which is the best response.

don't monitor the database tho. this seems pointless. just:

have the browser call a js function every couple of seconds that performs an ajax request to the server. have the server script return the database records that are "younger" than a certain timestamp held in a session variable as the last successful request with data being returned.

something like this would be ok server code

session_start();
echo handle_ajax_request(); //calls the function and returns the data to the request
function handle_ajax_request(){
 //connect to db and select database
if (!isset($_POST['ajax_request'])){
 return "";
}
$timestamp = isset($_SESSION['lastsuccessful']) ? $_SESSION['lastsuccessful'] : (time() - 600); //defaults to last ten minutes
$query = "Select poster_name, post_Text from posts where post_Timestamp < '".$timestamp."' order by post_Timestamp asc";
$chats = @mysql_query($query);
if (mysql_num_rows() > 0) {
  $_SESSION['lastsuccessful'] = time();
  $return = "";
  while ($chat = mysql_fetch_assoc($chats)){
   $return .= "<tr><td>{$chat['poster_Name']}</td><td>{$chat['poster_Text']}</td></tr>";
  }
  return $return;
}

let us know whether you need help with the javascript/ajax side of things. check out sajax:

http://www.modernmethod.com/sajax/

for a really easy toolkit to start developing interactive ajax applications. there is even a ready built chat-wall that you could build on.

 

[ArtWerk]

excellent, i'll take a look at all of this. If i have any questions, i'll move it over to the ajax forum.

One thing i did notice about the chatwall is that while i was typing my message, there was a slight "lag" every few seconds. I guess that was caused when it was performing it's server connections, but it was very annoying. I hope it doesn't do that with mine, or I might just stick to the way it's being handled now.

 

[jpadie]

i had to write something similar this afternoon for an application that i am developing. for what it's worth here is the code:

the sql table:

CREATE TABLE `posts` (
  `post_ID` int(11) NOT NULL auto_increment,
  `post_Name` varchar(255) NOT NULL,
  `post_Text` longtext NOT NULL,
  `post_Timestamp` varchar(30) NOT NULL,
  PRIMARY KEY  (`post_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

the code (self-processing form)

<?
define("HOSTNAME", "		");
define("DBUSERNAME", "		");
define("DBPASSWORD", "		");
define("DATABASENAME", "	");

if (isset($_POST['ajax_request'])):
	switch ($_POST['ajax_request_type']):
	case "post":
		echo postData();
		exit();
	break;
	case "update":
		if (($return = handle_ajax_request()) !== false):
			if (empty($return)):
				exit();
			else:
				echo "lastsuccessful=".time()."; var txt = '".ajax_escape($return)."';";
				exit();
			endif;
		else:
			exit();
		endif;
	break; 
	endswitch;
endif;
function ajax_escape($val) {
	//adapted from sajax
	$in = array("\\", "\r","\n","'",'"');
	$out = array("\\\\", "\\r", "\\n", "\\'", '\\"');
	return str_replace($in, $out, $val);
}
function dbconnect(){
	mysql_connect(HOSTNAME,DBUSERNAME,DBPASSWORD);
	mysql_select_db(DATABASENAME);
}
function postData(){
	dbconnect();
	if (empty($_POST['post_Name']) || empty($_POST['post_Text'])):
		exit();
	endif;
	$query = "
			Insert into posts
			set
				post_Timestamp = '".time()."',
				post_Name = '".mysql_real_escape_string(trim($_POST['post_Name']))."',
				post_Text = '".mysql_real_escape_string(nl2br(trim($_POST['post_Text'])))."'";

	$result = mysql_query($query);
	if (!$result) {
		return $query;
			
	} else {
		return true;
	}			
}

function handle_ajax_request(){
	//connect to db and select database
	dbconnect();
	$timestamp = !empty($_POST['lastsuccessful']) && $_POST['lastsuccessful'] !== "undefined"
					? $_POST['lastsuccessful'] 
					: 0; //defaults to last one hour
	
	$query = "
				Select 
					post_Name, post_Text 
				from 
					posts 
				where 
					post_Timestamp > '".$timestamp."' 
				order by 
					post_Timestamp desc";
	
	//echo $query;
	$chats = @mysql_query($query);
	
	if (@mysql_num_rows($chats) > 0):
		$return = "";
		while ($chat = mysql_fetch_assoc($chats)):
			$return .= 
<<<STR
	<div class="chat_row"><span class="name">{$chat['post_Name']}</span><span class="post">{$chat['post_Text']}</span></div> 
STR;
	  endwhile;
	  return $return;
	 endif;
}

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "[URL unfurl="true"]http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">[/URL]
<html xmlns="[URL unfurl="true"]http://www.w3.org/1999/xhtml">[/URL]
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Chat Box</title>
<script type="text/javascript">
var cb, objInterval, lastsuccessful;
function init_object() {
	//take from sajax
	var A;
	var msxmlhttp = new Array(
		'Msxml2.XMLHTTP.5.0',
		'Msxml2.XMLHTTP.4.0',
		'Msxml2.XMLHTTP.3.0',
		'Msxml2.XMLHTTP',
		'Microsoft.XMLHTTP');
	for (var i = 0; i < msxmlhttp.length; i++) {
		try {
			A = new ActiveXObject(msxmlhttp[i]);
		} catch (e) {
			A = null;
		}
	}
	
	if(!A && typeof XMLHttpRequest != "undefined")
		A = new XMLHttpRequest();
	return A;
}
function sendUpdateRequest(){
    //initialise variables
	var strResult, pd;
	
	//check if an xmlhttprequest object exists. if not create one
	var objHTTP = init_object();
	//open the object in post
	
	objHTTP.open('POST',"chatbox.php",false);
    //set the headers
	objHTTP.setRequestHeader("Method", "POST " + "chatbox.php" + " HTTP/1.1");
    objHTTP.setRequestHeader('Content-Type','application/x-[URL unfurl="true"]www-form-urlencoded');[/URL]
    //push in the data that tells the server to give data
	pd = "ajax_request=true&ajax_request_type=update&lastsuccessful="+lastsuccessful;
	//send the request to the server
    objHTTP.send(pd);
	
    //grab the results
	strResult = objHTTP.responseText;
	//if the return value is more than 0 chars then push it on to the page
	if (strResult.length > 0) {	
		insert_into_div (strResult);
	}
}
function postMe(){
	//build the post data
	var pd = "post_Name="+document.getElementById("post_Name").value + "&post_Text="+ document.getElementById("post_Text").value+"&ajax_request=true&ajax_request_type=post";
	var objHTTP = init_object();
	//open the object in post
	
	objHTTP.open('POST',"chatbox.php",false);
    //set the headers
	objHTTP.setRequestHeader("Method", "POST " + "chatbox.php" + " HTTP/1.1");
    objHTTP.setRequestHeader('Content-Type','application/x-[URL unfurl="true"]www-form-urlencoded');[/URL]
    //push in the data that tells the server to give data
	//send the request to the server
    objHTTP.send(pd);
    //grab the results
	strResult = objHTTP.responseText;
	//if the return value is more than 0 chars then push it on to the page
	if (strResult == true){
		document.getElementById("post_Text").value = "";
	}	
}
function insert_into_div (str){
	eval(str);
	if (!cb){
		cb = document.getElementById("chatbox");
	}
	cb.innerHTML =  txt + cb.innerHTML;
}
function updateInterval(t){
	clearInterval(objInterval);
	if (t > 0)
		objInterval = setInterval("sendUpdateRequest()", (t * 1000));
}
window.onload = function(){
	objInterval = setInterval("sendUpdateRequest()", 5000);//5 second intervals
	sendUpdateRequest();
}
</script>
<style>
body, input, button, textarea {
	font-family:"Trebuchet MS", Arial;
	font-size:12px;
}
textarea, input {
	width: 70%;
}
textarea {
	height: 80px;
}
#chatbox {
	background-color:#CCFFCC;
	width: 60%;
	border:dotted red 1px;
}
#form {
	width: 60%;
	border:dotted red 1px;
	background-color:#CCCCCC;
	margin-bottom:10px;
	padding-top: 10px;
}
.name{
	clear:both;
	float:left;
	width: 20%;
}
.post {
	float:right;
	text-align:left;
	width: 79%;
}
#updatediv {
	margin-bottom: 10px;
}
</style>
</head>

<body>
<div id="updatediv">Update screen every:<input type="text" id="updateTime" name="updateTime" value="5" onchange="updateInterval(this.value);" style="width:2.5em; text-align:center;"/> seconds (0 to freeze)
</div>
<div id="form">
	<span class="name">
	Your Name:
	</span>
	<span class="post">
	<input type="text" name="post_Name" id="post_Name" />
	</span>
	<span class="name">
	Your post:
	</span>
	<span class="post">
	<textarea name="post_Text" id="post_Text"></textarea>
	</span>
	<span class="name">
	<button onclick="postMe();">Post</button>
	</span>
	<div style="clear:both;">&nbsp;</div>
</div>

<div id="chatbox">
<div style="clear:both; visibility:hidden; line-height:0px;">&nbsp;</div>
</div>
</body>
</html>