jQuery 1.3 with PHP
Creating PHP applications that respond quickly, avoid unnecessary page reloads, and
provide great user interfaces, often requires complex JavaScript techniques. Even then, if
you get that far, the results might not work across different browsers! With jQuery, you
can use one of the most popular JavaScript libraries, forget about cross-browser issues,
and simplify the creation of very powerful and responsive interfaces—all with the
minimum of code.
This is the first book in the market that will ease the server-side PHP coder into the
client-side world of the popular jQuery JavaScript library.
This book will show you how to use jQuery to enhance your PHP applications, with
many examples using jQuery's user interface library jQuery UI, and other examples using
popular jQuery plugins. It will help you to add exciting user interface features to liven up
your PHP applications without having to become a master of client-side JavaScript.
This book will teach you how to use jQuery to create some really stunning effects, but
without you needing to have in-depth knowledge of how jQuery works. It provides you
with everything you need to build practical user interfaces, for everything from graphics
manipulation and drag-and-drop to data searching, and much more. The book also
provides practical demonstrations of PHP and jQuery and explains these examples, rather
than starting from how JavaScript works and how it is different from PHP.
By the end of this book, you should be able to take any PHP application you have
written, and transform it into a responsive, user-friendly interface, with capabilities you
would not have dreamed of being able to achieve, all in just a few lines of JavaScript.
What This Book Covers
Chapter 1, Introduction and Overview, introduces you to what jQuery is, why you would
want to use it, and why is it useful to combine it with PHP. Also, it gives a list of projects
illustrating uses for combining PHP and jQuery.
Chapter 2, Quick Tricks, looks at a few quick examples on how to interface PHP and
jQuery and a few tricks, which demonstrate how to relieve the most obvious resource
wastes on the server.
Chapter 3, Tabs and Accordions, walks through the creation of tabs and accordions using
the jQuery UI project, managing tabs and accordions using a rich text editor and a bit of
PHP, and using Ajax to populate your accordion and tab panels.
Chapter 4, Forms and Form Validation, explores form validation using jQuery and PHP
and how to use the same PHP configuration to validate on both sides—the server and the
client side. It also covers examples on optimization of large select boxes and building
auto-suggest fields.
Chapter 5, File Management, teaches you to manage almost everything—creating,
uploading, editing, moving, downloading, and deleting files and directories.
Chapter 6, Calendars, builds a weekly calendar for you, which has events that can be
created, edited, moved around, and deleted. It also takes care of recurring events.
Chapter 7, Image Manipulation, discusses methods for manipulating images with jQuery
and PHP, along with some ways to make the changes non-destructive, so that multiple
manipulations, such as select, rotate, resize, and crop, can be made on the same image.
Chapter 8, Drag and Drop, demonstrates a few uses of drag and drop, including sorting
lists, dragging between lists, and hierarchical sorting, which can be used to improve the
usability of your content management system.
Chapter 9, Data Tables, builds a very large data table and discusses how to navigate, sort,
search, paginate, and search it using jQuery and Ajax.
Chapter 10, Optimization, shows the ways to optimize jQuery and various other elements
of the web development environment.
Data Tables
From time to time, you will want to show data in your website and allow the data to
be sorted and searched.
It always impresses me that whenever I need to do anything with jQuery, there are
usually plugins available, which are exactly or close to what I need.
In this chapter, we will look at:
- How to install and use the DataTables plugin
- How to load data pages on request from the server
- Searching and ordering the data
The DataTables plugin allows sorting, filtering, and pagination on your data.
Here's an example screen from the project we will build in this chapter. The data is
from a database of cities of the world, filtered to find out if there is any place called
nowhere in the world:
Get your copy of DataTables from http://www.datatables.net/, and extract it into
the directory datatables, which is in the same directory as the jquery.min.js file.
What the DataTables plugin does is take a large table, paginate it, and allow the
columns to be ordered, and the cells to be filtered.
Setting up DataTables
Setting up DataTables involves setting up a table so that it has distinct <thead> and
<tbody> sections, and then simply running dataTable() on it.
As a reminder, tables in HTML have a header and a body. The HTML elements
<thead> and <tbody> are optional according to the specifications, but the
DataTables plugin requires that you put them in, so that it knows what to work with.
These elements may not be familiar to you, as they are usually not necessary when
you are writing your web pages and most people leave them out, but DataTables
needs to know what area of the table to turn into a navigation bar, and which area will
contain the data, so you need to include them.
Client-side code
The first example in this chapter is purely a client-side one. We will provide the data
in the same page that is demonstrating the table.
Copy the following code into a file in a new demo directory and name it tables.html:
<html>
<head>
<script src="../jquery.min.js"></script>
<script src="../datatables/media/js/jquery.dataTables.js">
</script>
<style type="text/css">
@import "../datatables/media/css/demo_table.css";</style>
<script>
$(document).ready(function(){
$('#the_table').dataTable();
});
</script>
</head>
<body>
<div style="width:500px">
<table id="the_table">
<thead>
<tr>
<th>Artist / Band</th><th>Album</th><th>Song</th>
</tr>
</thead>
<tbody>
<tr><td>Muse</td>
<td>Absolution</td>
<td>Sing for Absolution</td>
</tr>
<tr><td>Primus</td>
<td>Sailing The Seas Of Cheese</td>
<td>Tommy the Cat</td>
</tr>
<tr><td>Nine Inch Nails</td>
<td>Pretty Hate Machine</td>
<td>Something I Can Never Have</td>
</tr>
<tr><td>Horslips</td>
<td>The Táin</td>
<td>Dearg Doom</td>
</tr>
<tr><td>Muse</td>
<td>Absolution</td>
<td>Hysteria</td>
</tr>
<tr><td>Alice In Chains</td>
<td>Dirt</td>
<td>Rain When I Die</td>
</tr>
<!-- PLACE MORE SONGS HERE -->
</tbody>
</table>
</div>
</body>
</html>
When this is viewed in the browser, we immediately have a working data table:
Note that the rows are in alphabetical order according to Artist/Band. DataTables
automatically sorts your data initially based on the first column.
The HTML provided has a <div> wrapper around the table, set to a fixed width.
The reason for this is that the Search box at the top and the pagination buttons at
the bottom are fl oated to the right, outside the HTML table. The <div> wrapper is
provided to try to keep them at the same width as the table.
There are 14 entries in the HTML, but only 10 of them are shown here. Clicking the
arrow on the right side at the bottom-right pagination area loads up the next page:
And finally, we also have the ability to sort by column and search all data:
In this screenshot, we have the data filtered by the word horslips, and have ordered
Song in descending order by clicking the header twice.
With just this example, you can probably manage quite a few of your
lower-bandwidth information tables. By this, I mean that you could run the
DataTables plugin on complete tables of a few hundred rows. Beyond that,
the bandwidth and memory usage would start affecting your reader's experience.
In that case, it's time to go on to the next section and learn how to serve the data on
demand using jQuery and Ajax.
As an example of usage, a user list might reasonably be printed entirely to the page
and then converted using the DataTable plugin because, for smaller sites, the user
list might only be a few tens of rows and thus, serving it over Ajax may be overkill.
It is more likely, though, that the kind of information that you would really want this
applied to is part of a much larger data set, which is where the rest of the chapter
comes in!
Getting data from the server
The rest of the chapter will build up a sample application, which is a search
application for cities of the world.
This example will need a database, and a large data set. I chose a list of city names
and their spelling variants as my data set. You can get a list of this type online
by searching.
The exact point at which you decide a data set is large enough to require
it to be converted to serve over Ajax, instead of being printed fully to the
HTML source, depends on a few factors, which are mostly subjective.
A quick test is: if you only ever need to read a few pages of the data, yet
there are many pages in the source and the HTML is slow to load, then it's
time to convert.
The database I'm using in the example is MySQL (http://www.mysql.com/).
It is trivial to convert the example to use any other database, such as PostgreSQL
or SQLite.
For your use, here is a short list of large data sets:
- http://wordlist.sourceforge.net/—Links to collections of words.
- http://www.gutenberg.org/wiki/Gutenberg:Offline_Catalogs—A list
of books placed online by Project Gutenburg.
- http://www.world-gazetteer.com/wg.php?men=stdl—A list of all the
cities in the world, including populations.
The reason I chose a city name list is that I wanted to provide a realistic large
example of when you would use this.
In your own applications, you might also use the DataTables plugin to manage
large lists of products, objects such as pages or images, and anything else that can be
listed in tabular form and might be very large.
The city list I found has over two million variants in it, so it is an extreme example of
how to set up a searchable table.
It's also a perfect example of why the Ajax capabilities of the DataTables project are
important. Just to see the result, I exported all the entries into an HTML table, and
the file size was 179 MB. Obviously, too large for a web page.
So, let's find out how to break the information into chunks and load it only as needed.
Client-side code
On the client side, we do not need to provide placeholder data. Simply print out the
table, leaving the <tbody> section blank, and let DataTables retrieve the data from
the server.
We're starting a new project here, so create a new directory in your demos section
and save the following into it as tables.html:
<html>
<head>
<script src="../jquery.min.js"></script>
<script src="../datatables/media/js/jquery.dataTables.js">
</script>
<style type="text/css">
@import "../datatables/media/css/demo_table.css";
table{width:100%}
</style>
<script>
$(document).ready(function(){
$('#the_table').dataTable({
'sAjaxSource':'get_data.php'
});
});
</script>
</head>
<body>
<div style="width:500px">
<table id="the_table">
<thead>
<tr>
<th>Country</th>
<th>City</th>
<th>Latitude</th>
<th>Longitude</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</body>
</html>
In this example, we've added a parameter to the .dataTable call, sAjaxSource,
which is the URL of the script that will provide the data (the file will be named
get_data.php).
Server-side code
On the server side, we will start off by providing the first ten rows from the database.
DataTables expects the data to be returned as a two-dimensional array named
aaData.
In my own database, I've created a table like this:
CREATE TABLE `cities` (
`ccode` char(2) DEFAULT NULL,
`city` varchar(87) DEFAULT NULL,
`longitude` float DEFAULT NULL,
`latitude` float DEFAULT NULL,
KEY `city` (`city`(5))
) ENGINE=MyISAM DEFAULT CHARSET=utf8
Most of the searching will be done on city names, so I've indexed city.
Initially, let's just extract the first page of information. Create a file called
get_data.php and save it in the same directory as tables.html:
<?php
// { initialise variables
$amt=10;
$start=0;
// }
// { connect to database
function dbRow($sql){
$q=mysql_query($sql);
$r=mysql_fetch_array($q);
return $r;
}
function dbAll($sql){
$q=mysql_query($sql);
while($r=mysql_fetch_array($q))$rs[]=$r;
return $rs;
}
mysql_connect('localhost','username','password');
mysql_select_db('phpandjquery');
// }
// { count existing records
$r=dbRow('select count(ccode) as c from cities');
$total_records=$r['c'];
// }
// { start displaying records
echo '{"iTotalRecords":'.$total_records.',
"iTotalDisplayRecords":'.$total_records.',
"aaData":[';
$rs=dbAll("select ccode,city,longitude,latitude from cities
order by ccode,city limit $start,$amt");
$f=0;
foreach($rs as $r){
if($f++) echo ',';
echo '["',$r['ccode'],'",
"',addslashes($r['city']),'",
"',$r['longitude'],'",
"',$r['latitude'],'"]';
}
echo ']}';
// }
In a nutshell, what happens is that the script counts how many cities are there in
total, and then returns that count along with the first ten entries to the client browser
using JSON as the transport.
A sample JSON result for this (formatted for easier reading) is this:
{
"iTotalRecords":2673762,
"iTotalDisplayRecords":2673762,
"aaData":[
["ad","aixas","42.4833","1.46667"],
["ad","aixirivali","42.4667","1.5"],
["ad","aixirivall","42.4667","1.5"]
/* and 7 more... */
]
}
The three parameters in this JSON array are:
- iTotalRecords is the total number of records in the database
- iTotalDisplayRecords is the total number after filtering (explained later)
- aaData is a two-dimensional array of data that corresponds to the rows and
columns of the shown table
For the moment, iTotalRecords is equal to iTotalDisplayRecords. Later in the
chapter, we'll see how this would change.
If you have done like I did, and have installed a very large database, you'll see
the first ten entries of the table appear in the browser several seconds after
the page opens.
This is because it is much slower to build a list of items from a database than it is
to just read the list directly from a file.
We'll address that now, and will come back to pagination, ordering, and
searches afterwards.
Caching your database queries
In any large database, it is important to cache your queries. Database calls can be
quite expensive, even after careful tuning.
In the database that I set up for this, there are over two million rows, and queries
can take seconds to complete (tens of seconds if no indexing is done).
While modern databases do include caching engines for popular queries, it is much
better to simply open and read a file that contains cached information that translates
to "There are 2673762 rows in this database" than to have the database actually count
the rows.
We will create a very simple caching mechanism that takes the requested query,
encodes the query to a string using MD5, then returns the cached query if it exists,
and creates the cache if not. Remember, MD5 returns a pseudo-random string of
characters that can be used to save a cache with a unique name.
A nice thing about cities and countries is that the data does not change very quickly.
So, it would not be necessary to clear the cache all that often—after a few hundred
queries through it, it should be quite fast, as the most common searches will be
cached quickly, and non-cached searches will increasingly only be for rarer requests.
Server-side code
So, let's add the caching functions. Add this to the top of get_data.php:
// { caching functions
function cache_load($md5){
if(file_exists('cache/'.$md5)){
return json_decode(file_get_contents('cache/'.$md5), true);
}
return false;
}
function cache_save($md5,$vals){
file_put_contents('cache/'.$md5, json_encode($vals));
}
// }
These functions read and write from a directory named cache contained in the
same directory as the file itself. Create the directory and make sure it is writable by
the server.
The reason I use json_encode instead of serialize is that if I ever want to pass
the data directly back to jQuery without re-encoding it, then JSON is perfect. With
serialize, I would need to decode it in PHP, then re-encode as JSON before
sending it on.
Now, we need to change the database querying functions so that they read from the
cache if possible. Change the two functions to:
function dbRow($sql){
$r=cache_load(md5($sql));
if($r===false){
$q=mysql_query($sql);
$r=mysql_fetch_array($q);
cache_save(md5($sql),$r);
}
return $r;
}
function dbAll($sql){
$rs=cache_load(md5($sql));
if($rs===false){
$rs=array();
$q=mysql_query($sql);
while($r=mysql_fetch_array($q)) $rs[]=$r;
cache_save(md5($sql),$rs);
}
return $rs;
}
Note that this caches all queries—if you are using this in a project where the data is
more volatile, then you will want to change the caching method to only cache data
you are sure is likely to be static, and to allow for easy clearing of the cache in case of
data changing.
Before we carry on, you should verify for yourself that there is a marked
improvement in the speed. Load up the page a few times first using the non-cached
version, and time it, and then load up the cached version a few times. On very large
data sets, the speed difference should be very obvious.
If you overwrote your cache, you can emulate this, by manually removing the
server's cache after each page load. |
