31 May 2009

Simple loading of HTML fragment using jQuery AJAX

jQuery provides a simple load() function to load an HTML fragment using AJAX. To test it, create a test data file called data.html:

<html>
  <body>
    <ul>
      <li>Item 1</li>
      <li>Item 2</li>
      <li>Item 3</li>
      <li>Item 4</li>
    </ul>
  </body>
</html>

Next, create a target HTML file called testLoad.html in the same folder with the following jQuery statement:

<html>
  <head>
    <title>jQuery load HTML test</title>
    <script type='text/javascript' src='js/jquery-1.3.2.min.js'></script>
    <script type="text/javascript">
      $(document).ready(function() {
        $('#myLinks').load('data.html ul li');
      });
    </script>
  </head>
  <body>
    <ul id="myLinks"></ul>
  </body>
</html>

When you view testLoad.html, you should see the list items in data.html inserted into the target file, in the unordered list named myLinks.

It was a little fiddly to get this working the first time. If your source HTML file has an error, nothing seems to happen; in that case, check your browser's error console to see what went wrong. For instance, in Firefox, when my source HTML file wasn't well-formed, I found this error:

Error: mismatched tag. Expected: </ul>.
Source File: 
Line: 8, Column: 5
Source Code:
  </body>

Another thing I found is that you can't include an XML processing instruction (<? ... ?> lines) in your data file because when load() tries to insert your XML file into your target document, you would get an error like this:

Error: XML or text declaration not at start of entity
Source File: 
Line: 1, Column: 43
Source Code:
<div xmlns="http://www.w3.org/1999/xhtml"><?xml version="1.0" encoding="UTF-8"?>

29 May 2009

Selecting parent or ancestor of a node in jQuery

When manipulating an HTML document (especially one that you didn't generate), it can be easier to find a node by matching its descendant's unique id or class attribute and value first, then selecting that descendant's ancestor (which is the node you wanted to in the first place), compared to finding that node by referring to its position in the DOM, which is not obvious and isn't easy to maintain.

For example, in the Australian Government Bureau of Meterology site, you might want to highlight temperature and forecast for Melbourne, so the simplest thing to do is change the style of the row containing that string, but there's no unique attribute you can use to select that row (or tr node):

<tr>
…
</tr>
<tr>
  <td>
    <div>
      <table>
        <tbody>
          <tr>
            <td><a href='…'>Sydney</a></td>
            <td>20</td>
            <td>Shower or two</td>
          </tr>
          <tr>
            <td><a href='…'>Melbourne</a></td>
            <td>16</td>
            <td>Shower or two</td>
          </tr>
          …
        </tbody>
      </table>
      <table>
      …
      </table>
    </div>
  </td>
</tr>

For this site, the trick is to select the td node containing Melbourne, then find the first tr ancestor. Here's a sample script using jQuery:

// ==UserScript==
// @name           www.bom.gov.au Highlight City
// @namespace      kamhungsoh.com
// @description    Highlight row of a specific city.
// @require        http://ajax.googleapis.com/ajax/libs/jquery/1.3/jquery.min.js
// @include        http://www.bom.gov.au
// ==/UserScript==

$("a:contains(Melbourne)").each(function() {
  $(this).parents('tr:eq(0)').css('background-color', 'grey');
});

In this script, for each a node with a value of Melbourne, find the first tr parent using :eq(0) and change its background colour. You have to constrain the selection of parents to the first parent, otherwise all the tr ancestors will be selected; on this site, nested tables are used for layout so without this constraint, the enclosing tr node of the table will also be found and modified.

27 May 2009

Zebra-stripe table rows with jQuery's custom selectors

I thought I had a simple way to zebra-stripe table rows in jQuery:

$('table tbody tr').each(function(i) {
  $(this).addClass(i%2 ? 'OddRow' : 'EvenRow');
});

While I was looking up jQuery selectors, I found that the library has two custom selectors, :odd and :even, to select the odd and even elements in a matched element set, respectively, so you could zebra-stripe a table like this:

$('table tbody tr:odd').addClass('OddRow');
$('table tbody tr:even').addClass('EvenRow');

The second example seems a little more obvious.

See Also

26 May 2009

Read 'The Economist' article comments earliest first

A simple GreaseMonkey script using jQuery to show the comments to articles in The Economist, from earliest first. Basically, this script looks for <a> tags with specific text in their href attribute and appends &sort=asc. It's shorter and easier to read than the original plain Javascript version.

See Also

25 May 2009

CSS @media for Firefox Stylish Add-in

In an earlier entry, I wrote about using the @media print rule in a CSS file to insert the value of URLs in the print version of a web page. If the webmaster of the site didn't provide this rule, can you still print the URLs?

If you are using the Firefox Stylish add-in, you can provide your own CSS file to apply to a web site. I tried this:

@namespace url(http://www.w3.org/1999/xhtml);

@-moz-document domain("www.gamedev.net") {
  @media print {
    a:after { content:" (" attr(href) ")"; }
  }
}

… but it didn't work. It seems that the Mozilla / Firefox @-moz-document rule doesn't allow @-rules within it (see the Mozilla links below).

If you move the @media print outside of @-moz-document rule, as below, then any page that you print will include URL strings in the output.

@namespace url(http://www.w3.org/1999/xhtml);

@-moz-document domain("www.gamedev.net") {}

@media print {
  a:after { content:" (" attr(href) ")"; }
}

The only way to manage this behaviour is to disable that stylesheet in Firefox's User Styles page of the Add-ons dialog.

See Also

24 May 2009

XPath selectors no longer supported in jQuery

It's old news, but being new to jQuery, I didn't realise that XPath selectors were no longer supported by this library until I read Upgrading to jQuery 1.2 (jQuery is now up to version 1.3).

23 May 2009

Show URL When Printing a Page

When you print a web page, you'd naturally expect to see the URL of a link, instead of just the name of the link (usually the underlined text). If you are the webmaster of a site, you could generate a different page formatted for printing (for example, look for the print link in many news sites). Another way is to add the following rule into your CSS file:

@media print {
  a:after { content:" (" attr(href) ") "; }
}

The rule above will append the value of the URL after the link. How does it work?

@media print
Specifies the media type (in this case, for printing) for the following set of statements in braces.
a:after
Instructs the CSS engine to select the <a> tag, while :after is a pseudo-element that instructs the CSS engine to insert some content.
{ content:" (" attr(href) ")"; }
Inserts (or generates) the value of the href attribute, in parentheses, into the output.

You can see the effect of this rule in my web site; just use the Print Preview function in your browser and examine the text after each link.

See Also

21 May 2009

Playing with PHP Data Objects (PDO)

Just migrated the database access code of my PHP sample page from the original (and old) mysql_* functions to PHP Data Objects (PDO). Some advantages of making this change:

  • You can write more general code because your application is not tied to a specific PHP database extension library.
  • PDO is similar to other database APIs, such as ADO or JDBC, so it was easier for me to write code in PDO than using, say, the PHP MySQL API.
  • You can use PHP try … catch blocks, so you can remove the clutter related to testing the return flag of a function. After instantiating a PDO class, $pdo, call $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION) as shown in Error Handling section. Note that as per this comment, exceptions may only work in the database driver supports it.

As per the PDO introduction, PDO is not a complete data abstraction layer. You can't simply connect to another vendor's DBMS and expect your code to work. For instance, you still have to write SQL queries that the DBMS can understand. One way to tackle that is to encapsulate all database-specific information into a class and call methods to obtain queries for that specific database.

18 May 2009

Configuring Apache and PHP libraries

Problem when configuring Apache, PHP and MySQL on Windows XP. Here's my environment:

  • Microsoft Windows XP Professional Service Pack 3
  • Installed apache_2.2.11-win32-x86-no_ssl.msi as a service.
  • Installed php-5.2.9-2-win32-installer.msi
    • Used Apache 2.2 option
    • Added MySQL and MySQLi extensions.
  • Installed mysql-essential-5.1.34-win32.msi.

Checked that the PHP configuration file, php.ini, has the following entries to support MySQL and MySQLi extensions:

extension_dir ="C:\Program Files\PHP\ext"
…
[PHP_MYSQL]
extension=php_mysql.dll
[PHP_MYSQLI]
extension=php_mysqli.dll

Checked that the folder C:\Program Files\PHP\ext has the following files: php_mysql.dll and php_mysqli.dll.

When starting Apache, its errors.log has these messages:

[Fri May 15 14:41:03 2009] [notice] Apache/2.2.11 (Win32) PHP/5.2.9-2 configured -- resuming normal operations
[Fri May 15 14:41:03 2009] [notice] Server built: Dec 10 2008 00:10:06
[Fri May 15 14:41:03 2009] [notice] Parent: Created child process 3868
PHP Warning:  PHP Startup: Unable to load dynamic library 'C:\\Program Files\\PHP\\ext\\php_mysql.dll' - The specified module could not be found.\r\n in Unknown on line 0
PHP Warning:  PHP Startup: Unable to load dynamic library 'C:\\Program Files\\PHP\\ext\\php_mysqli.dll' - The specified module could not be found.\r\n in Unknown on line 0

It means that the required PHP extensions could not be loaded. It doesn't matter how I change change the value of extension_dir, for example using forward slashes instead of backslash, or using double backslashes, or a relative path, and then restarting the Apache service; the same type of error appears and the required extensions aren't loaded. In the end, I restarted Windows and then Apache loaded the libraries!

Once I got my configuration working, I changed the folder name and the value of extension_dir, and restarted Apache. Unfortunately, my system kept working, so I'm none the wiser.

11 May 2009

Rename Folder in Google Reader

Oh, erm … you can't rename folders in Google Reader. The workaround is to move all your subscriptions from one folder to another folder, then delete the original one. Here's the steps:

  1. In the Subscriptions panel, select Manage Subscriptions. The Settings page should be displayed.
  2. In the Settings page, filter your subscriptions by the name of folder you want to remove using the Filter by name, folder, or URL text box.
  3. Using the Change Folders… drop down list for each subscription, select the new folder and unselect the old folder. Note that for the first subscription, you have to create the new folder.
  4. After reassigning all the required subscriptions to the new folder, select the Folders and Tags tab.
  5. In the Folders and Tags, check the folder you want to delete, then press the Delete selected button.

See Also

08 May 2009

Modify URL to Read Comments From Beginning

When I read responses or comments to articles, I prefer to read them from the earliest to the latest. Some sites order comments in reverse (that is, latest to earliest). If a site's comment link can take an 'order' argument, just modify that URL to specify your preferred order and save yourself an extra click. Below is a sample GreaseMonkey script that can specify the order of comments in The Economist, which are ordered from latest to earliest, by default.

var pattern = 'mode=comment';
var sortOrder = '&sort=asc';
var links = document.getElementsByTagName('a');
for (var i = links.length - 1; i >= 0; --i) {
  var link = links[i];
  var url = link.getAttribute('href');
  if (url.search(pattern) != -1) {
    link.setAttribute('href', url.replace(pattern, pattern + sortOrder));
  }
}

To use it on other sites that have similar URLs, just modify the values of pattern and sortOrder.

06 May 2009

Simple Data Grid Using PHP and jQuery

Introduction

This article walks though the process of writing a simple Web-based data grid application for browsing a database table, using the PHP Web scripting language, jQuery Javascript library and MySQL DBMS.

Requirement and Design

The grid is just an HTML table, with filters for each column and pagination controls at the bottom of the table. The filters are implemented using drop down lists, and the pagination can be done using two buttons, one to move to the next page, the other to move to the previous page of data:

+-------------+-------------+
|Column header|Column header|
| Filter      | Filter      |
+-------------+-------------+
|Data row1                  |
|Data row2                  |
|Data row3                  |
+-------------+-------------+
|Pagination controls        |
+-------------+-------------+

Two other requirements are to ensure that the data grid can be used with or without Javascript, and that it can be used in Firefox, MSIE and Opera.

One We Prepared Earlier …

To orient you, check out the data grid implementation first. Try it with Javascript enabled and disabled on your browser. When Javascript is disabled, you have to first select a filter value, then press the Submit button before the grid is updated.

When Javascript is enabled, just changing a filter value will update the grid and you can reset all the filters at once by pressing the Reset button. Also, the presentation is slightly enhanced by zebra-striping the rows to make them easier to view.

As you filter or paginate the data, the SQL query, below the grid, is updated.

Test Data

To allow us to develop and test the grid, we create some test data in the MySQL database. I use the same schema and and data from an earlier posting. Below is the SQL statement to create the table:

create table if not exists p0020_shirt (Region varchar(8), Category varchar(8), Shirt_Style varchar(8), ShipDate date, Units integer, Price decimal(4,2), Cost decimal(4,2));

We add data into the table using a series of insert statements, like the following:

insert into p0020_shirt values ('East','Boys','Tee',date('2005-01-01'),11,5.25,4.66);
insert into p0020_shirt values ('East','Boys','Golf',date('2005-01-01'),12,5.26,4.57);
insert into p0020_shirt values ('East','Boys','Polo',date('2005-01-01'),13,5.27,5.01);
insert into p0020_shirt values ('East','Girls','Tee',date('2005-01-01'),14,5.28,5.01);
…

We filter and paginate our data in one select statement using the where, and limit and offset clauses:

select * from [table reference] [filter] limit [number of rows] offset [position]

The filter is just a where clause, generated when the user selects a column and value to filter the rows.

The filter controls are implemented using drop down lists, and to populate them, we fetch all unique values for each string column using the distinct option in a select statement, such as: select distinct [column name] from [table reference] order by 1 asc.

When populating the drop down lists, we should also let the user reset the filter. To this end, we add a dummy value 'All' into the drop down list. Rather than having a special step in the PHP code when it generates the drop down lists, we can use union to combine the results of two select statements into one result set, then use a loop to populate the drop down list:

select 'All'
union
select distinct [column name] from [table reference] order by 1 asc

For example, the statement above would generate a list like (All, East, North, South, West).

The pagination controls just modify the offset position by adding or subtracting a constant and the current offset. We can easily stop the user from paging before the first row by ensuring that the offset value is always 0 or more. To stop the user from paging beyond the last row, we use another query to count the number of rows a query would return: select count(*) from [table reference] [filter]. (In the PHP code, we also pad the table with empty rows if there are fewer rows in the dataset than the standard number of rows so that the height of the table doesn't change in the last page.)

PHP Data Grid Implementation

By moving as much of the logic into SQL statements, the PHP implementation of the data grid control is straightforward. If you view the PHP source code, you will see that the entry point, the main() function, initializes variables using default values or from a previous form submission, connects to the database, then paints the drop down lists, data rows and pagination controls.

Enhance Data Grid UI with Javascript and jQuery

HTML forms should be viewable and usable without Javascript. If Javascript is enabled, then we can enhance the presentation and usability. With this in mind, the PHP code should just generate the non-Javascript form and create no event handlers, and if Javascript is enabled, the browser should use Javascript to add event handlers to HTML elements.

One library that makes it easier to manipulate the DOM in a browser is jQuery. For instance, the laborious DOM function calls such as document.getElementsById() are replaced by simpler ones such as $().

In this data grid example, the Javascript functions to enhance the presentation or add interactivity can be found page's head element.

Perhaps one odd feature is to hide the Submit button if Javascript is enabled. If Javascript is disabled, then the user must press the Submit button to submit the form. On the other hand, if Javascript is enabled, just changing the drop down list causes the onchange event handler to submit the form, enabled, so the Submit button is redundant.

Unexpected Problems

While developing this data grid control, I stumbled upon two unexpected problems.

The first problem is that the form can't reset select elements (the filters) to the first option in the list. Resetting the select elements just sets them to the default option, which is the option chosen when this form is generated in PHP.

The second problem was the Internet Explorer Submit Button Bug.

Improvements

One obvious improvement to this data grid is to reduce the number of database queries required just to update the data rows or filters. At the moment, there are 5 queries (one for each filter, one for the data rows, and one for counting the number of rows) each time the page is updated. While this is not a problem for a small dataset or a small number of users, it may quickly overload a server with a lot of users and more complex queries. This improvement can be implemented using AJAX to update each control without refreshing the entire page.

Another improvement is to auto-generate the filter lists based on rules of the number of unique values and the column types. For example, a filter for a text column could allow the user to enter a regular expression or to auto-complete as the user enters more letters. A filter for numeric values could automatically generate quartiles. Finally, a filter for dates could automatically contain months, quarters or years.

Conclusion

This article has presented a way to implement a simple data grid control using PHP and jQuery. SQL queries are used as much as possible to simplify the page generation logic in PHP. PHP is used to provide the business logic to query the database and generate a basic form. jQuery (and Javascript) are used to enhance the usability and presentation of the page. I'll keep exploring this approach to see if it would make it easier to develop and maintain.