-
Notifications
You must be signed in to change notification settings - Fork 6
TableGear.php
The TableGear PHP class handles interaction with the database, submitted data, and data formatting. There are a lot of customizable features in this powerful class.
A web server running PHP 4.1 or later. Database interaction requires MySQL.
Although written in PHP, the class is designed to be familiar to MooTools users. After including the source file, the constructor takes only a single options object (an associative array in PHP). This means that the most basic usage is:
include "TableGear.php";
$tg = new TableGear();Due to the way this class handles AJAX calls, it's important to put the constructor at the very beginning of your php document before any other data is output. If you forget to do this, the table fields will most likely not update. This will create a new instance of TableGear, but without any data in it, which is not so useful. More commonly, you will want to pass it information about your database:
$tg = new TableGear(array(
"database" => array(
"username" => "USERNAME",
"password" => "PASSWORD",
"database" => "DATABASE",
"table" => "TABLE"
)
));USERNAME, PASSWORD, and DATABASE are the minimum required parameters to be able to connect to the database. By passing a TABLE in the constructor, TableGear will fetch the data from that table automatically.
Your data is now loaded and ready to go! When you are ready to output it, simply call the getTable() method:
$tg->getTable();That's it! You now have a valid XHTML table that represents your data.
By default, all fields in the table are editable except the auto-incremented primary key (if one exists). If you want to change this, you can pass the constructor the editable parameter. This will tell TableGear which fields can be changed. This parameter can be an array containing either field names (as they appear in the database) or column numbers (as they appear in the table). To make all fields editable, simply pass all:
$tg = new TableGear(array(
"database" => array(
"username" => "USERNAME",
"password" => "PASSWORD",
"database" => "DATABASE",
"table" => "TABLE"
),
"editable" => "all"
));
Changing the editable parameter to have even one editable field will immediately have a number of effects. First, instead of just a table, getTable() will output a form with the table inside. Second, it will output form elements (text inputs by default) inside each editable cell, and set the class to editable. Finally, it will add a column to the left side of the table with checkbox inputs inside. This column is for editing rows, and is for browsers that don't have Javascript. If you don't like the look of these boxes, don't worry, they will be hidden when TableGear.js is applied.
The sortable option also takes the same parameters as editable: an array with field names or column numbers, or all.
$tg = new TableGear(array(
"database" => array(
"username" => "USERNAME",
"password" => "PASSWORD",
"database" => "DATABASE",
"table" => "TABLE"
),
"editable" => "all",
"sortable" => "all"
));
That's it! Let's review what we've got. We now have a valid XHTML table that is a representation of a table in our database. Even though we haven't applied the Javascript yet, we can already edit the values in our database by checking the box of the rows we want to edit, changing the appropriate field, and clicking on update. More importantly, the output XHTML is now ready to accept TableGear.js, which will bring everything together!
Go on to the final step of applying TableGear.js...
...or keep reading to learn how to customize your table further!
All options are passed to TableGear in the constructor. Much of TableGear's power lies in its ability to let you easily customize and transform your data through the use of these options. Most of them require custom data types (read below). Also try clicking on the headers of this table.
| Option | Accepts | Default | Description |
|---|---|---|---|
| database | Object | null | See Database Options for more. |
| form | Object | null | See Form Options for more. |
| sortable | Column Array or all
|
null | Defines sortable fields. |
| editable | Column Array or all
|
All except auto increment | Defines editable fields. |
| selects | Column Object | null | Defines select elements. For each column specified, you can pass an array, an object, or a parameter string. For more information, see adding select elements. |
| textareas | Column Object | null | Defines textfield elements. For each column specified, you must pass an object of type rows (the textfield's "rows" attribute) or cols (the textfield's "cols" attribute). |
| editRowLabel | HTML Object | null | A label for the checkbox elements for editing rows. |
| deleteRowLabel | HTML Object | null | A label for the checkbox elements for deleting rows. If an image element is used here, TableGear.js will turn it into a button for deleting rows. |
| allowDelete | Boolean | true | When this is set to true, rows can be deleted by clicking on the deleteRowLabel. |
| autoHeaders | Boolean | true | Automatically gets the headers from the data field names. |
| readableHeaders | Boolean | true | Creates readable headers from camelCased and underscored field names when "autoHeaders" is on. |
| processHTTP | Boolean | true | Process data submitted by HTTP. You can turn this off if you need to do this manually. |
| totals | Column Array or all
|
null | Defines column totals. Totals are placed in the tfoot element. When you define a total on a column, TableGear will pass it back after each AJAX call, calculating the new total after a field has been edited. |
| formatting | Column Object | null | Formats the data before it is output to the browser. For each column specified, you must pass a parameter string. For more information, see data formatting. Specifying a data format will also add that data type to the column header's class name. This will let TableGear.js know how to sort it. |
| inputFormat | Column Object | null | Formats user input before it is sent to the database. For each column specified, you must pass a parameter string. For more information, see input formats. Note: Columns cannot be specified by number. |
| transform | Column Object | null | Transforms data before it is output to the browser. More customizable than formatting. For each column specified, you must pass an HTML object. For more information, see data transforms. |
| callback | Object | null | Calls a custom function, referenced by a string, after performing certain actions on the database. For more information, see callback functions. The following callback functions can be specified: onInsert Called after a row is inserted. onUpdate Called after a row is edited. onDelete Called after a row is deleted. |
| title | HTML Object | null | A title for the table. Creates a single header at the top of the thead element with the class "title". |
| headers | Column Object | null | Defines custom headers. For each column specified, you must pass a special object of the following type: id An id to give the th element. class A class added to any other existing classes. html An HTML object for the headers contents. |
| footers | Column Object | null | Defines custom footers. These will be added to the tfoot element below any totals. For each column specified, you must pass an HTML object. |
| columns | Column Object | null | Adds classes to columns of data, so that they can be styled differently with CSS. This will include totals but not headers or footers. For each column specified, pass a string that will become the column's class. |
| shiftColumns | Object | null | Allows column positions to be shifted so that they don't have to match the database. This option is a shortcut that will call the shiftColumn() method when TableGear is initialized. |
| custom | Object | null | Defines custom tags, passed as HTML objects, to use in a specific position, passed as follows: FORM_TOP Just inside the opening form tag. TABLE_TOP Just outside the opening table tag. TABLE_BOTTOM Just outside the closing table tag. FORM_BOTTOM Just inside the closing form tag. |
| pagination | Object | null | This allows you to paginate your table. See more below. |
| validate | Column Object | null | Validates each field/column specified against a regular expression. If the expression evaluates to false, the field will not be updated. |
| noDataMessage | String | "- No Data -" | The message that appears when no rows are in the table. |
| indent | Number | 0 | The base indent of the HTML in the source code. |
| blockEditable | Boolean | false | Defines block-level (div) elements inside editable cells, instead of the default span elements. |
| hotText | Column Array | null | Adds compatibility with HotText by adding the class "hotText" to columns. A shortcut for columns. |
| Name | Description |
|---|---|
| host | The database host. Optional (defaults to localhost). |
| name | The database to select. Required. |
| username | The database username. Required |
| password | The database password. Required but can be blank. |
| table | Database table to use. Required. |
| fields | Passing an array here will limit the output to the fields you indicate. |
| sort | The field to sort the database on. Add [DESC] after the field name for descending order. |
| noAutoQuery | Suppresses the first query that automatically fetches all rows. Use this if you plan on limiting your row sets with a custom query. |
| Name | Description |
|---|---|
| url | Form submit url. Default is PHP_SELF. |
| method | Form submit method. Default is post. |
| id | ID attribute for the form. Default is null. |
| class | Class attribute for the form. Default is null. |
| submit | Submit button text. Default is Update. |
Object - First off, the term "object" refers to an associative array. PHP does not have object literals – this is just a term of convenience for people who are familiar with Javascript. It also serves to differentiate associative and non-associative arrays. They are generally not mutually compatible, so when an option requires an "object" make sure first that the array really is associative. The most simple option that requires an object is database:
$tg = new TableGear(array(
"database" => array(
"username" => "StevenSegal",
"password" => "kungFuChop",
"database" => "jujitsu"
)
));Column Array - A "column array" is an array of either field names as they appear in the data, or column numbers as they appear in the table. Either can be used so if, for example, you want to make both the quantity column and the 3rd column in the table sortable, you would pass the following option:
$tg = new TableGear(array(
"sortable" => array("quantity", 3)
));Column Object - A "column object" is similar to a column array, except that it is an "object", so columns are specified as keys in the array. This is required when more arguments are needed. The footers option lets you add custom footers to your table, and takes a column object, so if you wanted to add footers to the quantity column and the 3rd column in the table, you would pass a column object for this:
$tg = new TableGear(array(
"footers" => array(
"quantity" => "Quantity",
3 => "I'm Column Three!"
)
));In addition to names and numbers, you can also pass the constants EDIT or DELETE to specify the columns with the checkboxes for editing and deleting rows. These constants are useful for things like adding headers to the edit and delete rows. Note that they must be all caps.
Note that the data that is passed to each column, in this case "Quantity", and "I'm Column Three!", is contextual. Here it is a string, but depending on what is required it could be an array or another object.
HTML Object - HTML objects can be a very powerful tool. They are objects that represent an HTML element. Each HTML object has three properties: tag specifies the element's tag name, attrib the element's attributes, and html holds the contents. These objects are nestable, which means that at any point where HTML objects are allowed, you can have as much of your own custom HTML as you want!
The best example of this is the custom option, which lets you put custom tags at key points. Let's say you wanted to insert complex, nested HTML elements just inside the form tag:
$tg = new TableGear(array(
"custom" => array(
"FORM_TOP" => array(
"tag" => "div",
"attrib" => array("class" => "preamble"),
"html" => array(
array(
"tag" => "p",
"attrib" => array("id" => "someID"),
"html" => "Look at me!"
),
array(
"tag" => "img",
"attrib" => array("src" => "file.jpg", alt => "A picture!")
)
)
)
)
));This code is simply saying "inject this custom HTML just inside the form tag". We could even rewrite it to define our HTML object somewhere else, and pass it as a variable:
$tg = new TableGear(array(
"custom" => array(
"FORM_TOP" => $someCustomHTML
)
));This will inject the following HTML just after the opening form tag:
<div class="preamble">
<p id="someID">Look at me!</p>
<img src="file.jpg" alt="A picture!" />
</div>Passing HTML this way has certain advantages, including quickly parsing the HTML for necessary data (without resorting to regular expressions) and keeping track of indents for readable code. HTML objects really start to shine when working with data transforms, where you can inject data from your database at any point in your own custom HTML tags!
Two more things should be noted about HTML objects. First, despite being called "objects", they will always occur in an HTML context, so you can actually pass a string instead of an object. An example of where you would want to pass an HTML object as a string is the editRowLabel option, where you will most likely just want to output text inside the label element. Second, inline elements like span and a will still need to be written out as HTML, as they occur randomly inside text nodes.
Parameter String - Three of the more advanced options in TableGear, selects, formatting, and inputFormat, are highly customizable and require a number of parameters. To pass them, you can use a parameter string in the following format:
param[s1=value,s2=value,s3]
Here, param is the parameter, and s1, s2, and s3 are sub-parameters. Note that s3 is a boolean, so the equal sign is not required. Also, do not include the square brackets [] when no sub-parameters are passed. Parameter names are contextual – they depend on which option you are using. For more information on the parameters you can use, see advanced features.
Although nearly all of the functionality of TableGear.php is handled through the options object passed in the constructor, there are still some special cases where this isn't enough. The following public methods may come in handy in such cases:
TableGear->getTable()Outputs an XHTML-valid table (and form element, if applicable) to the browser. This is the only method that you will want to use 100% of the time.
TableGear->connect()This method connects (or re-connects) to the database using the information provided in the TableGear->database object. This is provided as a public method in case the database info cannot be provided in the constructor, or perhaps if you need to connect to a different database after creating the object.
TableGear->query($query)Performs a query on the database using the active connection. This method is probably not very useful by itself, but is provided in case special queries need to be made.
TableGear->fetchDataArray($query)Performs a (typically SELECT) query on the database, then loads the resulting data into TableGear. Probably the most useful of the three public database-related methods. This method should be called for complicated SELECT queries such as JOINS, WHERE clauses, etc., as well as limiting row sets.
If you use this method for this purpose, you should also pass the noAutoQuery parameter to the database object to prevent it from automatically fetching every row in the table by default.
TableGear->injectColumn($array, [$position], [$fieldName])This method splices a column of data into your table from another source.
$array must be a plain, non-associative array. If the length of $array is shorter than the number of rows in the table, it will inject blank cells to fill up the column. If it is longer than the number of rows in the table, the data will be truncated.
$position can be a number specifying the position to inject the column, or a string: first, last, before, or after. first injects the data into the first column in the table, last the last. before and after inject the data relative to other fields, specified in square brackets: before[someField].
$fieldName provides a name for the new column, which is used in the headers, as well as for specifying options using column objects.
TableGear->shiftColumn($column, $position)This method will shift a column to a new position. This is so that you can adjust your tables so that the columns don't have to occur in the same order as the database.
$column is the column in the table, specified by either a string field name or a number.
$position can be a number specifying the position to inject the column, or a string: first, last, before, or after. first injects the column into the first column in the table, last the last. before and after inject the column relative to other fields, specified in square brackets: before[someField].
You can pass the shortcut shiftColumns to the constructor, which will call shiftColumn() for each item:
$tg = new TableGear(array(
"shiftColumns" => array(
"cost" => 1,
"quantity" => "first",
"lastName" => "after[firstName]"
)
));
TableGear->getJavascript(library)Gets the Javascript necessary to initialize the table. library may be either jquery or mootools.
The standard features of TableGear are useful, but the advanced features are where it shines. Mastering these features will get you on the road to having total control over your data. All the advanced features except for callback need to be passed custom data types such as: column objects, HTML objects, and parameter strings. For more information on these, see custom data types.
Let's say you have a field body_parts in your database, and you want to limit the input for this field to: head, shoulders, knees, and toes. This is the perfect job for a select element. You can pass TableGear this select element using the selects option. Use a column object to specify the field, then pass an array to represent the select:
$tg = new TableGear(array(
"selects" => array(
"body_parts" => array("head", "shoulders", "knees", "toes")
)
)
));This will output the following HTML in each body_parts cell.
<select name="body_parts[KEY]">
<option value="head">head</option>
<option value="shoulders">shoulders</option>
<option value="knees">knees</option>
<option value="toes">toes</option>
</select>Note that KEY represents the primary key of the database, and will be different for each row. Also, if the data exists in the database, that option will be selected automatically. What if you need the value attributes to be different from the text displayed? Simply pass an object instead of an array...
$tg = new TableGear(array(
"selects" => array(
"position" => array("First" => 1, "Second" => 2, "Third" => 3)
)
)
));...which outputs:
<select name="position[KEY]">
<option value="1">First</option>
<option value="2">Second</option>
<option value="3">Third</option>
</select>Okay...that's all good, but what if you're using sequential numbers, and don't want to write out an entire array? Or if the array values need to be relative to the data? If you pass a parameter string specifying increment[sub], you can have a select automatically filled out for you! sub represents the sub-parameters, which are:
| Sub | Default | Description |
|---|---|---|
| min | Negative Infinity | The minimum value possible. |
| max | Infinity | The maximum value possible. |
| step | 1 | The amount that each option element increments. If step is 10, the elements will climb by tens: 10, 20, 30, etc. This number may also be a fraction. |
| range | 0 | The total number of option elements that you want to be inside the select element. Does not have any effect if abs is true. |
| abs | false | Defines absolute values. When true, option elements will be the same in every cell, otherwise they will be incremented relative to the current data. |
Let's try it out! Say you want to edit the age field of a site for teenagers:
$tg = new TableGear(array(
"selects" => array(
"age" => "increment[min=13,max=19,abs]"
)
)
));This will create the following select element:
<select name="age[KEY]">
<option value="13">13</option>
<option value="14">14</option>
<option value="15">15</option>
<option value="16">16</option>
<option value="17">17</option>
<option value="18">18</option>
<option value="19">19</option>
</select>In this example we set abs to true, as the possible options for a teenager's age will never change. But what if we had a huge range of possible values, say 1 - 100,000? Writing out this many option elements would be crazy (and could possibly break the internet), but we can use increment to define a range of values relative to our current data:
$tg = new TableGear(array(
"selects" => array(
"henchmen" => "increment[min=1,max=100000,range=4]"
)
)
));The sub-parameter abs is false by default, so we will get a different select element depending on what value is currently in the table. Let's say the value of henchmen in row 55 is 5345. This will result in the following select element for that row:
<select name="henchmen55">
<option value="5341">5341</option>
<option value="5342">5342</option>
<option value="5343">5343</option>
<option value="5344">5344</option>
<option value="5345" selected="selected">5345</option>
<option value="5346">5346</option>
<option value="5347">5347</option>
<option value="5348">5348</option>
<option value="5349">5349</option>
</select>Since the option elements are relative to the data and we specified 4 as the range, the current value is now the mid-point with four option elements on either side. But now let's see what happens if the current value for henchmen in row 342 is 2:
<select name="henchmen342">
<option value="1">1</option>
<option value="2" selected="selected">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
<option value="6">6</option>
</select>Since we specified the min parameter as 1, the option values will never fall outside this, even if the range does. The same will be true when the data approaches max.
As you can see, increment is a powerful tool for quickly injecting incremented select elements into your tables. If you experiment with it, you'll find that it has even more potential than meets the eye. As an example, the sub-parameters can be decimals as well as whole numbers, so you could potentially create a select that begins at 4.25 and increments up to 10.75 in steps of .25 each (good if you're counting quarters).
But wait, it gets even better! Although increment requires numeric values to be incrementable, data formatting can be applied on top of this to change the way this data is represented. For example, if you had a date field in your database that was in TIMESTAMP format, you could use increment to create select elements that increment the timestamp by 86400, and apply a date data format on top. The result would be a select element that increments by days (i.e. December 25, 2008, December 26, 2008, December 27, 2008, etc.)!
Most data is in a format that makes it easy to work with as data, but needs to be formatted to make sense to a user. The formatting option helps get the data into common formats before it is sent to the browser. There are currently four data format types, date, currency, numeric, and memory, which are passed as a parameter string. Sub-parameters are as follows:
| Parameter | Sub | Default | Description |
|---|---|---|---|
| date | string | F j, Y |
Specifies a date format for use with PHP's date() function. For more information on how to use this format, have a look at the PHP documentation. The default will output dates in the following format: March 10, 2004. PHP constants like DATE_RSS can also be used. |
| currency | precision | 2 | The precision to round the number to. Negative values can also be used for less precision. |
| pad | false | When true, pads the result to the value specified in precision. | |
| nocommas | false | When true, will prevent commas from being added to the number. | |
| prefix | null | A prefix for the currency, such as the dollar sign "$". | |
| suffix | null | A suffix for the currency, such as USD. |
|
| numeric | number | 0 | The precision to round the number to. Passing null here will insert commas into the numbers. |
| memory | auto | false | When true, the data will be displayed in its most appropriate unit. For example, "14,324,242,234b" would be displayed as "14gb". |
| decimals | 0 | The precision to round the number to. Negatives numbers will round to less precise units. | |
| unit | "b" | The unit that the memory is in. May also be written out (i.e. kilobytes). | |
| small | false | Unless this is set to true, megabyte and kilobyte values will automatically be rounded off. | |
| capital | false | Makes the unit all caps: MB. |
|
| camel | false | Makes the unit camel case: Mb. |
|
| space | false | Adds a space between the number and the unit: 24 kb. |
Let's try a simple example. We have a field cost in our database that needs to be put into the format $X.XX USD. The formatting option itself takes a column object, so we give it:
$tg = new TableGear(array(
"formatting" => array(
"cost" => "currency[prefix=$,suffix= USD,pad]"
)
)
));When data occurs in the cost column of our table, it will be formatted to make sense as currency:
1.99 -> $1.99 USD
4679.23 -> $4,679.23 USD
4 -> $4.00 USD
55234 -> $55,234.00 USD
Note that currency is rounded to two decimal places by default, and that pad is adding .00 to our data to pad it out. Also note the extra space in the suffix. The other formats work in the same way. For special cases, or other formats that aren't covered by formatting, use data transforms instead.
One of the cool features of the TableGear package is that after a data format is specified, it will be applied in all client-server interaction. This means that not only will data initially appear in the right format when the page is loaded, but data updated through AJAX requests will have the format applied as well. In addition, totals will also automatically have the formatting applied for that column, so you won't have to worry about them, either.
The formatting option is designed to handle situations where your raw data needs to be tweaked just a bit to be understandable to the user. But what about situations where the data in your database is just a reference – something that would make no sense to display to the user in the first place?
A good example of this is filenames. Let's say you have a database table called products, and a thumbnail for each product sitting on the server. Displaying the data thumbnail3242.jpg to the user isn't that useful – you need to make it point to something. This is where the transform option can help.
transform is different from formatting in that it doesn't take any parameters. Instead, it accepts an HTML object that contains any additional HTML (or text) that you want to use to transform your data. Inside this HTML object, you specify where your data should occur by using the token {DATA}:
$tg = new TableGear(array(
"transform" => array(
"thumbnail" => array(
"tag" => "img",
"attrib" => array(
"src" => "/path/to/thumbnails/{DATA}",
"alt" => "My thumbnail."
)
)
)
)
));Let's look at what's going on here. First, just like formatting, we passed transform a column object telling it that we want to transform the data in the column "thumbnail". The object we passed for this column is an HTML object that tells TableGear we want to output an img element in the place that the data would normally occur. The src attribute for this element points to a file on the server, in a directory where we know our thumbnails exist. All that's left is to tell TableGear where to inject the actual filename, using {DATA}. Now, the following HTML will be output where {DATA} would normally occur:
<img src="/path/to/thumbnails/somefile.jpg" alt="My thumbnail." />...and somefile.jpg is automatically replaced by the filename in the database...simple! Now we can inject {DATA} anywhere in our HTML (including attributes like src).
But what else can we do? In addition to {DATA} itself, there are some other tokens that may come in handy:
| Name | Description |
|---|---|
| {DATA} | The data in the database. |
| {KEY} | The primary key for the row where {DATA} occurs. |
| {FIELD} | The name of the field where {DATA} occurs in the database. |
| {COLUMN} | The number of the column where {DATA} occurs in the table. |
| {RANDOM} | A random number from 0 to 9999. |
| {ASSOCIATED} | A field associated with this data. This one is a bit more complicated... see below. |
Let's look at this example again, but using all the tokens together:
$tg = new TableGear(array(
"transform" => array(
"thumbnail" => array(
"tag" => "img",
"attrib" => array(
"src" => "/path/to/thumbnails/{DATA}?sid={RANDOM}",
"alt" => "This is {FIELD} number {KEY}.
In the table, it's in column #{COLUMN}."
)
)
)
)
));Now, we will get something like:
<img src="/path/to/thumbnails/thumbnail35.jpg?sid=3245" alt="This is thumbnail number 35.
In the table, it's in column #3." />We can now use any (or none) of these available values – just by using the token! As you can see, the token {RANDOM} can be useful to prevent caching.
There is one other token to talk about: {ASSOCIATED}. Although transform takes an HTML object, there is one special parameter that you can add to your HTML objects alongside tag, attrib, and html, which is associate. This will associate a field in the database so that you can access it inside that HTML object.
This is complicated to explain, but easy to understand through example. Let's say that as in the previous example, the field thumbnail holds the filename of a thumbnail on the server. Additionally, the field item holds the name of the item. You can access this information by associating the field:
$tg = new TableGear(array(
"transform" => array(
"thumbnail" => array(
"tag" => "img",
"attrib" => array(
"src" => "/path/to/thumbnails/{DATA}",
"alt" => "Image of {ASSOCIATED}"
),
"associate" => "item"
)
)
)
));This will output something like:
<img src="/path/to/thumbnails/thumbnail35.jpg" alt="Image of Oldskool Betamax VCR" />Make sure you remember that in the HTML object the parameter is associate, but the token is {ASSOCIATED} with a D. Also, note that this will associate that field for all attributes and html inside that HTML object. Since HTML objects are nestable, this means that you can assign one associated field, and reuse it with the {ASSOCIATED} token as much as you want in all your HTML down the line. If you wanted, you could then choose to override the top-level associated field at the last minute by redeclaring associated in a nested HTML object. Play around with it – you'll find it's quite flexible.
As we've seen, formatting is used when data in the database needs to be in a different format before it can be shown to the user. inputFormat is the opposite – when user inputted data needs to be in a different format before it can be put in the database.
The abilities of inputFormat are more limited than formatting. However, it is still quite useful, and almost does feel like mind reading. There are currently five parameters that can be passed to inputFormat:
| Parameter | Sub | Default | Description |
|---|---|---|---|
| date | string | Y-m-d H:i:s |
Educated guess of various date formats based on strtotime plus Asian date formats. The sub-parameter defines the format to be used in the database, and defaults to the standard MySQL format. |
| timestamp | none | - | Same as date, but the result will be stored in the database as a TIMESTAMP. Note that PHP timestamps on 32-bit platforms only support dates between 1901 and 2038. |
| eDate | string | Y-m-d H:i:s |
These are both the same as date and timestamp, but in the case of the ambiguous format XX/XX/YY(YY), defaults to English (European) date formats over US ones. This means that 1/4/2008 will be April 1, 2008, not January 4, 2008. |
| eTimestamp | none | - | |
| numeric | none | - | This will get a numeric value from user input. This means that the input $53,244.25 US dollars would result in the numeral 53244.25. |
Let's say we want to have a field in our database called purchase_date that accepts a wide range of user input. Once we have this input, we want to store it in the database in the format: Monday January 1, 2008. As with formatting and transform, we begin by passing a column object:|
$tg = new TableGear(array(
"inputFormat" => array(
"purchase_date" => "date[l F j, Y]"
)
)
)
));The parameter we passed for purchase_date tells TableGear that we want to accept user input in a date format, and store it in the database in the format l F j, Y, which is a format for PHP's date() function (for more information on this format, see the PHP documentation). If we had simply passed date as our parameter without the square brackets, the data would be stored in the standard MySQL format.
Now any input in the purchase_date field will be interpreted as a date! This means that the user can enter January 3, 2002, 1/3/2002, or 2002-01-03. They will all be interpreted the same, and end up in the same format in the database.
Really, though, this only scratches the surface. Other acceptable input includes: today, tomorrow, next Tuesday, 24 hours ago, +1 month, and 2002年1月3日 (standard Japanese/Chinese format using both half-width and full-width numbers). If you are expecting input in English/European formats (i.e. 3/1/2003 for January 3), you can pass eDate or eTimestamp instead.
In many cases, updating your data simply requires changing the database. However, there are a few other situations when other actions need to be taken in addition to updating the table itself.
One such situation is when the data is pointing to files on the server. If you were to, for example, delete a row in the database, you would also want to delete the file that the data was linked to. callback can help you do this. There are three types of callback:
| Type | Description |
|---|---|
| onUpdate | Performed every time the database is updated. |
| onDelete | Performed every time a row is delete from the database. |
| onInsert | Performed every time a row is inserted into the database. |
To use callback functions, you need to define them outside the TableGear constructor, then pass a reference to them as a string. Let's try an example that will help with the problem defined above – deleting files referenced by data in the database:
function foo($key, $previous, $updated, $ref){
unlink("/path/to/file/thumbnail" . $key . ".jpg");
}
$tg = new TableGear(array(
"callback" => array(
"onDelete" => "foo"
)
)
)
));This code will tell TableGear that every time a row is deleted from the database, call function foo, so that you can delete the file. The function will be passed 4 arguments. $key is the primary key for the affected row, $previous is an object holding the previous data (more below), $updated is the updated data (in the case of update and insert only), and $ref is a reference to the TableGear instance.
You will probably notice that in this code we are assuming our thumbnail files are neatly based on the primary key of the database. This would be ideal, but it may be possible that the files are randomly named on the server. In this case, we need to know what the previous filename was in order to delete it. We can use the $previous object to get this data.
However, there is one "gotcha" for the onDelete callback function. If we are simply deleting the data, we won't know what was in the row before we delete it. For this somewhat special case, pass the getPrevious parameter inside callback to grab the data in the row before it is affected and pass it to the callback function, like this:
function foo($key, $previous, $updated, $ref){
unlink("/path/to/file/" . $previous["filename"]);
}
$tg = new TableGear(array(
"callback" => array(
"onDelete" => "foo"
)
),
"getPrevious" => true
)
));Now the $previous object will be populated with the data from the row we have just deleted. If you don't turn on getPrevious, $previous will be null.
These callback functions will be performed even for AJAX requests. This means that we can apply TableGear.js to a table with filename references, change the filenames at will, and our callback function will make sure the actual files on the server are renamed to keep up (all without a page refresh!).
New to version 1.2, you can now paginate your table! Very good for huge databases with large amounts of data, especially as TableGear can slow down with excessively large data sets. In its simplest form, pagination requires an object with the parameter perPage, which is the number of rows on each page:
$tg = new TableGear(array(
"pagination" => array(
"perPage" => 10
)
)
);In addition to perPage, you can also pass in prev and next as the text for your prev and next links. Finally, the param linkCount is the amount of numbered page links on each side of the current page. The default of 5 will produce a maximum of five links on either side of the current page.
$tg = new TableGear(array(
"pagination" => array(
"perPage" => 10,
"prev" => "Prev",
"next" => "Next",
"linkCount" => 10
)
)
);