Category: MySQL

What It Is

jQuery is an Open Source JavaScript library, and extensive collection of JavaScript code that can be plugged into any website by the developer and used to carry out myriad tasks. Numerous plug-ins exist to extend its capabilities.

For those unfamiliar with the terms, Open Source means it can be used for free, and JavaScript is a coding language that operates on the client computer rather than the web server. Operating on the client means it can respond very quickly relative to tasks that have to be requested across the Internet, carried out on the server, and sent back across the Internet. Not everything can be done this way, but a combination of 'server side' and 'client side' programming delivers a highly functional, high performance and highly satisfying user experience.

In general, anything related to databases or tasks with security implications must be carried out on the server side, though they can be assisted and richly enhanced by client side tasks using jQuery. The security implications of client side code are significant: anything send by a web browser can be intercepted and manipulated easily by a knowledgable user, so key tasks must be carried out or validated on the server where the user cannot interfere.

How It Works

I'm going to use an example here of code I use when registering a new user on one of my websites. When the username is being entered in the registration form, the database is checked every time a character is typed into the box. To be more specific, each time a key is released while typing in the username field of the user registration form, the database is queried to see if the string typed at at that point is a currently used username in the database. If so, a red "Not Available" indicator is displayed beside the username field. If not, a green "Available" indicator is displayed.

Two things are required to use jQuery on a website.

First, the web devloper makes the code available to the web pages. This is done in one of two ways. The code can be downloaded to the website, or links to the code on a Content Deliver Network (CDN) can be placed in the head section of the HTML code. I prefer to use a CDN. CDNs are specialized and highly efficient servers that will almost certainly deliver the code faster than my webserver could, at no cost to me.

Second, the required javascript to work with the library code is placed (or linked) in the head section of the HTML.

Below, I show the jQuery document ready function containing another function to support the page that registers new users. Look at the line of code. Paraphrased, it means "Every time a keystroke is lifted (keyup) in the object identifed as 'username' (the username field of the registration form), run the function 'check_username()' ".

Note that the document ready function can contain many other functions. Here I've only placed one function headed by the long dashed comment line that I place at the top of my functions so that I can see where each one starts if I have many of them.

The check_username() function (remember, it runs everytime you lift a keystroke while typing a username) is not placed in the document ready function because it doesn't run until it is needed. It does a few things. First, it creates a javascript variable 'username' containing the text in the username field at that moment. Next it checks to see if that string is empty or less than 5 characters, the length defined as the minimum length for a password.

If the string is less than 5 characters, it hides the 'Not Available' or 'Available' indicators. Then there's nothing else for it to do.

If the string is long enough then a jQuery AJAX routine runs a separate PHP script called ajax.check_username.php which you can see below.

AJAX is a technology which allows certaing content in a web page to be updated without reloading the page. This makes the whole experience run very quickly with minimal load on the server and bandwidth.

In this case, the AJAX routine fires the ajax.check_username.php script for each keystroke. This is a pretty simple script that connects to the database and looks to see if the username string as of that keystroke is in use in the database. The response to that query will be one of two values: zero if the username is not in use, 1 if it is used. That value is passed back to the check_username() function at the line that reads success: function(response).

We're almost done. Now the check_username() function simply turns on the appropriate availability symbol. If the response was 1, meaning the username is in use, the green tick mark object (#tick) is hidden in case it is showing, and the red x mark is shown. If the response is 0, meaning the username is not already in use, the opposite happens.

That's it! Every time you type a character in the username field, all that happens. Seems like a lot, doesn't it?

The Takeaway

Remember that once jQuery was made available to the website three key elements were used here, which I'll call the Trigger, the Caller and the Agent.

The Trigger is the jQuery keyup() code that fires every time the user lifts a keystroke in the form control identified by the attribute id="username" and initiates the Caller.

The Caller is the check_username() JavaScript function that calls the Agent if certain conditions are met – specifically if there is a text string of 5 characters or more in the username field. After the Agent responds, it will modify the visibility of the #cross and #tick objects which are placed beside the username field in the HTML form.

The Agent is the ajax.check_username.php script which operates in the background to examine the database and report back to the Caller on whether the string found in the username field at that moment is already in use as a username.

The Code

Here's the JavaScript code that needs to be available. It will be in the head section of the HTML or in an external file linked in the head section. I prefer to keep it in an external file to make my files easier to work with. Note I've got two separate functions here; a jQuery document ready function and my own check_username() function. jQuery functions typically start withe the characters $(.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

<script type="text/javascript">
 
  $(document).ready(function(){
  // ================================================================================================================== //
  // DOCUMENT READY FUNCTION
  // Whatever is in here is loaded before the page, it's ready to go when the page loads.
  // Ref: http://docs.jquery.com/Tutorials:Introducing_$(document).ready()
  // ================================================================================================================== //
 
    // ---------------------------------------------------------------------------------------------------------------- //
    // causes username to be checked for availability as it's entered in new member form
    // Everytime a keystroke is lifted in the #username control, the check_username() function below is fired.
    $('#username').keyup(check_username);
 
  });
 
  // ================================================================================================================ //
  // CHECK USERNAME FOR AVAILABILITY
  // when username is being entered for a new member, check each keystroke to see if that username is already in use
  // ref: http://benohead.com/check-username-availability-using-jquery-and-php/
  // ref: http://papermashup.com/jquery-php-mysql-username-availability-checker/
  // ================================================================================================================ //
  function check_username(){
    var username = $('#username').val();
    if(username == '' || username.length < 5){
       $('#username').removeClass("tick").removeClass("cross");
       $('#cross').hide();
       $('#tick').hide();
    } else {
       jQuery.ajax({
          type: 'POST',
          url: '/ajax.check_username.php',
          data: 'username='+ username,
          cache: false,
          success: function(response){
            if(response == 1){
               $('#tick').hide();
               $('#cross').fadeIn();
            }
            else {
               $('#cross').hide();
               $('#tick').fadeIn();
            }
          }
       });
    }
  }
 
</script>

Here's the php script used to go to the database to see of the username is already in use. It operates behind the scenes, invisible to the user. I put ajax. at the front of the filename so all such scripts will appear together in my file listings, and to remind me that this is a background script that is never called in the URL of a web browser.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

<?php
 
/*----------------------------------------------------------------------------------------------------------------------
SCRIPT: ajax.check_username.php
AUTHOR: Rob Scaife
DATE: 2013-01-10
 
NOTE: This script is called by the check_username() jquery function to test username for availability
as it is entered. It will never be called directly.
 
--- OVERVIEW -----------------------------------------------------------------------------------------------------------
 
Checks username being entered in new user form for availability.
On each keyup once minimum username length has been reached, this sc ript is fired by the check_username() js
function to see if the username at that point is in use.
 
--- NOTES --------------------------------------------------------------------------------------------------------------
 
--- INTERDEPENDENCIES --------------------------------------------------------------------------------------------------
called by check_username() function in jQueryPlugins.js
userprofile.php - Contains the form where username is entered.
 
--- REFERENCES / SOURCES -----------------------------------------------------------------------------------------------
http://benohead.com/check-username-availability-using-jquery-and-php/
http://papermashup.com/jquery-php-mysql-username-availability-checker/ (cross and tick icons)
 
======================================================================================================================*/
 
// database credentials
$database = "db_name";
$dbserver = "db_server";
$password = "db_password";
 
// retrieve and sanitize password
$username = mysql_real_escape_string($_REQUEST["username"]);
 
// connect to database
$con = mysql_connect($dbserver,$database,$password);
 
if (!$con) {
echo 0;
} else {
mysql_select_db($database, $con);
$result = mysql_query("SELECT * FROM users WHERE username='" . $username . "'");
$num = mysql_num_rows($result);
echo $num; //it will always return 1 or 0 since we do not allow multiple users with the same user name.
}
 
// close the connection to the database
mysql_close();
 
?>

Here is the HTML code for the username field. I haven't included all the HTML form or table code here, just the table row that holds the username field. Login forms are often laid out in an HTML table but don't have to be. Notice the two span tags identified as "tick" and "cross". These are the visual indicators that are made to appear or disappear by the check_username() function above. You can skip the graphic or the text if you want, but you need to have something there.

1
2
3
4
5
6
7
8

<tr>
<td>Username:</td>
<td>
<input type='text' id='username' name='username' style='font-family: consolas, sans-serif;font-size:105%;width:170px;' class="login_input required">
<span id="tick"><img src="/images/icons/tick.png" width="16" height="16"/> Available</span>
<span id="cross"><img src="/images/icons/cross.png" width="16" height="16"/> Not Available</span>
</td>
</tr>

—