How to access a database

Mon, 01/07/2008 - 04:35 — sgrover

The inherent JavaScript security restrictions prevent us from accessing files or other resources (outside of web accessible content) directly. This means we cannot access a database directly. There is a way around this though, and that is utilizing Ajax.

Via Ajax we can pass a request to a database with a server side resource, get some results back, and then update our page with those results. The results can be whatever we'd like - full tables, report data, or just what background image the user likes.

There are a number of considerations before undertaking this task.

How/where do we access the data. We need a server side resource to get our data. This could be a PHP/ASP.Net/Cold Fusion/CGI driven page. (or any number of other sever side techniques)
What information do we need to send with our request so we get the correct data back. For example, if we were looking for a specific employee's record, we'd probably need to pass the employee ID we are interested in. This ID would then be used in the SQL to get the correct data.
What format will the results be in? If you are also creating the server side resource, then you have some flexibility here. If however someone else controls that resource, you have to adapt to what they are giving you. It could be HTML, XML, plain text, or even JavaScript Object Notation (JSON).
What will we do with the results? If we are expecting HTML then we may simply add that HTML into our page. If we get back JSON, we may use this to adjust the page, or to generate a table.

Once we have this information we can then build the Ajax request.

Samples:
Let's do an example. Actually, we'll do two just so we can demonstrate different approaches. We'll use the employee details for our sample. Imagine we have a list of employee names on the page ( LI tags), as well as an empty DIV after each name (but within the LI tag). We want to add functionality so that when a name is clicked, we ask for the employee details and populate the corresponding DIV. We'll even add in some animations and make the DIV slide up/down.

Sample 1
Right, so first, where is the information coming from? We'll suppose a page has been created for this and is called "employee_detail.php". It takes an ID parameter - which is conveniently store in the ID attribute of our LI tag. The server side page returns an HTML table (for our first sample). We already know we want that table in our DIV. We could handle this situation like so:

$(document).ready( function () { $(".employeeNames").click( function () { $(this) .children("div") .load("employee_details.php", { id : this.id }) .slideDown(); }); });

We are using a shortcut here - the .load() method. We tell jQuery to ask for the results from "employee_details.php, and set the "id" parameter to the value represented by "this.id". "this" is always context sensitive, and is a way to reference the current element. The element we are dealing with in our click() function is the LI tag that received the click - so that LI tag is "this". When we get the results back, we add it to the referenced element - in this case the DIV tag that is a child of the clicked LI tag. The moment the LI tag is clicked, we slide the detail DIV down (assuming it was previously hidden).

Sample 2
Now using the same basic sample, lets imagine that we needed to highlight supervisors in a different color. We'll use a different server side file this time - "employee_json.php", which will return a JavaScript object with the employee data. This object has properties for name, position, start date, and phone number. We need to show this information in a table, but highlight the containing DIV with a different color if the position property is "supervisor".

$(document).ready( function () { $(".employeeName").click( function () { var clickedItem = this; $.ajax({ url : "employee_json.php", data : "id=" + clickedItem.id, dataType : "json", success : function (result) { var tbl = ""; tbl += "Name:" + result.name + ""; tbl += "Position:" + result.position + ""; tbl += "Start:" + result.start_date + ""; tbl += "Phone:" + result.phone + ""; tbl += ""; // $(clickedItem).children("div").html(tbl).slideDown(); if (result.position == "Supervisor") { $(clickedItem).children("div").css("background-color", "#ddd"); } } }) }); });

Here we are setting up the same .click() handler as in sample 1. The difference is that we are using the $.ajax() method instead of the .load(). We tell the $.ajax() method what page to ask for, what data to pass to that page, and that we are expecting a JSON result. We also tell the routine what to do if everything happens successfully. Because the "success" function isn't run until later (remember Ajax is asynchronous), we store a reference to our clicked item and use that reference in our function instead of "this" (which would be the wrong reference at that time). In our success function, we simply build a string containing the HTML for a table, and then place that table into our DIV and show it via the slideDown() method. We do a check on the position, and if it is a supervisor position, we change the background color of the DIV.

Final Notes
Well we are not directly accessing a database, we are utilizing the results of a database request. We have seen two different approaches that can be used - use the results directly, or do other work with the results. In this way, jQuery (and JavaScript) provides some very flexible approaches to getting new data.