Querying a MySQL Database ~ Ajax Training, Learn Ajax Video Tutorials Online, Ajax Examples
Ajax Training, Learn Ajax Video Tutorials Online, Ajax Examples: Querying a MySQL Database

Querying a MySQL Database

In order to make a valid query to a database table, the table must first be there. Let’s create a table called block that has the purpose of storing a random word. The following SQL code (the language that MySQL uses to perform actions) will create the table:

CREATE TABLE block (
blockid INT AUTO_INCREMENT PRIMARY KEY,
content TEXT
);

Now that you have a valid table named block created, you can go ahead and insert some data using SQL once more. Consider the following code to insert eight random words into your block table:

INSERT INTO block (content) VALUES ('frying');
INSERT INTO block (content) VALUES ('awaits');
INSERT INTO block (content) VALUES ('similar');
INSERT INTO block (content) VALUES ('invade');
INSERT INTO block (content) VALUES ('profiles');
INSERT INTO block (content) VALUES ('clothes');
INSERT INTO block (content) VALUES ('riding');
INSERT INTO block (content) VALUES ('postpone');

Now that you have a valid table set up and information stored within that table, it is time to work with Ajax and PHP to perform a query to the database dynamically and without any page refreshing. Ajax functionality can be triggered based on different events. Certainly, a common event (basically, an action that can be “captured” to execute code) to trigger Ajax code can come from the onclick event. The reason this event proves so useful is because many HTML objects allow this event to be fired. By making use of the onclick event, you can achieve some pretty interesting functionality. Consider the following block of code, which will randomly grab a word from your database of random words and populate it into the element that was clicked. When the page first loads, sample4_1.html

Now have a look at the following code for sample4_1.html. You will notice that each block has an onclick event registered for it. This is the action that will trigger your Ajax functionality.





<title>Sample 4_1</title>
<meta equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link rel="stylesheet" type="text/css" href="style.css">
<script type="text/javascript" src="functions.js"></script>

<div class="dborder" id="">" onclick="grabword (this.id)"></div>

Now, when any of the boxes are clicked, they fire a function called grabword, which accepts the current object’s id as an argument. This is the function that will run an Ajax request to either populate the box or, if the box is already populated, make the box empty again. The following JavaScript function (contained within functions.js) will perform the functionality for you.

//functions.js
//Create a boolean variable to check for a valid Internet Explorer instance.
var xmlhttp = false;
//Check if we are using IE.
try {
//If the javascript version is greater than 5.
xmlhttp = new ActiveXObject("Msxml2.XMLHTTP");

} catch (e) {
//If not, then use the older active x object.
try {
//If we are using IE.
xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
} catch (E) {
//Else we must be using a non-IE browser.
xmlhttp = false;
}
}
//If we are using a non-IE browser, create a javascript instance of the object.
if (!xmlhttp && typeof XMLHttpRequest != 'undefined') {
xmlhttp = new XMLHttpRequest();
}
//Function to run a word grabber script.
function grabword (theelement){
//If there is nothing in the box, run Ajax to populate it.
if (document.getElementById(theelement).innerHTML.length == 0){
//Change the background color.
document.getElementById(theelement).style.background = "#CCCCCC";
serverPage = "wordgrabber.php";
var obj = document.getElementById(theelement);
xmlhttp.open("POST", serverPage);
xmlhttp.onreadystatechange = function() {
if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
obj.innerHTML = xmlhttp.responseText;
}
}
xmlhttp.send(null);
} else {
//Change the background color.
document.getElementById(theelement).style.background = "#FFFFFF";
//If the box is already populated, clear it.
document.getElementById(theelement).innerHTML = "";
}
}

You first create an XMLHttpRequest object and then check to see if the box already has content. If the box is already filled with content, the grabword function merely sets the innerHTML property of the object to blank. If it is empty, however, the function makes an Ajax request to populate the box with the results of the output from the wordgrabber.php file. Let’s have a look at the wordgrabber.php file to see how the query is executed:

The PHP script first requires the database connection script built in the previous code block (dbconnector.php), and then calls the opendatabase function to allow a valid connection to the database. From there, you simply build a SQL query to grab the content of a random word from your block table. Last, the content is outputted;
shows the effects of clicking and unclicking the different boxes.

Related Posts by Categories

2 comments:

Anonymous said...

Is there any way that an AJAX object can get back a record set?

Anonymous said...

YES, You could build an XML document out of your recordset and send that back to the server, you could build an xml document and add as many records as you have in your recordset, then once the client has received it you can use javascript to iterate over the ’s in the xml.

Useful Links on Adobe Flex

Your Ad Here

Latest Books on Adobe Flex