How to create advanced html tables using TableGear

by Jean. 27 Comments -

Tables are a very important part of HTML, but they can also be a pain to deal with. Using TableGear, you can create dynamic tables that can be edited “on the fly” and sort according to your needs. And all that, with almost no effort.

What we’re going to do

Any web developer had, at one point in their career, work with HTML tables. Not only they were intensively used to create website structure (bad practice!), but they allow you to display tabular data, which is an important part of most websites : For example, think about a checkout page on an e-commerce site. Generally, the customer order is displayed using a table.

In order to save time and efforts while working with HTML tables, we are going to use an Open-Source tool called TableGear. This very handy piece of code simplifies both the back end coding (getting data from the database and displaying it within an HTML table) and the front end coding (super cool functions such as live edition with Ajax saving)

Your first supercharged table using Tablegear

Well, enough talk for now. Let’s get our hands in code!

1. The first thing you have to do is to download your Tablegear copy on the website. Once you have it, unzip the archive. You’ll see 4 directories:

  • src which contains the php files
  • stylesheets which contains CSS files
  • images
  • javascripts

2. Now, create a file in the root of the TableGear1.5.2 directory. Let’s call it table.php.

3. Paste the following code into your table.php file:

<?php
include("src/TableGear1.5.2.php");

$table = new TableGear(array(
	"database"=> array("username" => "USERNAME",
	"password" => "PASSWORD",
	"database" => "DATABASE",
	"table" => "TABLE",
	"key" => "PRIMARY_KEY"
        ),
        "editable" => "all",
        "sortable" => "all"
); 

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
	<title>TableGear Demo</title>
	<link type="text/css" rel="stylesheet" href="stylesheets/TableGear.css" />
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
</head>
<body>
  <?= $table->getTable(); ?>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/mootools/1.2.3/mootools-yui-compressed.js"></script>
<script type="text/javascript" src="javascripts/TableGear1.5.2.js"></script>
<?= $table->getJavascript(); ?>
</body>
</html>

Don’t forget to set-up your database parameters on line 4:

  • username : Your DB username
  • password : Your DB password
  • database : Your database name
  • table : the DB table where the data you want to sort is stored
  • key : Your table primary key

4. Save the file and launch it in your web browser. Without coding a single HTML “table” line, we have all our data from the database nicely sorted within an html table. The table is also editable and sortable.

Great, isn’t it?

Enhancing the table

Although what we have done is very cool, TableGear can do a lot more than this. For example, it can format dates, uses textareas instead of textfields, and more.
Below is the list of parameters that can be used:

/*  Columns that use selects instead of inputs. */
"selects" => array("FIELD" => "increment[min=1,range=5]"),

/*  Columns that use textareas instead of inputs. */
"textareas" => array("FIELD"),

/*  Data formats. Formats include "date", "currency", "numeric", and "memory". */
"formatting" => array("DATE" => "date", "PRICE" => "currency[prefix=$]", "MEMORY" => "memory[auto]"),

/*  Formats to accept as input from users. This can be a bit more complicated.
 *  The "date" input format for example makes educated guesses about user input dates and standardizes them to
 *  a MySQL format. Numeric will grab a full number from user input, for example 12345 from $12,345. */
"inputFormat"	=> array("DATE" => "date", "PRICE" => "numeric"),

/*  Validates user input and won't allow anything that doesn't validate.
 *  This example will only accept a string that ends in .jpg */
"validate" => array("FILENAME" => "/\.jpg/")

/*  Uncomment this if you want the user to be able to delete rows. */
"allowDelete" => true,
"deleteRowLabel" => array("tag" => "img", "attrib" => array("src" => "images/icons/delete.gif", "alt" => "Delete Row"))

/*  Uncomment this if you want to use pagination in your table. Very useful for large data sets. */
"pagination" => array(
	"perPage" => 5,
  	"prev" => "Previous",
  	"next" => "Next",
 	"linkCount" => 4,
)

Don’t forget to have a look to TableGear website where you can find more tutorials.

Comments (27) - Leave yours

  1. Sarbjit Singh said:

    Wow!!

    It could not get easier than that..

    I will have to check if it can limit the number of entries based on a column value like a date. This will make life a lot more easier.

  2. Josh Stauffer said:

    Very cool! I am wondering if this could be used with WordPress. I have a few WordPress sites that use a lot of custom fields. It would be neat if I could see the post name along with the custom field values in the table. Then edit them appropriately. Now my mind is going. :-)

  3. Artful Dodger said:

    0o.. sounds quite good even though I got out of using tables a long time I go I can still remember the times when I was using Dreamweaver to create websites using tables… what a pain in the back side. I’m sure this would make it alot easier though so I’ll give it a shot =]

    Thanks.

  4. David Cowgill said:

    This is pretty cool and would be very useful for building WordPress custom admin pages. All that it needs is

    1) To use jquery instead of mootools
    2) Have support for the $wpdb-> function

    Then this would really take off and I’d use it on my premium WordPress theme, ClassiPress.

Leave a Reply

Your email address will not be published. Required fields are marked *

Please respect the following rules: No advertising, no spam, no keyword in name field. Thank you!