Creating an Ajax Process Using PHP and Oracle

by Larry Ullman

Use the power of JavaScript to add seamless database interactions to your Web pages.

Published February 2007

For some time now, Ajax (Asynchronous JavaScript and XML), has been all the rage in the Web development world. Coming to the forefront with some of Google's features (Suggest, Maps, Gmail, and so on), Ajax performs server requests without the user having to submit a form or click on a link. In other words, the Web browser can make the server request, and handle the response, without the user doing anything or even knowing that this is happening. The immediacy that Ajax brings is not only a great convenience, but it can also be downright cool.

In this recipe, I discuss all the code necessary to use Ajax to go from a simple Web page to a JavaScript function to an XMLHttpRequest to a PHP script and, finally, to an Oracle database. Along with the code, I do talk about the individual pieces with respect to the whole picture: what each chunk does and why it's important. By reading this HowTo, you will acquire not only some sample code but also, hopefully, a broader understanding of the whole Ajax concept.

Background

The example I'll be working with will be a registration form (or a minimal part of it, anyway). The theory is that I must confirm unique email addresses in the users table so that people cannot register multiple times using the same address. This is normally part of the validation process that comes after the user submits the form to the handling PHP script. But why should the user have to wait until then for this piece of validation? Instead, as soon as the user enters their email address and goes to the next form field, the email address will be immediately validated. But to do so, I still need to query the database, which is where Ajax comes in.

The simplified table structure for this example could be created with the following SQL statement (without the presence of the other tables, I've done away with identifying keys and such).

CREATE TABLE users (
 email VARCHAR2(60)
)

Obviously you could expand on this in many ways. For now, though, the important thing is that the sample code assumes you have established such a table and can connect to it from a PHP script. Some records must be in this table for the example to fully function, so you should populate it like so:

INSERT INTO users (email) VALUES ('this@that.com')
INSERT INTO users (email) VALUES ('me@school.edu')
INSERT INTO users (email) VALUES ('fake@address.net')

After you've created and populated the table, you can begin coding. As I mentioned, there are two scripts involved. To develop and test everything, you will:

Create the PHP script that queries the Oracle database.
Manually test the PHP script to see how it works.
Write the JavaScript code that interacts with the PHP script.
Make the HTML form that ties into the JavaScript.
Test the entire process.

Step 1: Programming the Database Query

The entire PHP script is called ajax.php. (See sample code zip.) The purpose of this script is to run a query in Oracle and print a message based upon the results of the query. This is all very basic Oracle, SQL, and PHP, but I'll run through it to clarify what's happening.

The script expects to receive an email address in the URL, which is available in the variable $_GET['email']. Then the script connects to the Oracle database. The query itself counts how many records in the users table have that email address:

SELECT COUNT(*) AS NUM_ROWS FROM users WHERE email='{$_GET['email']}'

This should return either 0 or 1 (or nothing at all). The query is executed in Oracle and the results are bound to the PHP $rows variable. Now $rows represents how many records in the database have that email address. With respect to Ajax, the important part of the script is shown below.

if ($rows > 0) {
 echo 'Email address has already been registered!';
} else {
 echo 'Email address is available!';
}

One of those two messages will be printed, based upon the value of $rows. That's all there is to this page! Absolutely no HTML is needed as this script won't be used like a standard Web page.

If you want to improve this script, you can confirm that the email address matches a regular expression pattern for email addresses. If so, execute the query like normal; if not, echo a statement saying that it's not a valid email address. At the very least, you'd likely want to prevent SQL injection attacks. (I.e., avoid using the $_GET variable in your query without some assurances of its validity.)

Step 2: Testing the PHP Script

Because this entire Ajax process involves several technologies—HTML, JavaScript, PHP, SQL, and Oracle—you'll be best off testing each piece as you go. If you don't, you can easily get lost trying to find where a problem exists. Testing this PHP script should be fairly easy:

Make sure you've created the table in Oracle and populated it.
Edit ajax.php so that it uses valid connection parameters for your Oracle installation.
Save the script as ajax.php.
Place it in the proper directory for your Web server.
Go to http://yoururl/ajax.php?email=X in your Web browser.

In place of X, use one of the email addresses that's already in the database (e.g., http://yoururl/ajax.php?email=this@that.com). You should see the "Email address has already been registered!" message. Then use an email address that has not been stored. You should see the "Email address is available!" text. Once you have that working, you can move on to the actual Ajax aspect of this example.

Step 3: Programming the JavaScript

This section of the process is probably the most difficult unless you've done tons of JavaScript already. In any case, the JavaScript code is the heart of the Ajax process, as it performs and handles the request from the PHP page. I'll go through this code in detail.

The JavaScript code will define three functions. The first creates a request object variable:

function createRequestObject() {
 var ro;
 if (navigator.appName == "Microsoft Internet Explorer") {
  ro = new ActiveXObject("Microsoft.XMLHTTP");
 } else {
  ro = new XMLHttpRequest();
 }
 return ro;
}

You should be able to use this code for any Ajax application without modification. If the Web browser being used is Microsoft Internet Explorer, then the ro variable is initialized as an ActiveXObject of type Microsoft.XMLHTTP. For all other browsers, ro is a straightforward XMLHttpRequest type of object. That's really nothing else to it. The ro variable is now an object with all the necessary functionality to perform an asynchronous request. After creating the request object variable, this function then returns it.

This function is immediately called by the JavaScript code when the page is first loaded:

var http = createRequestObject();

Now http represents the object and is globally available in the other functions.

Next up is the function that calls the PHP script:

function sendRequest(email) {
 http.open('get', 'ajax.php?email=' + encodeURIComponent(email));
 http.onreadystatechange = handleResponse;
 http.send(null);
}

This function takes one argument, which is the email address to be verified. Then it opens a connection to the PHP script. As the first argument in the open() method indicates, the request will be of type get, as opposed to post. The email address is appended to the URL, resulting in a page request like ajax.php?email=this@that.com. As you already know, this is how the PHP script is properly called. The encodeURIComponent() function just encodes the submitted value to make it URL-safe. The third line within the function tells the object what JavaScript function to call after the request has been made. The final line actually makes the request.

To recap, the first function, createRequestObject(), makes the object variable that's needed. The second function, sendRequest(), makes the actual request of the PHP script. Now we need a function that handles that request:

function handleResponse() {
 if (http.readyState == 4) {
  document.getElementById('email_label').innerHTML = http.responseText;
 }
}

The previous function told the request object to call this function after the request has been made. This function first checks that the request was successfully made. If so, then http.readyState will be equal to 4. It could also be equal to 0 through 3 (you can search online for their actual meanings, if you care), but being equal to 4 means the request worked. The results of the request will be stored in the http.responseText attribute, the results being either of the two messages echoed by the PHP script. To be clear, if the request worked, then http.responseText will either be "Email address has already been registered!" or "Email address is available!".

At this point, the JavaScript knows what the result of the PHP script was. All you need to do is get the JavaScript to notify the user of that result. An easy option would be to use an alert() box:

alert(http.responseText);

If you put this within the conditional, you'll see the result when you test the whole application. (In fact, alert() is a great debugging tool to confirm what's going on in your JavaScript code.) However, I find these alerts to be annoying as an end user and would rather print the message next to the form's email input. To do so, I refer to the DOM (Document Object Model). Specifically, I assign the http.responseText to an element in my page called email_label. You'll see this in Step 4.

Whew! Hopefully you followed all that. To repeat, this is the most important and most complex part of this sequence. The JavaScript uses three functions to do three things: define a browser-specific request object; make a request to the PHP script; and, do something with the results of the request. If you're confused by any of this, or when it comes time to make a more complex Ajax application, you'll likely want to do a little research on JavaScript and DOM.

Step 4: Making a JavaScript-functional HTML Form

As I wrote previously, the premise behind this example is a registration form that takes, at the least, an email address. The minimal form would be:


Email Address: 

First Name: 

(Rest of the form...)

That's it! Well, not quite. You'd want more inputs (a "submit" button, and so on). But for the purposes of this example, that's all you need. To avoid confusion, note that the action and method attributes here have nothing to do with the ajax.php script (which doesn't handle this form's submission).

Two more things are required, though. First, this form must somehow call the JavaScript code that makes the request. You could use several tricks to do that: make a clickable link, wait for the submit button to be clicked, and so on. I've decided that once the user has entered an email address and has gone on to the next form element, the JavaScript should be called. This requires the onchange() method, which I'll add to the email input:

Email Address: 
onchange="sendRequest(this.form.email.value)" />

As soon as the user changes the value in this input, including entering anything for the first time, and then tabs or clicks to another input, the JavaScript sendRequest() function will be called. It is fed one argument, the value of the email form input. If you look back at the JavaScript code, you can see how the value typed here gets sent to that function, which in turn is sent to the PHP script.

The second thing the HTML form needs is a place where JavaScript can "write" the received message so that it's visible to the user. As the handleResponse() function dictates, that message will be assigned to the innerHTML of an element called email_label. For this I add a span with that ID after the email address input:

That's it for tying the HTML form to the JavaScript. (See Listing 1 in the sample code zip for the final code.) Now it's time to see how it works!

Step 5: Testing the Ajax Process

Having completed the HTML and JavaScript, you should save that as ajax.html (or anything, really), then place it in the same directory as the PHP script. Load the form by running it in your Web browser, through a URL, not by opening it from the file system (i.e, go to http://yoururl/ajax.html). Type an unused email address in the proper input, then tab or click on the next input. Repeat using a stored email address.

The two screenshots below show the results you should see. Absolute magic!

Conclusion

In this article you've seen how you can easily implement an Ajax process using PHP and Oracle. By doing so, server side validation is being accomplished within the client (although you should still keep server side validation as well, in case JavaScript is disabled). This particular example, in my opinion, demonstrates a nice feature for an end user, saving them from having to:

Submit the form
See that the email address has already been registered
Return to the form
Repeat

Also, I think this example and the concept in general is kind of cool. And on the Web, that's not an insignificant factor.

Of course, there's tons more you can do with Ajax, PHP, and Oracle. The example in this article sent only one piece of data to the PHP script and retrieved just a string. You can send much more back and forth. If you have a significant amount of data to be returned to the calling page, you can have PHP return it in XML format, then use JavaScript to parse and display it. If you search online, you'll find many different examples.

Larry Ullman is the Director of Digital Media Technology and Lead Web Developer at DMC Insights Inc., a company specializing in information technology. Larry lives outside of Washington, D.C., and is also the author of several books on PHP, SQL, Web development, and other computer technologies.

Curtsy: http://www.oracle.com/

Think Different

Saturday

Tutorial for PHP , Oracle and Ajax