I've recently started exploring using AJAX for a small project, and I've had reasonable success though it's not as smooth as I would like.
The Basic setup is that an applications named ProphetX which interfaces with Excel to show stock market prices. Prices are updated as they change in Excel. Using VBA I save the data from the spreadsheet into an SQL08 DB every time a price updates. This could sometimes be a few times per second.
Using PHP on an Apache server I connect to the SQL DB and load the data into tables, and a javascript function to keep updating the information once every second. I've noticed however that at times the page will simply hang if you already have it up, or load a blank screen if you pull it up, particularly when data is being updated rapidly. So to the meat of my question: Is there something in my code which could be causing this hiccup? I doubt it is congesting the network or server resources as I've been monitoring them and they seem low.
Also I used WireShark to monitor network traffic, and when I load up the page and it shows blank in the browser, I can see the HTML being sent from the server to my machine.
Any help / coding style criticism is much appreciated, source code below.
Index.php:
<html>
<head>
<script type="text/javascript" src="update.js"></script>
</head>
<body onLoad = update()>
<font size = +2>
<?php
echo "
<div id = 'marketData'></div>
";
?>
</font>
</body></html>
Update.js:
function update()
{
if (window.XMLHttpRequest)
{// code for IE7+, Firefox, Chrome, Opera, Safari
xmlhttp=new XMLHttpRequest();
}
else
{// code for IE6, IE5
xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
}
xmlhttp.onreadystatechange=function()
{
if (xmlhttp.readyState==4 && xmlhttp.status==200)
{
document.getElementById("marketData").innerHTML=xmlhttp.responseText;
}
}
//URL needs a var to be passed via get for code to function in IE, thus Math.Random().
//I am also confused by this requirement.
xmlhttp.open("GET","update.php?i="+ Math.random(),true);
xmlhttp.send();
var t = setTimeout("update()", 3000);
}
Update.php:
<?php
//connect to database
error_reporting(0);
sqlsrv_configure("WarningsReturnAsErrors", 1);
$server = "myServer";
$db = "myDB";
$connectionInfo = array("Database"=>"$db, "UID"=>"user", "PWD"=>"pass");
$conn = sqlsrv_connect($server, $connectionInfo);
if($conn)
{
echo "<font size =-1 color=green>Connection Established<br></font>";
}
else
{
echo"Connection not established:<br>";
print_r(sqlsrv_errors());
}
//Func calls sqlsrv_query($conn, [$symbol],[$DatabaseName])
$stmt = sqlsrv_query($conn,query(array('sym1','sym2','sym3'), "electronic") );
errorCheck($stmt);
printTables("Electronic Commodity Prices", $stmt);
$stmt = sqlsrv_query($conn,query(array('sym1','sym2','sym3'), "floor") );
errorCheck($stmt);
printTables("Floor Commodity Prices", $stmt);
$stmt = sqlsrv_query($conn,query(array('sym1','sym2','sym3',... ,sym19), "natgas") );
errorCheck($stmt);
printTables("Natural Gas Commodity Prices", $stmt);
sqlsrv_free_stmt($stmt);
sqlsrv_close( $conn);
//This function prints out the tables
function printTables($tableName, $stmt)
{
echo
"
$tableName<hr>
<table cellspacing ='5' cellpadding = '5'>
<tr>
<th>Symbol</th>
<th>Product</th>
<th>Last Price</th>
<th>Change</th>
<th>High Price</th>
<th>Low Price</th>
<th>Previous Price</th>
<th>Trade Time</th>
</tr>
";
while($row=sqlsrv_fetch_array($stmt))
{
echo
"
<tr>
<td>$row[symbol]</td>
<td><font size =+3 color = blue>$row[description]</font></td>
<td>$row[last]</td>
<td><font size =+5> $row[change]</font></td>
<td>$row[highPrice]</td>
<td>$row[lowPrice]</td>
<td>$row[previousprice]</td>
<td>" . date("j M g:i",strtotime($row['tradetime'])) . "</td>
</tr>
";
}
echo"</table><hr>";
}
function query($symbols, $db)
{
$count = count($symbols);
$stmt =
"
select distinct id,symbol,description,last,change,highPrice,lowPrice,previousprice,tradetime from $db
where ";
for($i = 0; $i< $count; $i++)
{
$stmt .= "id in (select MAX(id)from $db where symbol ='$symbols[$i]') ";
if($i != $count-1)
{
$stmt.= "or ";
}
}
$stmt .= "order by description asc";
// id in (select MAX(id)from $db where symbol ='$symbols[0]')
// or id in (select MAX(id)from $db where symbol ='$symbols[1]')
// or id in (select MAX(id)from $db where symbol ='$symbols[2]')
// order by description asc
return $stmt;
}
function errorCheck($stmt)
{
if( $stmt=== false )
{
echo "Error in statement preparation/execution.\n";
die( print_r( sqlsrv_errors(), true));
}
}
?>