PHP and JavaScript/AJAX database query
I have been running around in circles a bit over the last couple of months, but they have been good circles, because they have enriched my understanding of a number of web related topics. And my current circle has taken me back to the generally excellent and easy to follow W3Schools website. In my post on their AJAX tutorial, I bemoaned that fact that their exchanging data with a server example only read data from a "measly old text file", rather than a database. However, had I dug deeper in my first post on PHP, I might have noticed that the w3schools tutorial on the topic gives an example of communicating with a database, using AJAX and PHP, in one of the advanced sections. I shall now work through that example.
My first step is to cut and paste their exact web code to see if it works. Should I post it here? When I first began this blog, I posted almost no code, but included an array of links to sources. A couple of years later, when I went back to reread some of my early posts, I found that many of my sources had changed their URL or disappeared altogether. So I started posting code. But the question then arises, should the full 30 or more line copyright notice be included with every code snippet. I think not. That would render the blog unreadable. "Fair Practice Law" in most countries allows you to copy snippets, certainly from literature, as long as you acknowledge your source. Would that apply to a full working web page? Possibly not. I shall refrain from quoting it, and apologise if the source subsequently disappears.
Of course I have posted code for some working web pages in my last few entries, (and in fact below) but I think one, of a couple of important questions, is did I copy something and change it, or did I write something of my own and draw inspiration from a source. It is something of a moot point, but I think there are a couple of things to bear in mind. Did I begin with a downloaded file, or did I begin with a clean sheet of paper? In this case I began clean, because of the database communication differences. A second important question is did I use a single source for inspiration? Again, I used more than one source, for practical and cosmetic reasons.
Meanwhile, back in reality, I have cut and pasted the HTML, and it works. And while I am not posting the HTML, I shall post a screen shot (below), because on an empty page it looks a bit different. But it certainly works. I had a quick look at the dropdown box, and the names were all there as in the tutorial. Obviously I refrained from selecting one because I haven't written a PHP file. And this is where I have to put my thinking cap on, because for this to be useful to me, it has to work with my data, and there are no "names" in my database.
Getting it to work for me involved one major change to the HTML, and a few cosmetic changes. The major change was to rewrite the dropdown box from scratch to make it meaningful for my data. The cosmetic changes were to variable names - not strictly necessary, but they always say variable names should try to reflect what they contain.
An important point to note here, for the unwary, is that parameters passed by AJAX seem to in the form of strings. The index field in my database is an integer, so I was tempted to try and pass an integer through AJAX. But from my notes on AJAX the syntax of the open method is:
xmlhttp.open("METHOD","URL",async);
Where URL is, and will always be a string. So in my code (shown below), my attempts to change the idx variable (in the function, and in the dropdown box) to a number were futile, and had to be abandoned. The variable was passed as a string, but the character was included in the SQL statement (shown further below) as a number.
A final point is that I included some diagnostic lines, but these were pretty redundant as PHP errors were picked up in the parse, and SQL errors were also caught by an error trap in the code. After all this, my HTML was:
<html>
<head>
<head>
<title>AJAXPHPexpt1</title>
<script type="text/javascript">
function showItem(idx)
{
if (idx=="")
{
document.getElementById("cbxItem").innerHTML="";
return;
}
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("cbxItem").innerHTML=xmlhttp.responseText;
}
}
xmlhttp.open("GET","getitem.php?id="+idx,true);
xmlhttp.send();
}
</script>
</head>
<body>
<form>
<select name="itemselection" onchange="showItem(this.value)">
<option value="">Select an item:</option>
<option value="2">2+2=</option>
<option value="4">2+3=</option>
<option value="7">4+3=</option>
</select>
</form>
<br />
<div id="cbxItem"><b>Item info will be listed here.</b></div>
</body>
</html>
And my PHP code was:
<?php
$id = $_GET['id'];
include('dbinfo.php');// collect database variables and connect.
$con = mysql_connect( $dbhost, $dbuser, $dbpass );
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db($dbname, $con);
$query = "SELECT * FROM mytable WHERE Itemid=".$id;
$result = mysql_query($query) or die(mysql_error());
echo"<p>The item id is now: $id </p>";
echo"<p>The query is: $query </p>";
echo
'<table style="text-align:center;">
<tr bgcolor="#CCCCCC">
<td width="60"><strong>Itemid</strong></td>
<td width="60"><strong>Partid</strong></td>
<td width="60"><strong>OpCode</strong></td>
<td width="60"><strong>Itemdet</strong></td>
<td width="60"><strong>Raw</strong></td>
<td width="60"><strong>Rate</strong></td>
</tr>';
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['Itemid'] . "</td>";
echo "<td>" . $row['OpCode'] . "</td>";
echo "<td>" . $row['Partid'] . "</td>";
echo "<td>" . $row['Itemdet'] . "</td>";
echo "<td>" . $row['Raw'] . "</td>";
echo "<td>" . $row['Rate'] . "</td>";
echo "</tr>";
}
mysql_close($con);
?>
And after selecting an item, the page was as shown below:
Comments