I am trying to build a multi-user chat system where the data is saved in database and extracted upon request. The following piece of code is just an example and I know it has a lot of nice SQL injections in it which I will change once the whole thing works well. Plus keeping the php server code in the same file is just to keep the code neat in one place, the problem I will explain here was still there when the server code was in a different php file.
The code works well but the problem is that my database stops responding for some time after a while. If more users are chatting at once(multiple instances of this script) are going on, then the database stops responding for some time very soon and it starts responding after some time again. Kindly suggest where I am going wrong.
<?php
if($_POST){
$db=mysqli_connect("localhost","***","****","****");
if ( isset($_POST['update']) ){
$q = 0;
$lasttime = isset($_POST['timestamp']) ? $_POST['timestamp'] : 0;
while (1){
sleep(3);
$mresult = mysqli_query($db,"SELECT * FROM tblchat WHERE msg_datetime > $lasttime");
$wresult = mysqli_query($db,"SELECT writer_alias FROM tblwriter WHERE writer_isactive=1 AND (UNIX_TIMESTAMP(NOW())-last_activity)<10");
if (mysqli_num_rows($mresult)){ $msgs = array(); while ($row = mysqli_fetch_object( $mresult )) { $msgs[] = $row; } mysqli_free_result($mresult); echo json_encode(array("writers"=>$writers,"msgs"=>$msgs)); flush(); break; }
if (mysqli_num_rows($wresult)){ $writers = array(); while ($row = mysqli_fetch_object( $wresult )) { $writers[] = $row; } mysqli_free_result($wresult); echo json_encode(array("writers"=>$writers,"msgs"=>$msgs)); flush(); break; }
++$q;
if ($q>15){ break; }
}
}elseif ( isset($_POST['save']) ){
$msg = isset($_POST['msg']) ? $_POST['msg'] : '';
if ($msg != ''){
$from = $_POST["from"];
$to = $_POST["to"];
mysqli_query($db,"INSERT INTO tblchat VALUES('".$to."','".$from."','".$msg."','".time()."')");
echo json_encode(array("success"=>"1"));
flush();
}
}
mysqli_close($db);
exit();
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>testing comet</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></script>
</head>
<body>
<p>
<input type="text" name="word" id="word" value="" />
<input type="button" name="send" value="Send" id="mybutton"/>
</p>
<div id="content"></div>
<script type="text/javascript">
var lastime = 0;
$("#mybutton").click(function(){
$.post(location.href, {save:"1",from:"1",to:"5",msg:$("#word").val()}, function(data){
console.log(data);
$("#word").val("");
}, "json");
});
$(document).ready( function(){ update(); } );
function update(){
console.log("update called");
$.ajax({ type: "POST", url: location.href, data: {update:"1",timestamp:lastime}, success: function(data1){
console.log(data1);
lastime = handleDATA(data1.msgs);
}, dataType: "json", complete: update, timeout: 60000 });
}
function handleDATA (data){
for(i=0;i<data.length;i++){
$("#content").append(data[i].msg_from +": "+ data[i].msg+"</br>");
}
return data[data.length-1].msg_datetime;
}
</script>
</body>
</html>
While this may or may not be the absolute solution, let's fix some things with your code (as well as some formatting to help illustrate better):
Old Section (Lines 7 - 15):
while (1){
sleep(3);
$mresult = mysqli_query($db,"SELECT * FROM tblchat WHERE msg_datetime > $lasttime");
$wresult = mysqli_query($db,"SELECT writer_alias FROM tblwriter WHERE writer_isactive=1 AND (UNIX_TIMESTAMP(NOW())-last_activity)<10");
if (mysqli_num_rows($mresult)){ $msgs = array(); while ($row = mysqli_fetch_object( $mresult )) { $msgs[] = $row; } mysqli_free_result($mresult); echo json_encode(array("writers"=>$writers,"msgs"=>$msgs)); flush(); break; }
if (mysqli_num_rows($wresult)){ $writers = array(); while ($row = mysqli_fetch_object( $wresult )) { $writers[] = $row; } mysqli_free_result($wresult); echo json_encode(array("writers"=>$writers,"msgs"=>$msgs)); flush(); break; }
++$q;
if ($q>15){ break; }
}
New Function at Top:
// This is separate of the while below, put this
// function at top of your script (less code later)
function toArray( myqli_result $result )
{
while( $row = mysqli_fetch_object( $result ) )
{
$array[] = $row;
}
mysqli_free_result( $result );
return $array;
}
New While Statement:
//Replacement While Function
while( $q < 15 )
{
sleep(3);
$mresult = mysqli_query( $db, "SELECT * FROM tblchat WHERE msg_datetime > $lasttime" );
$wresult = mysqli_query( $db, "SELECT writer_alias FROM tblwriter WHERE writer_isactive = 1 AND ( UNIX_TIMESTAMP( NOW() ) - last_activity ) < 10" );
//If you need both results, you must check for both results
//And do what you need to do, BEFORE break;
if ( mysqli_num_rows( $mresult ) > 0 && mysqli_num_rows( $wresult ) > 0 )
{
$msgs = toArray( $mresult );
$writers = toArray( $wresult );
echo json_encode( array( "writers" => $writers, "msgs" => $msgs ) );
flush();
break;
}
++$q;
}
Clean code makes happy programmers, and also makes it much easier to debug later.
If you need both result sets (as it seems you do) you won't get both of them as your code is now, you'll break out of it when you get your first result set back. This will allow both queries to be processed before the json_encode.
Here, toArray( mysqli_result $result ) is a function that will only accept a mysqli_result object generate and return the array, while freeing the result.
If you find yourself writing the same lines of code several times with different variables, you might want to create a function for it instead.