Categories
Database DHTML jQuery MySQL PHP Web Development

Introduction to jQuery

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>

Categories
PHP Web Development

PHP Primer Code v3

This is the code to accompany the PHP Primer on this site.

It provides a fully working PHP/MySQL records management system on one script when given access to the MySQL table 'primer', which can be created with the code embedded in comments at the end of the script.

This very simple script should get you up and running very quickly, and you can build on it.

Note that before putting any web application online, input from site visitors should be filtered to prevent SQL Injection attacks.

<!--?php // ------------------------------------------------------------------------------------ // PHP MySQL PRIMER v3 // ------------------------------------------------------------------------------------ // // This file is intended to give a basic understanding and all the code required to // access a MySQL database with PHP and to add, edit and delete data in the database. // // If you run this .php script on a server with PHP and MySQL installed, and have // created the 'primer' database using the sql script in the comments at the end of // this file, this script is a functioning add/edit/delete/list database interface. // You can cut and paste this entire document into a .php file using a text editor. // // You can copy this file and use it as the basis of any system to manage information // with PHP and MySQL. // // Prerequisites: // - Installing a web server, PHP and MySQL is beyond the scope of this document. // Suffice it to say that these components are available at no cost. Many people use // a WAMP package (http://en.wikipedia.org/wiki/WAMP), which installs all these // components together. One such WAMP package is called XAMPP, and can be downloaded // at http://en.wikipedia.org/wiki/XAMPP. Separate manual install of these components // can get a little involved, so you may be better off with a XAMP package or a LAMP // package on Linux. // // - The sql script embedded in comments at the end of this file will give the SQL // code required to create the databse and table that work with this file. // Save the script in a file called primer.sql and restore the file to your // MySQL server and it will create the database. // // - Create a MySQL user 'test' with the password 'password', and ensure that it // has full rights on the database 'primer'. // // - Once you do that you can start to experiment with this page. // // This demonstration script operates with an extremely simple database, called 'primer'. // It contains only one table, 'members' and that table stores only three // attributes, 'id', 'firstname' and 'lastname'. The 'id' attribute is the primary key // and it is an auto-increment attribute, meaning that when a new record is added // the value of 'id' will be automatically assigned. // // I strongly recommend installing the MySQL Administrator and MySQL Query Browser // utilities, both free from MySQL at http://dev.mysql.com/downloads/gui-tools/5.0.html. // The administrator utility can be used to 'restore' the primer.sql file to create // the sample database on your server. // // I also strongly urge the use of a syntax-colouring text editor, which makes code // much easier to work with. A good option is the free open-source editor Notepad++ // at http://notepad-plus.sourceforge.net. // // // NOTE: In a normal working environment, some of the things in this // file would be separated into include files called with the include() function. // Examples would be the page header HTML code, the HTML code to close the page, // and the database connect code. These include files would be accessed by all pages // on the site, so changes to the entire site could be made in one file. // In this case, I've put it all into one file for clarity. // // Now Go Nuts! // // // // ------------------------------------------------------------------------------------ // Version 2 - 2009-07-30: Some enhancements added which make the code slightly more // complex, but make it a better template for building a webapp component. // - Added TH header // - Added CSS styles // - Replaced ampersands in query strings with entities to pass validation // - Added code for alternating row colours // - Added code to sanitize input to prevent SQL Injection attacks // // Version 3 - 2009-10-15: embedded primer.sql script in comments at end of this file // // ------------------------------------------------------------------------------------ // First we're going to open the HTML page in the normal fashion // this page intro applies to all pages rendered by this file. // You probably already understand this, so feel free to skip ahead to the // DATABASE CONNECT section following. // the DOCTYPE declaration just below is typically on one line, it is split in 2 here to facilitate printing ?-->
tags contain Cascading Style Sheet (CSS) code, -- */ /* -- which controls the visual appearance of content on the web page. -- */ /* ---------------------------------------------------------------------------- */ .header a {color: white;} html {font-family:sans-serif;} table {border-collapse:collapse;} .listtable table {padding:.25em;border:1px solid silver;} .listtable td {padding:.125em .5em .125em .5em;border:1px solid silver;font-size:80%;text-align:left;} .listtable th { background-color:#B0C4DE; padding:.5em .5em 0em .5em; font-weight: bolder; font-size:80%; text-align:left; border-top:2px solid navy; border-bottom:1px solid navy; line-height:135%; } .listtable tr {border-left:1px solid navy;border-right:1px solid navy;} .listtable a:link {color:blue;} .listtable a:visited {color:blue;} .listtable a:active {color:blue;} .listtable a:hover {color: red;}
PHP • MySQL Primer list • add
$value) { $_POST[$key] = mysql_real_escape_string($value); } // Prevent SQL Injection attacks in GET vars foreach ($_GET as $key => $value) { $_GET[$key] = mysql_real_escape_string($value); } // connect to database server $dbserver = "localhost"; $username = "test"; $password = "password"; $connect = mysql_connect ("$dbserver", "$username", "$password") or die ('
I cannot connect to the database because: ' . mysql_error()); // select the database mysql_select_db ("primer") or die('
I could not select database: '.mysql_error()); // ==================================== /database connect ======================================= // // OK, so we've opened up our HTML page and connected to the database. // Now it's time to determine what the page will be doing, because this PHP script // file will generate pages to fill several distinct roles // When the page is loaded, it may have a query string with an 'action' parameter. // The switch statement below will look for that parameter and load the // appropriate page content by calling a function that generates the page. // A couple of these functions do not generate a page, they perform an action // on the database instead, then end by calling the listRecords() function to show // the modified database. // The code which follows first picks up the 'action' parameter – if any – from the // Query String (the end of the URL). // Then it uses that value in a switch statement to decide which of the functions below // will be executed. // If there is an action parameter in the Query String, the appropriate block will execute. // If there isn't an action paramenter, or it isn't listed in the case statements below, // the default block at the end of the switch statement will execute. // Once one of the functions is called from the switch statement, the function will // execute, after which control will be returned to the line following the function call. // That's important to understand, so pause here and make sure that's clear. // Each case in the switch ends with a break statement to cause flow to break out of the // switch statement once flow returns from the called function. Following the break // statement the flow will resume on the line immediately following the switch statement. // The real work of the page is done inside these functions // Capture the 'action' parameter from the query string. If there is none, // enter a substitute so that the switch statement doest not generate an error. $action = isset($_REQUEST['action']) ? $_REQUEST['action'] : "none_specified"; // One of the cases below will be executed, depending on the value of $action switch ($action) { case 'add': addRecord(); break; case 'delete'; deleteRecord(); break; case 'edit'; editRecord($_REQUEST['id']); break; case 'commitNewRecord'; commitNewRecord(); break; case 'update'; updateRecord(); break; default; listRecords(); break; } // Once the function called from the switch statement above is complete, the flow // will return here, to the line following the switch statement // Now we will close the page structure. ?>
<!--?php 
 
// ensure that the flow stops here, immediately after the page structure is closed.
exit(); 
 
// ==================================================================================== //
// ======                  THIS IS THE EXIT POINT OF THE FILE.                    ===== //
// ======  ALL CODE BELOW IS CALLED FROM THE CASES IN THE SWITCH STATEMENT ABOVE  ===== //
// ==================================================================================== //
 
function listRecords() {
#-----------------------------------------------------------------------------------------
#    FUNCTION: listMembers
#     PURPOSE: Read the contents of the members table and list them
#  PARAMETERS: none
#-----------------------------------------------------------------------------------------
  // display a header
  echo "<h2-->List Members"; // start by opening up a table to display the data echo ""; // read the data from the database table into a dataset $dataset = mysql_query("SELECT * FROM members"); // create the header row echo ""; echo " \n"; // nothing needed in 1st column header, so just put a hard space entity echo " \n"; echo " \n"; echo ""; // now we'll loop through the dataset, displaying rows one by one while ($row = mysql_fetch_assoc($dataset)) { // create a variable for each attribute in this row // the variable will have the same name as the attribute // this is much easier to work with than "$row['attribute']" foreach($row as $var => $value){ $$var = $value; } // create the background colour for this row to make it easier to read. // The background colour will toggle back and forth between each row. $bgcolour = (!isset($bgcolour) || $bgcolour == "#F0F7FF") ? "#FFFFFF" : "#F0F7FF"; // Now create a table row for this record // Note how the variables $id, $firstname and $lastname, created just above, are // inserted into the data. // Remember this is all happening inside a 'while' loop, so these values will change // with each iteration as the rows are generated one by one. echo "\n"; // note the variable that sets the alternating row color echo ""; echo ""; echo ""; } // end while // We're out of the while loop, so we've gone through all the rows in the dataset. // Now close the table echo "
First Name Last Name
"; echo " edit · "; echo " delete"; echo " $firstname $lastname
"; } // end listRecords() function addRecord() { #----------------------------------------------------------------------------------------- # FUNCTION: addRecord() # PURPOSE: Present a form for the user to add a new record to the database # PARAMETERS: None # NOTE: Notice that I have used the hard-space entity   in the empty # table cell. This corrects display problems with empty cells # in some browsers. #----------------------------------------------------------------------------------------- // display a header echo "

Add a New Member

"; // Build the form. // Because this is a fairly lengthy piece of code, we'll break out of PHP mode into HTML // mode temporarily. // We supply the 'action' parameter 'commitNewRecord' in the 'action' parameter in the // form element to tell the script what to do when it receives the data from the form. // NOTE: Notice how we use the server variable $_SERVER['PHP_SELF'] in the
element // to substitute for the current page name. This means that it will work even if // the file name of the script is changed, as it would be if you copied it to // modify and work with a copy. ?>
First name:
Last name:
?action=update'> ">

First Name: ">
Last Name: ">

Enjoy!

Categories
JavaScript PHP Web Development

Data Validation

Some quick references here to data validation resources.  Many or most of these resources will refer to the Validate plugin for jQuery.

Server Side

http://phpmaster.com/form-validation-with-php/

Client Side

Much of client side data validation will center around jQuery.

Tutorials:

Custom Rules

Conditional Validation

Notes

To test if jQuery library is loaded, place the following in the HEAD of your document:

<script type="text/javascript">// <![CDATA[
  $().ready(function() {
    alert("HELLO");
  });
// ]]></script>

 

Categories
MySQL PHP

Access Column names in MySQL

I recently needed to create a page that would serve as a light admin page for my configuration table.  Just read in the table and let me edit the values. 

However, the field names serve as lables, and sometimes they don't have all the information needed on now the attribute is used, so I maintain more info in the comment for the field.  (The optional COMMENT is part of the meta data for the field in MySQL.)

So I wanted to capture the field comments into an array so that I could place them beside the edit control for each attribute.

Here's my solution:

1
2
3
4
5
6
7
8
9
10
11
12
13
// -------------------- capture column comments into array ---------------
$tablename = "configuration";
$meta_to_capture = array('Field','Comment');
$result = mysql_query("SHOW FULL COLUMNS FROM $tablename");
$row = mysql_fetch_assoc($result);
while ($row = mysql_fetch_assoc($result)) {
  foreach($row as $var => $value){
    if (in_array($var,$meta_to_capture)) {
      $field_comment[$row['Field']] = $row['Comment'];
    }
  }
}
// -------------------- /capture column comments into array --------------

To access it, simply use the following syntax:

$comment = $field_comment['attribute_name'];
Categories
PHP

Random String or Password Generator

The following function returns a random string of the length passed to the function, with a default length of 8 if none is passed.

If you look at the $chars string that supplied the character list for generating the string, you'll see that I've eliminated easily confused characters such as the zero and upper case "O", and the number "1" and lower case "l". This reduces support calls when users have to type the string in manually, as in when it is used as a temporary password.

I use this for generating temporary passwords when people are resetting lost passwords on a website and in other uses where I need a random string.

Where security is a significant concern, you may want to research random string generation more thoroughly.

function GeneratePassword($length = 8) {
  $chars = '2346789abcdefghjkmnprtuvwxyz';
  for ($p = 0; $p < $length; $p++) {
    $result .= $chars[mt_rand(0, 27)];
  }
  return $result;
}