Javascript popup anytime database is updated

Juan1989 · December 16, 2014

I'm trying to get a simple javascript popup script going anytime the database is updated in real-time. I'm not sure as to what to do next, because I'm still a newbie with jQuery and ajax, but the following code is what I have right now:

PHP MySQL query page:

<?php

$con = mysqli_connect('localhost','root','root','mydb');
if (!$con) {
	die('Could not connect: ' . mysqli_error($con));
}

mysqli_select_db($con,"mydb");
$sql="SELECT * FROM incoming_calls";
$result = mysqli_query($con,$sql);

while($row = mysqli_fetch_array($result)) {
	$callArray[] = array('phonenumber' => $row['phone_number'], 'id' => $row['phone_login_id']);
}
if (!empty($callArray)) {
	//echo json_encode($callArray);
	for ($i = 0; $i < count($callArray); ++$i) {
		print $callArray[$i]['id'] . ": " . $callArray[$i]['phonenumber'] . "<br>";
	}
}

mysqli_close($con);
?>

MySQL "incoming_calls" db table (I have a node.js script, that draws SMDR data from a phone system and posts it to this table):

id	phone_number	phone_login_id	date_created
3	000-000-0000	1225	        12/15/2014 14:53
6	000-000-0000	1222	        12/15/2014 14:53
9	000-000-0000	1202	        12/15/2014 14:53
12	000-000-0000	1201	        12/15/2014 14:55
18	000-000-0000	1232	        12/15/2014 14:55
21	000-000-0000	1222	        12/15/2014 14:57
24	000-000-0000	1222	        12/15/2014 14:57
27	000-000-0000	1201	        12/15/2014 14:58
30	000-000-0000	1207	        12/15/2014 14:58
33	000-000-0000	1212	        12/15/2014 14:59

HTML ajax call page:

<!DOCTYPE html> 
<html lang="en"> 
<head> 
<meta charset="utf-8"> 
<title>Phone calls</title>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
</head>
<body>
<script language="javascript" type="text/javascript">
function getCall(){
  $.get("phonecall.php", function(data){
    $("#call").html(data);
  });
}
setInterval(getCall,5000);
</script>

<div id="call"></div>
</body>
</html>

Any help with this is greatly appreciated! Thanks ahead of time!

Edited December 16, 2014 by Juan1989

Ch0cu3r · December 16, 2014

anytime the database is updated in real-time

What do you mean by update?

Edited December 16, 2014 by Ch0cu3r

Juan1989 · December 16, 2014

What do you mean by update?

Anytime new information is inserted into the table.

Juan1989 · December 16, 2014

Okay what I have now is this, if the json_encode is in the while loop it will return all users with xxx-xxx-xxxx phone number, where if it is not within the while loop it will only return the last user in the table with the phone number:

PHP page:

<?php

$con = mysqli_connect('localhost','root','root','mydb');
if (!$con) {
	die('Could not connect: ' . mysqli_error($con));
}

mysqli_select_db($con,"mydb");
$sql = "SELECT * FROM incoming_calls";
$result = mysqli_query($con,$sql);

while($row = mysqli_fetch_array($result)) {
	//$callArray = array('phonenumber' => $row['phone_number'], 'id' => $row['phone_login_id']);
	if(!empty($row)) {
	$number = $row['phone_number'];
	}
}

$sql="SELECT Username, Password FROM tblUsers WHERE PhoneHome='xxx-xxx-xxxx' OR PhoneCell='xxx-xxx-xxxx' OR PhoneWork='xxx-xxx-xxxx'";
$result = mysqli_query($con,$sql);

while($row = mysqli_fetch_array($result)) {
	$userArray = array("username" => $row['Username'], "password" => $row['Password']);
	//echo json_encode($userArray);
}
	echo json_encode($userArray);

mysqli_close($con);
?>

HTML page:

<!DOCTYPE html> 
<html lang="en-US"> 
<head> 
<meta charset="utf-8"> 
<title>Phone calls</title>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
</head>
<body>
<script language="javascript" type="text/javascript">
function getCall(){
  $.get("phonecall.php", function(data){
    var loginInfo = jQuery.parseJSON(data);
    var user = loginInfo.username;
    var pass = loginInfo.password;
	$('#username').html(user);
	$('#password').html(pass);
  });

/*$.getJSON("phonecall.php", function(data){
    for (var i=0, len=data.length; i < len; i++) {
        $('#username').html(data);
    }
});*/
}
setInterval(getCall,5000);
</script>

<div id="username"></div>
<div id="password"></div>
</body>
</html>

The problem I am having now is that when there are 2 or more users with the same phone number for say like a house phone, depending on where the json_encode is, will either return the last entry in the table, or return nothing at all. If the json_encode is in the while loop, I can check the console, it says the information is being retrieved, but something must not be right with my syntax to allow more than one entry to be displayed. Any ideas?

Edited December 16, 2014 by Juan1989

Ch0cu3r · December 16, 2014

Ok now I am confused... Why are you returning the usernames and passwords? I though you wanted to return the latest phone calls?

The reason why you are only getting the last result is because you are redefining the $userArray within your while loop. What you need to do is add [] (square brackets) to the end of the variable to add each row to the array. This will cause $userArray to contain a multidimensional array containing your data from your query.

In your javascript when you have parsed the json, You would use jquery's $.each method to loop over the data.

Sign In

Javascript popup anytime database is updated

Recommended Posts

Juan1989

Link to comment

Share on other sites

Ch0cu3r

Link to comment

Share on other sites

Juan1989

Link to comment

Share on other sites

Juan1989

Link to comment

Share on other sites

Ch0cu3r

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information