Home › Help

Ajax-tables

  1. Warning
  2. What is Ajaxtable
  3. Installation
  4. Why Ajaxtable?
  5. Simple Example
  6. Full example
  7. Input example / Overriding table declaration functions
  8. Using the calendar input
  9. Other Input Column Types
  10. Edit and Delete Columns
  11. Theming
  12. User Edit and Delete Columns
  13. Theming
  14. The cluetip.js file
  15. Ajaxtable Parameters
  16. Search Input Parameters
  17. Column Parameters
  18. Input Column Parameters

Warning (top)

Test

Warning: A couple of the examples below all you to edit a node via AJAX, including a node delete function. Please exercise caution when using these. There will be a dialog that pops up asking you if you really want to delete the node, but please be careful! I would suggest only enabling the docs module when you are testing. Make sure you back up the database if you're not sure.



What is Ajaxtable (top)

Ajaxtable is a Drupal module that provides an API to generate ajax-sortable and searchable tables based on simple parameters. The module also provides a tool for replacing an input box with a searchable table. It works like an autocomplete field, but with the table view you can have pagination, multiple search parameters, and sorting.

Ajaxtable was developed to provide an alternative to certain uses of Views and the Autocomplete field, thought it does not replace either one.



Installation (top)

After downloading ajaxtable, you will need to update jquery.js in the drupal /misc folder. As of right now, the jQuery-update module will get you to 1.1.4, but the patch for 1.2 hasn't been committed yet. This means that you need to copy the jquery.js in the ajaxtable folder and paste it into the Drupal misc/ folder, which will overwrite the old one. There may be some problems with this, but I haven't run across any yet. The jquery-update module adds some fixes that should smooth the transition.



Why Ajaxtable? (top)

Speed

Ajax-based tables load faster than standard page refreshes. When tracking down a particular record, it can be time consuming to go through several page requests. If you need to re-sort, select a new page, and then possibly do a search to narrow down the records further, you'll be spending at least 15-20 seconds on page reloading. With an ajax-based table, you're looking at a few seconds, max.

Look and feel

How the resulting table is rendered is based completely on themes. Themes are completely CSS-based, so virtually any style of rendering is possible. Themes can be added on the module level, if your module's tables needs their own look and feel, and they can be overridden in a table declaration function.



Simple Example (top)

To generate an ajaxtable, you need two parts: 1) The table declaration function, and 2) a call to that function. In this example, no parameters are specified, which means that all parameters are set to the default value.

To create a table declaration function, you need to create a function name that starts with "ajaxtable_table_" and ends with a unique ID. It is a good idea to prepend your ID with your module name to insure there are no conflicts with other modules. So, or example:

function ajaxtable_table_ajaxtable_example1($override='') { ajaxtable_override($table,$override); return $table; }
Click to highlight and press CTL+C to copy

And to render the table, you need to use the ajaxtable_render($id) function, where $id is the unique id you set for your table.

print ajaxtable_render("ajaxtable_example1");
Click to highlight and press CTL+C to copy

This would result in the following table:

In order to view the examples in the Ajaxtable Docs, you must have "administer nodes" access because the example involve manipulating node data.

Examples draw from the node database. If you have no nodes, this table will be empty.


Full example (top)

In this example, we're using most of the parameters so you can see the effects.

function ajaxtable_table_ajaxtable_example2($override='') { $search_inputs = array( array( 'type' => 'text', 'id' => 'is_like_title', 'operator' => 'like', 'help_title' => 'Like', 'help' => 'Search the node titles matching this phrase', 'col' => 'title', 'attributes' => array( 'size' => '5', ), ), array( 'type' => 'text', 'id' => 'is_not_like_title', 'operator' => 'not like', 'help_title' => 'Not like', 'help' => 'Search the node titles that do not match this phrase', 'col' => 'title', 'attributes' => array( 'size' => '5', ), ), ); // Get the inputs so they can be added to search $inputs = ajaxtable_search_inputs($search_inputs); // Alter the theme's search bar template to include the // additional search inputs. $theme['search'] = ' <table class="ajaxtable-default-search-table"> <tr> <td class="ajaxtable-default-search-table-left"></td> <td>{search_help}</td><td>Search for {search_input} </td> <td> | Like: ' . $inputs['is_like_title']['input'] . '</td> <td>' . $inputs['is_like_title']['help'] . '</td> <td> | Not Like: ' . $inputs['is_not_like_title']['input'] . '</td> <td>' . $inputs['is_not_like_title']['help'] . '</td> <td>{search_button}</td> <td class="ajaxtable-default-search-table-right"></td> </tr> </table> '; // Change the text of the buttons $theme['button_text'] = array( 'search' => 'Search', 'jump' => 'Jump to page', 'next' => '>>', 'prev' => '<<', ); $table = array( 'theme_override' => $theme, 'rows' => 10, 'theme_module' => 'ajaxtable', 'pages' => 15, 'theme' => 'default', 'search_inputs' => $search_inputs, 'autosearch' => TRUE, 'empty' => '<p>Nothing matched your query, sorry!</p>', 'query' => ' SELECT * FROM {node} ', 'columns' => array( array( 'col' => 'nid', 'label' => 'nid', 'unique' => TRUE, 'sortable' => TRUE, 'searchable' => TRUE, 'default_sort' => 'desc', 'help' => 'The node ID, for uniquely identifying nodes.', ), array( 'col' => 'title', 'label' => 'Title', 'sortable' => TRUE, 'searchable' => TRUE, 'help' => 'The title of the node', ), ), ); ajaxtable_override($table,$override); return $table; }
Click to highlight and press CTL+C to copy

In order to view the examples in the Ajaxtable Docs, you must have "administer nodes" access because the example involve manipulating node data.

Examples draw from the node database. If you have no nodes, this table will be empty.


Input example / Overriding table declaration functions (top)

You can an axaxtable to replace an input in a form. This requires jQuery 1.2 or higher, since it needs the "replaceWith" jquery function.

To replace an input, you need to do 3 things 1) create a table declaration function like in the above example, 2) add an "input_name" parameter to the table declaration function, 3) one of the columns need to have the parameter "input_value" set to TRUE, and 4) add a line in your module's hook_form_alter() function, and 3)

To demonstrate this example, we will be overriding the table in the previous example. This implementation is useful to reuse the same parameters of one ajaxtable and override select parameters. An override specifies the new parameters, then calls the function for another table.

function ajaxtable_table_ajaxtable_example3($override='') { $override = array( 'theme' => 'input', 'input_help' => 'This is the input help', 'search_help' => 'You must select an option from the search input. Simply typing in the sales order id is not sufficient.', 'input_name' => 'field_field_id_value[0][value]', 'rows' => '10', 'autosearch' => TRUE, 'hide' => TRUE, ); $table = ajaxtable_table_ajaxtable_example2($override); ajaxtable_override($table,$override); return $table; }
Click to highlight and press CTL+C to copy

Notice that there is a parameter called "input_name". This is the value of the "name" attribute for the input you want to replace. You can find this by looking at the code of the rendered form. Also, in the previous example we set the "input_value" parameter on the "nid" column, which doesn't do anything unless it is called to replace an input. Instead of re-setting all of the columns in this override, we're setting it in the previous table' function, which then passes it to the input version.

Next, we need to call the code to actually replace the input with the ajaxtable table. Note that this has only been set up for a text input. In this example, we set the theme to "input", which displays a text input that looks similar to other form inputs, which will auto-generate the table using the phrase in the box, similar to an autocomplete input.

To replace the input, we will add a line to our module's hook_form_alter() function, as in the following which would replace the "title" input with our search input as specified in in the ajaxtable_example3 example (note that you would replace the "ajaxtable_form_alter" with "mymodule_form_alter"):

function ajaxtable_form_alter($form_id, &$form) { if ($form_id == 'page_node_form') { ajaxtable_replace_input($form,'ajaxtable_example3'); } }
Click to highlight and press CTL+C to copy

The input should end up looking like this:

In order to view the examples in the Ajaxtable Docs, you must have "administer nodes" access because the example involve manipulating node data.

Examples draw from the node database. If you have no nodes, this table will be empty.

A couple things to note about how Ajaxtable handles the input:

  • Ajaxtable replaces the text input with a table that uses radio buttons instead for the value. The radio buttons will display in a column that will be added automatically to a table which has an "input_name" parameter set.
  • When used as an input, ajaxtable will hide the table when called on a form with a blank input. If the form has a posted value or default value, ajaxtable will display a table with the radio button in that row already selected.


Using the calendar input (top)

Using the calendar search input or column input requires the JS Calendar maodule, which is part of the jstools module. Here's an example:

In order to view the examples in the Ajaxtable Docs, you must have "administer nodes" access because the example involve manipulating node data.

Examples draw from the node database. If you have no nodes, this table will be empty.
function ajaxtable_table_ajaxtable_example4($override='') { $search_inputs = array( array( 'type' => 'date', 'id' => 'date_search', 'cal_params' => array( 'ifFormat' => '%Y-%m-%d', 'showsTime' => 'false', 'timeFormat' => '24', ), 'help_title' => 'Date Search', 'operator' => '>', 'help' => 'Anything after this date', 'col' => 'created', 'size' => 8, ), ); $inputs = ajaxtable_search_inputs($search_inputs); $theme['search'] = ' <table class="ajaxtable-default-search-table"> <tr> <td class="ajaxtable-default-search-table-left"></td> <td>{search_help}</td><td>Search for {search_input} </td> <td> | After this date: ' . $inputs['date_search']['input'] . '</td> <td>' . $inputs['date_search']['help'] . '</td> <td>{search_button}</td> <td class="ajaxtable-default-search-table-right"></td> </tr> </table> '; $override = array( 'search_inputs' => $search_inputs, 'theme_override' => $theme, 'columns' => array( array( 'col' => 'nid', 'label' => 'ID', 'sortable' => TRUE, 'searchable' => TRUE, 'help' => 'The unique node ID.', ), array( 'col' => 'title', 'label' => 'Title', 'sortable' => TRUE, 'searchable' => TRUE, 'help' => 'The title of the node.', ), array( 'col' => 'created', 'label' => 'Created', 'sortable' => TRUE, 'searchable' => TRUE, 'callback' => 'ajaxtable_col_date_example', 'help' => 'The date the node was created', ), ), ); ajaxtable_override($table,$override); return $table; } function ajaxtable_col_date_example($col,$value,$row,$table,$callback_array) { $input = array( 'type' => 'date', 'cal_params' => array( 'ifFormat' => '%Y-%m-%d', 'showsTime' => 'false', 'timeFormat' => '24', ), 'function_name' => 'ajaxtable_example_process_date', 'value' => date('Y-m-d',$row['created']), 'size' => 20, 'uid' => $row['nid'], ); return ajaxtable_ajax_input_col($row,$table,$input); } function ajaxtable_process_col_ajaxtable_example_process_date($value,$uid) { // You would typically check for permission here if (strtotime($value) && $value != '') { $sql = "UPDATE node SET created = '%s' WHERE nid = '%d'"; //db_query($sql,strtotime($value),$uid); return TRUE; } else { echo 'error|Please input a valid date'; } }
Click to highlight and press CTL+C to copy


Other Input Column Types (top)

Ajaxtable can handle other kinds of input columns, as seen in the following example:

In order to view the examples in the Ajaxtable Docs, you must have "administer nodes" access because the example involve manipulating node data.

Examples draw from the node database. If you have no nodes, this table will be empty.


Edit and Delete Columns (top)

You can easily add edit and delete columns but just setting a parameter each. In your query, just you just need to make sure that there is a value for "nid" being returned for each row. As a tip, you can use the user_access() function to set the visibility of the columns.

function ajaxtable_table_ajaxtable_example7($override='') { $table = array( 'edit' => TRUE, 'delete' => TRUE, ); ajaxtable_override($table,$override); return $table; }
Click to highlight and press CTL+C to copy

Adding those two parameters above will result in the following:

In order to view the examples in the Ajaxtable Docs, you must have "administer nodes" access because the example involve manipulating node data.

Examples draw from the node database. If you have no nodes, this table will be empty.


Theming (top)

There are two places where you can create themes. The first is in a ajaxtable_theme folder that you add to your module folder, in which case you will need to add a "theme_module" parameter to your ajaxtables. If you look in the ajaxtable module directory, you will see a folder called "ajaxtable_themes". In the folder are the two default themes, one called "default", which is a good general purpose theme, and one called "input", which is compact and good for using the table as an input.

In the theme directory, there is a "theme.php" file, which contains definitions for the theme. You can override these values in your table declaration function, as you can see in the "full" example above (ajaxtable_example2).

By adjusting the theme, you can make an ajaxtable look pretty much however you want. Lots of CSS used, and in places that require actualy HTML templating, the template.php file is used.



User Edit and Delete Columns (top)

If your query returns rows that include a uid column (for user ID), and that column is specified as the "unique" column, you can use the "user_edit" and "user_delete" parameters to add an edit button and delete box for the users. No table is included for this example for safety reasons, but you can get the idea.

function ajaxtable_table_ajaxtable_example8($override='') { $table = array( 'query' => 'SELECT * FROM users', 'user_edit' => TRUE, 'user_delete' => TRUE, 'return' => 'ajaxtable/docs', 'columns' => array( array( 'col' => 'uid', 'label' => 'UID', 'sortable' => TRUE, 'searchable' => TRUE, 'input_value' => TRUE, 'unique' => TRUE, 'default_sort' => 'desc', 'help' => 'The user ID', ), array( 'col' => 'name', 'label' => 'Username', 'sortable' => TRUE, 'searchable' => TRUE, 'help' => 'The username of the user', ), ), ); ajaxtable_override($table,$override); return $table; }
Click to highlight and press CTL+C to copy


Theming (top)

There are two places where you can create themes. The first is in a ajaxtable_theme folder that you add to your module folder, in which case you will need to add a "theme_module" parameter to your ajaxtables. If you look in the ajaxtable module directory, you will see a folder called "ajaxtable_themes". In the folder are the two default themes, one called "default", which is a good general purpose theme, and one called "input", which is compact and good for using the table as an input.

In the theme directory, there is a "theme.php" file, which contains definitions for the theme. You can override these values in your table declaration function, as you can see in the "full" example above (ajaxtable_example2).

By adjusting the theme, you can make an ajaxtable look pretty much however you want. Lots of CSS used, and in places that require actualy HTML templating, the template.php file is used.



The cluetip.js file (top)

The cluetip.js file consists of a function that defines how the hover-overs are displayed. If you don't have the cluetip module installed, normal titles will be used instead.



Ajaxtable Parameters (top)

Name Type Description Default
rows integer The number of rows to display at one time 50
theme_override array Array used to override the $theme variable defined in the theme's theme.php file. It is identical to the array in the theme's theme.php file.
theme string The theme to use for the table default
theme_module string The module where the theme can be found ajaxtable
pages integer The number of pages to be displayed at one time in the navigation 8
search_inputs key/value pair array Additional search inputs used to reduce the number of rows returned. For parameters, see Search Input Parameters
autosearch boolean When autosearch is set to TRUE, the table will be refreshed whenever the values of the search inputs change (i.e., as things are typed). This also replaces the search submit button with some information about how the autosearch works. FALSE
empty string The text to display when an empty result set is returned. <p>There were no results that match your query.</p>
query string The SQL query that generates the results for the table. Note that this should not have any LIMIT or ORDER parameters. SELECT * FROM {node}
columns array An array of the columns to be displayed in the table, which will be displayed in the order they are defined. See Column Parameters for the parameters you can pass.
array( 'col' => 'nid', 'label' => 'NID', 'sortable' => TRUE, 'searchable' => TRUE, 'default_sort' => 'desc', 'help' => 'NID stands for Node ID. Most types of data are based on nodes, and each has a unique ID. This ID is useful for tracking the history of the node and other reporting. The NID is incremented with each entry, so larger numbers were created more recently.', ), array( 'col' => 'title', 'label' => 'Title', 'sortable' => TRUE, 'searchable' => TRUE, 'help' => 'The title of the node', ), );
refresh_search boolean If set to TRUE, when a table is refreshed, so will the search bar. By default, the search bar is set outside of the ajax refreshing area, so that a user can type while the table is being refreshed. However, if you want to use a compact theme where the search and navigation bars are merged into a single bar, then you would want to set this to TRUE, so that the navigation still refreshes. SELECT * FROM {node}
edit boolean If set to TRUE, a column will be added to the end of the table with an "edit" button, which when clicked will take you to the edit node page. To work properly, this requires the query to return a column with "nid" and that column needs to be set to "unique". FALSE
delete boolean If set to TRUE, a column will be added to the end of the table with checkbox that, when clicked, will display a javascript alert asking if you really want to delete the node. If yes is selected, then it will run an AJAX-based delete on the node. To work properly, this requires the query to return a "nid" column and that column needs to be set to "unique". FALSE
user_edit boolean If set to TRUE, a column will be added to the end of the table with an "edit" button, which when clicked will take you to the edit user page. To work properly, this requires the query to return a "uid" column and that column needs to be set to "unique". FALSE
user_delete boolean If set to TRUE, a column will be added to the end of the table with checkbox that, when clicked, will display a javascript alert asking if you really want to delete the user. If yes is selected, then it will run an AJAX-based delete on the user. To work properly, this requires the query to return a "uid" column and that column needs to be set to "unique". FALSE
return string (url) The url the user will be returned to after clicking on an "Edit" link.
search_help string (url) Help text that is displayed when the search help icon is hovered over.
input_name string If the table is replacing an input as a autocomplete-style input, then this is the "name" attribute of the input.
hide boolean If set to TRUE, this will hide the table initially. This is useful for autocomplete inputs, or if you don't want to display the table until someone is using the search. FALSE
return_false boolean If set to TRUE, if the table has no rows it will return FALSE instead of an empty table. This is useful if you don't want to display the table at all when it is empty. FALSE
click_row boolean If set to TRUE, the entire row can be clicked to select the radio input column. TRUE
hover boolean If set to TRUE, the row will change it's class (i.e., color) when it is hovered over. TRUE
access boolean If set to FALSE, the table with return the value of the no_access parameter. If return_false is set to TRUE, it will return FALSE. This would normally be used with the user_access() function. TRUE
no_access string The HTML to display when a user does not have permission to view the table. Alternately, you can use return_false to return nothing. TRUE


Search Input Parameters (top)

Name Type Description Required Text Calendar
type string The type of input to display. Currently, "text" is the only option, but this may be improved upon in the future. x x x
col string The column that will be searched against when a search is submitted x x x
id string The unique id used for the search input. This is mostly used when rendering the inputs, but is also used internally to avoid conflict with other inputs. x x x
operator string The operator used to compare the text in the input to the column specified. Available options are "=","!=",">",">=","<","<=","like", and "not like". x x x
help_title string The title used in the cluetip hoverover. x x
help string The body used in the cluetip hoverover. x x
attributes array An array of name => value pairs of attributes that will be added to the input. For example "size" => 5 or "class" => "myclass". x x
cal_params key/value pair array A set of paired matches for the three js calendar options. Here is an example:
'cal_params' => array( 'ifFormat' => '%Y-%m-%d', 'showsTime' => 'false', 'timeFormat' => '24', ),
 x


Column Parameters (top)

Name Type Description Required
col string The table column to display. You are free to leave this blank if you are using a column just for a callback function.
alias string If the SQL query uses an alias for the column, you need to include the alias here. Otherwise, there may be conflicts.
label string The text to display in the column header (th)
help string The text / html to display in when a user hovers over a help icon in the column header. If blank, the icon will be hidden.
callback_array key/value pair array A set of values to send to the callback function.
callback string A callback function to use for the column.
sortable boolean If the table can be sorted on this column, set to TRUE. If not included, it won't be sortable.
searchable boolean If the column can be searched using the default search input, set to TRUE.
default_sort "asc" or "desc" If the column1
unique boolean A column that can be used as a unique key for various ajaxtable functions.
access boolean If set to FALSE, the column will not be visible or searchable to the user. Typically, you would use the user_access() function to set this.
hide boolean If set to TRUE, the column will be visibly hidden, but will still be processed. This will allow a column to be searchable but not visible.


Input Column Parameters (top)

The x's in the 'text', 'calendar', 'checkbox' and 'select' columns indicate that the parameters are available in that input column type.

Name Type Description Required Text Calendar Checkbox Select
type string The type of input to display. Can be "text", "calendar", "checkbox" or "select" x x x x x
function_name string The name of the function that processes the input when it is posted via AJAX. x x x x x
value string The current value of the input. Not required, but usually you want to display the correct value. x x x x
attributes key/value pair array A set of paired matches for tag attributes, such as "class" => "my_class_name". x x x x
size string The size of the input x x
options key/value pair array A set of paired matches for the select options, in the form of "My Label" => "my_key". x x
cal_params key/value pair array A set of paired matches for the three js calendar options. Here is an example:
'cal_params' => array( 'ifFormat' => '%Y-%m-%d', 'showsTime' => 'false', 'timeFormat' => '24', ),
 x