Querying a database via jquery and ajax

foregoinggowpenSoftware and s/w Development

Nov 4, 2013 (3 years and 11 months ago)

72 views

F28DM: Database Management Systems


JQuery and Ajax



Page
1

of
7

Querying a database via jquery and ajax

1

Acknowledgements

Quite a bit of material taken from
http://www.telerik.com/help/aspnet
-
ajax/ajax
-
ajax.html

and
http://www.adaptivepath.com/ideas/ajax
-
new
-
approach
-
web
-
applications
.

2

DOM recap

Javascript and the Document Object Model was introduced in the Internet & Comms course.

J
avaScript is a scripting language which can be embedded in HTML in order to create enhanced user
interfaces and dynamic websites. It is used on the client side, within HTML documents.

The HTML DOM defines a standard way for accessing and manipulating HTML
documents. Elements in
the document are organized hierarchically, in a tree. In particular, if elements on a form, for example,
have their id attribute set, their values can be accessed and altered by javascript. We will see this in a
few examples.







3

Classic web
application

model

The classic web application model works like this: Most user actions in the interface trigger an HTTP
request back to a web server. The server does some processing

retrieving data, crunching numbers,
talking to various legacy
systems

and then returns an HTML page to the client.

Take a login form for example. When the user clicks the ‘submit’ button:



A HTTP request is sent to a script on the web server, including their user
na
me and password as parameters.

F28DM: Database Management Systems


JQuery and Ajax



Page
2

of
7



The script on the w
eb server sends a query to the database, discovers that there is an error, and
sends back an HTTP response containing a complete html page plus any associated data such as
CSS information.



The page is redrawn and displayed to the user, but it will look mu
ch the same apart from the
error message, and, particularly if the login page is surrounded by lots of other features such as
images and menus, will take time to be redrawn.




This traditional synchronous (postback
-
based) communication requires a full pag
e transmitted and
reloaded each time a message is required from the server. This leads to the following negative effects:



Poor Interactivity

-

the user interaction with the application is interrupted by a postback every
time a server call is needed.



Ineff
ectiveness

-

the full page is rendered and transferred to the client on each postback. This
process is time consuming and traffic intensive.



Low Usability

-

the requirement for full page postback

whenever the user interface is changed
imposes hefty limitations on the degree of sophistication a web user interface can achieve.
Before AJAX, rich and smooth interfaces with on
-
demand updates could only be implemented
using Flash technology.

Javascript

functions are often added to validate data and display error messages, to prevent the page
being sent unnecessarily to the browser (e.g. omitted field).


The following diagram shows classic and ajax web application model (from adaptivepath).


F28DM: Database Management Systems


JQuery and Ajax



Page
3

of
7

4

Ajax

Ajax w
as first mentioned by Jesse James Garrett in 2005 in the above ‘adaptive path’ link.

Ajax

is an acronym for
Asynchronous JavaScript and XML.

Ajax consists of several technologies used together:



Presentation of data using XHTML, CSS



Dynamic display and int
eraction using the DOM (Document Object Model)



Asynchronous data retrieval using XMLHttpRequest.

‘Asynchronous’ means that the browser
can get some information and update the web page without having to reload the whole page.



Data interchange using XML (ori
ginally) or now JSON is often used



Javascript to respond to the user actions, send the request and process the response

Its use is now widespread.

The core idea behind AJAX is to make the communication with the server asynchronous, so that data is
transfe
rred and processed in the background. As a result the user can continue working on the other
parts of the page without interruption. In an AJAX
-
enabled application only the relevant page elements
are updated and only when necessary.

In the ajax web applic
ation model,
javascript

is used to control the rendering of the web page and to
handle the response to the user actions. As in the traditional model, some data validation is done (e.g.
field omitted), the page can be altered without any requests being sen
t to the server and without
redrawing the whole page.

When it is necessary to send a request to the server, this is done by creating an XMLHTTPRequest object
which is sent to the server using HTTPRequest. The response is sent back using a simple data str
ucture
such as XML or JSON. The html page is then partially altered using javascript. The whole page is not
reloaded.

Below is a diagram of the complete lifecycle of an AJAX
-
enabled web form, together with a list of events
in a typical interaction.








F28DM: Database Management Systems


JQuery and Ajax



Page
4

of
7



1.

Initial request by the browser


the user requests a particular URL, using the traditional
HttpRequest.

2.

The complete page is created by the server (along with the JavaScript AJAX engine) and sent to
be rendered by the client (HTML, CSS, JavaScript

AJAX engine).

3.

All subsequent requests to the server are initiated as function calls to the JavaScript AJAX engine.

4.

The JavaScript engine then makes an XmlHttpRequest to the server.

5.

The server processes the request and sends a response in simple (e.g.
XML) format to the client
(XML document). It contains the data only of the page elements that need to be changed. In
most cases this data comprises just a fraction of the total page markup.

6.

The AJAX engine processes the server response, updates the releva
nt page content or performs
another operation with the new data received from the server. (HTML + CSS)

5

jQuery

“Query is a fast and concise JavaScript Library that simplifies HTML document traversing, event handling,
animating, and Ajax interactions for rap
id web development.”
http://jquery.com/

An open source JavaScript library that simplifies the interaction between HTML and JavaScript.
Built in an attempt to simplify the existing DOM APIs and abstract away cross
-
brow
ser issues.
Created by John Resig in 2005, released in January of 2006.
http://ejohn.org/apps/workshop/intro/#1


$(function

()

{…});
is shorthand for

the jquery

$(document).ready(function().
Th
is
function will run as soon as the html document is ready to be manipulated. We put our code in here, for
whatever we want to happen. In the following examples, as soon as the html document is ready, we
send an ajax request for data which is then displaye
d.


How to use
jquery
:

Either download the latest jquery functions, and link to them, or link to the latest jquery file.


<script src="

http://code.jquery.com/jquery
-
latest.min.js
">


</script>


Then write the code.



F28DM: Database Management Systems


JQuery and Ajax



Page
5

of
7

6

Example

-

Display, adding and deleting

Based on
http://www.saaraan.com/downloads/23
-
ajax
-
add
-
delete
-
records
,
but
significantly

altered!

A reasonable amount of error checking is built in.
This
program demonstrates t
he use of javascript, jquery and ajax:

1.

Display table of departments with Delete button for each, and a form
for adding a new department. Javascript function is called as soon as
the html document is ready.

2.

Processes delete and removes deleted record from t
able. Javascript
function is called when delete button is clicked. These buttons are
links,
NOT on a form!

3.

Adds new department and adds new record to table. Javascript
function is called on form submit.

6.1

Html, j
avascript and ajax in this program

In the
index.html page, there is some
basic html to start the document off, outline the table for the
departments (table element only, no details), and to include the form at the bottom for adding a record.


There is also
javascript
which contains the main funct
ionality.

<script type="text/javascript">

$(document).ready(function() {
put jquery functions in here

});


</script>


In the document ready function, there are three functions:

1.

$.ajax({…});

To get the initial table data and create the table. This runs as

soon as the
document is ready.

2.

$("#responds").on("click", ".del_button", function(e) { . . });

To respond
to a click in a class=“del_button” in the table with the id=”responds”.

3.

$("#FormSubmit").click(function (e) { . . . });

To respond to the add form
being submitted

6.2

A jquery ajax request

An ajax request is made in javascript using jquery as follows
, showing example values
:


$.ajax({

// or
jQuery.ajax({


type:

"POST"




//how parameters are sent




url: "
destination
.php",
//the
serverside
script

data: myVariableWithData,
//
parameters for the url

d
ataType: 'json',
//returned data format




success: function(data)
//what to do when successful reply



{ . . .

do something with the returned data
},


error: function(data, ErrorText, thrown)


{ . . .
do something with the error data
}


});

F28DM: Database Management Systems


JQuery and Ajax



Page
6

of
7

In this program, the error handling throws an alert (see the code).

6.3

The initial creation of the table
with
delete button

6.3.1

Delete button

The whole table has an id=”responds”.

The following approach only works because the department number is a short number.
It’s a bit
complicated, but otherwise the example is good.

Each row in the table and each delete button ha
s an id containing the department number
(id=”dNum_8” and id=”del_8”). This means that once the button is clicked, we have access to the
department number which identifies the department to be deleted, and the row to be removed from
the table can similarl
y be identified.

<tr id="dNum_8”>

<td>8</td><td>Building</td>'

<td><div class="del_wrapper"><a href="#" class="del_button" id="del
-
8">



<img src="images/icon_del.gif" border="0" /></a></div></td>'


</tr>

The delete button is a link with a reference to n
owhere (#). There is a jquery function which is called
when a delete button in the 'responds' table is clicked.

6.3.2

Requesting and displaying the data

The ajax request is sent off with no parameters.

The
serverside script, PHP, recognizes ‘no parameters’, and

runs the query, using PDO, to retrieve
department details. The script loops through the results and adds them to an array, then encodes this in
JSON format.

The javascript success function receives this JSON array, and loops through it, creating a row in
the table
for each department.

There is a user
-
defined function createTableRow to do this. Each row is then
added to the table (using the id=’responds’ and the append function).
$("#responds").append(createTableRow(data[i].dNum,data[i].dName));

6.3.3

Adding a re
cord

The javascript function picks up the values from the form text boxes, and throws an alert if these are
missing. The parameter string is then composed (
dNum=12&dName=Biology
).
The ajax request is sent
off with
these parameters.

The serverside

script, PHP, recognizes these parameters. It checks that the number is within limits, and,
by running a query, checks that there isn’t already a record there with the same department number.
(An alternative to this would be to catch SQL Duplicate Key Exc
eptions). If all is OK, the department
record is created, and the details returned. If the record is not inserted, an error message is returned.

F28DM: Database Management Systems


JQuery and Ajax



Page
7

of
7

The javascript success function receives this
returned
JSON
object
, and
extracts the dept number and
name. A ne
w row is created, and added to the table as before.

Also the form text boxes are cleared.

$("#responds").append (createTableRow(response["dNum"], response["dName"]));

6.3.4

Deleting a record

The javascript

function splits the number from the button id, and creates the parameter
(
recordToDelete=12
). The ajax request is sent off with a parameter of the number of the department
to be deleted.

The serverside script, PHP, recognizes these parameters. The record

is deleted and a check made to see
that this happened (1 row affected). If it didn’t work, an error is returned.

The javascript success function uses the fadeOut function to remove the row from the table (using
id=’responds’ for the table and id=”dNum_n”
for the row (where n was the number to delete).

6.4

Ajax03 Autocomplete with PHP, jQuery, MySQL and XML

Another example.
Cool but no time in lectures for this. Look at it if you are interested.

http://www.script
-
tutorials.com/autocomplete
-
with
-
php
-
jquery
-
mysql
-
and
-
xml/

7

Testing your program

To catch javascript errors, ensure that in your browser, web development tools are displayed


for
example, the error console.