Documentation

The best way to get started is by downloading Mysql Ajax Table Editor and modifying one of the examples to fit your needs. Also many code examples can be found in the forums.

Installation

  1. Set the mysql variables in Common.php with the correct information to connect to your mysql database.
  2. Create the example tables in your database by running the sql in the mate.sql file.
  3. After you create the tables and have successfully connected to your database, the examples should be working. Point your browser to www.yoursite.com/path/to/mate/ and click on one of the example links. If you have any troubles installing please post a question on the forums.

Language Support

Currently Mysql Ajax Table Editor is available in English, Dutch, French, German, Russian and Spanish. If you need another language don't worry because translating mate is simple and can be done quickly. If you do decide create a new translation please send us the files and we will include the translation in the official release.

To use a different language you simply have to change the included javascript and php files. For example if you wanted to use Spanish instead of English you would change
require_once('../shared/php/mate/php/lang/LangVars-en.php');
to
require_once('../shared/php/mate/php/lang/LangVars-es.php');
and
<script type="text/javascript" src="js/lang/lang_vars-en.js"></script>
to
<script type="text/javascript" src="js/lang/lang_vars-es.js"></script>

Permissions

Permissions can be set on a per column basis and on a table basis. Example Source Code
The available permissions are:
E - Edit
A - Add
C - Copy
V - View - Display a column on the view screen.
D - Delete
X - Export
Q - Quick Search
S - Advanced search
H - Hide - Gives the user the option to hide a column.
O - Order - Gives the user the ability to order a column.
I - Icons - Displays icons on each row for edit, copy, view and delete options.
M - Multiple - Displays checkboxes on each row for multiple edit, copy, and delete actions. This is also used for user buttons with call back functions.
U - Allows the user to set the number of records displayed on each page.
T - Table - Display a column in the main table.
F - Filter - Use new filtering system (paid version only).
In order to use the H and O options you must have the mate_columns table created in your mysql database.

Configuration Options And Callback Functions

userIcons - Add extra icons with a call back function (for user icons to be displayed the I permission must be set).
Usage 1
$userIcons[] = array('icon_html' => 'html for icon');
$this->Editor->setConfig('userIcons',$userIcons);
The string #primaryColValue# will be replaced with the primary column value. This string can be changed by setting the replaceWithId option.
Usage 2
$userIcons[] = array('format_fun' => 'callback function to format icons');
$this->Editor->setConfig('userIcons',$userIcons);
The current row's information will be passed to the callback function. The call back function must return an associative array in the following format array('icon_html' => 'html for icons', 'num_icons' => 'number of icons').
Usage 3
$userIcons[] = array('class' => 'icon class', 'title' => 'icon title','call_back_fun' => 'callback function that is called when icon is clicked');
Optional Parameters:
no_update - Set this to true if you do not want the table to be redrawn after the call back executes.
confirm_msg - Set a confirmation message that will appear before the call back executes.
$this->Editor->setConfig('userIcons',$userIcons);
An associative array of the clicked row's information will be passed to the function.
addScreenFun - Call back to manipulate html on the add screen (no data is passed).
editScreenFun - Call back to manipulate html on the edit screen (no data is passed).
viewScreenFun - Call back to manipulate html on the view screen (no data is passed).
tableScreenFun - Call back to manipulate html on the table screen (no data is passed).
afterCopyFun - Call back function that is called after a row is copied.
The new row's insert id and an associative array of columns and values are passed to the call back function.
afterEditFun - Call back function that is called after a row is edited.
The primary column value, primary column name and an associative array of columns and values are passed to the call back function.
afterAddFun - Call back function that is called after a row is added.
The new row's insert id, primary column name and an associative array of columns and values are passed to the call back function.
afterDeleteFun - Call back function that is called after a row is deleted.
The deleted row's primary column value and primary column name are passed to the call back function.
disableMultCbFun - Call back function that can be called to disable the multiple row checkbox.
A row of data will be passed and the function should return true if the checkbox should be disabled.
tableInfo - A hash of html attributes that will be placed in the table tag.
oddRowColor - Set color of odd rows.
evenRowColor - Set color of even rows.
userButtons - Create a user button with a call back function, a hash of html attributes or your own custom button (If a call back function is used the M permission must be set).
Usage 1
$userButtons[] = array('button_html' => 'html for button');
$this->Editor->setConfig('userButtons',$userButtons);
Usage 2
$userButtons[] = array('label' => 'label for button', 'call_back_fun' => 'Call back that will be called on button press.');
Unless the pass_id_array option is set to true, the callback function will be executed for each row that was selected (an array of columns and values will be passed).
Optional Parameters:
pass_id_array - Pass an array of ids to the callback function instead of executing the callback for each row, default is false.
confirm_msg - Set a confirmation message that will appear before the call back executes.
update_html - Update the table screen html after callback function is executed, default is true.
$this->Editor->setConfig('userButtons',$userButtons);
Usage 3
$userButtons[] = array('label' => 'label for button', 'button_info' => 'hash of html attributes that will be placed in the button tag');
$this->Editor->setConfig('userButtons',$userButtons);
defNumAdvSearches - Change the default number of advanced searches (when toggling between quick and advanced searches).
numAdvSearches - Set the default number of advanced searches (when page loads for the first time). This option will only need to be used if you are setting the searchType option to advanced.
addRowTitle - Set add row title.
editRowTitle - Set edit row title.
viewRowTitle - Set view row title.
tableTitle - Set table title.
iconTitle - Set a title for the icon column.
orderByColumn - Set default order by column.
ascOrDesc - Set default ascending or descending.
displayNum - Set display per page (do not set this variable if U permission is being used).
displayNumInc - Set the display increment for the user defined display drop down.
maxDispNum - Set the maximum number of rows that can be displayed on one page.
dbName - Set the database name (this is used to join tables on different databases).
searchType - Set search type (quick or advanced).
replaceWithId - String that will be replaced by the row id in the user icon html (default is "#primaryColValue#").
useHightlight - Turn highlighting on quick searches off.
highlightHash - Set the highlight css for quick searches.
$this->Editor->setConfig('highlightHash','color: red; font-weight: bold;');
userActions - Set custom user actions that can be called from javascript events.
$this->Editor->setConfig('userActions',array('action_name' => 'call back function'));
Javascript to call user action: "toAjaxTableEditor('action_name',this.value);". This javascript could be placed in an onchange tag in a drop down or an onblur tag in a text input or any other place where a javascript event can be triggered. Dynamic Form Example
extraOrderByInfo - Set a second or third... order by column
$this->Editor->setConfig('extraOrderByInfo','col2Name asc, col3Name desc');
extraRowInfo - Add extra information to the table row tags.
$this->Editor->setConfig('extraRowInfo','onclick="showRowDetails(\'#primaryColValue#\',\'#rowNum#\');" style="cursor: pointer;"');
The strings #primaryColValue# and #rowNum# will be replaced with the corresponding values.
userColumns - Add your own user defined columns with and format them with a call back function.
$userColumns[] = array('call_back_fun' => 'function to format data in column');
Optional Parameters:
title - Set a title for the column.
$this->Editor->setConfig('userColumns',$userColumns);
An associative array of the current row's information will be passed to the call back function.
iconColPosition - This variable is used to set the position of the add, edit, copy and delete icons. The possible values are first and last (default is last). To set this variable use the following code.
$this->Editor->setConfig('iconColPosition','first');
removeIcons - This option is used to allow editing, copying or deleting but remove the icon. This can be useful when using a custom icon for these actions.
$this->Editor->setConfig('removeIcons','E');
sqlFilters - This variable can be set to add additional filters to the query.
$this->Editor->setConfig('sqlFilters',"department = 'Engineering'");
allowEditMult - Mysql ajax table editor has the ability to edit multiple rows. If you would like to disable that functionality and still have the ability to select multiple rows for delete and copy then you can set this variable to false.
$this->Editor->setConfig('allowEditMult',false);
defaultJsCalFormat - Set the default date format for the javascript calendar.
$this->Editor->setConfig('defaultJsCalFormat','%B %d, %Y'); See all formats
paginationLinks - Use pagination links instead of select drop down (paid version only).
$this->Editor->setConfig('paginationLinks',true);

Column Options

display_text - Text that gets displayed for the field.
req - Make a field required.
default - Set default value of field.
select_array - Associative array for a select drop down list (array keys are the select values and array values are what get displayed).
select_query - Query to create drop down list (must return 2 columns: the first column is the select values and the second column is what gets displayed).
hidden_add - Make a field hidden on add.
hidden_edit - Make a field hidden on edit.
calendar - Make a javascript date selector calendar.
As of September 17 2009 the calendar option can be passed as an array with format, reset and extra_info as parameters. The reset parameter will add a reset icon to remove the date. If you don't need these parameters you can just set calendar to true or to a date format.
'calendar' => array('format' => '%B %d, %Y', 'reset' => true, 'extra_info' => '')
'calendar' => true
'calendar' => '%B %d, %Y' See all formats
mysql_add_fun - Use a mysql function when inserting a row.
mysql_edit_fun - Use a mysql function when updating a row.
data_filters - Apply multiple data filters to a field.
'data_filters' => array('filters' => array("like '%c%'","like '%f%'"), 'criteria' => 'this is optional see below')
Optional Parameters:
criteria - Possible values are "any" or "all" the default is "all", which means all data filters will have to be matched for a row to be displayed. If criteria is set to "any" only one of the data filters will have to be matched for the row to be displayed.
join - Join a field to another table.
'join' => array('table' => 'table to join on', 'column' => 'column to join on')
Optional Parameters:
display_mask - Mask joined value with different columns or a mysql function or both. View Example Source Code
type - Set join type, default is left.
db - Specify a different database.
real_column - This option is used when you need to display multiple columns from a joined table. For more information view this topic in the forums. It can also be used to simulate a join on the primary key.
alias - When tables are joined in mate an alias is automatically generated. You can manually set this alias for a joined table by setting this option. This becomes useful when trying to access the joined table with other options such as sqlFilters or orderByColumn.
textarea - Use a textarea.
'textarea' => array('rows' => 5, 'cols' => 30)
checkbox - Use a checkbox input.
'checkbox' => array('checked_value' => 'Yes', 'un_checked_value' => 'No'), 'default' => 'Yes'
When using the checkbox option you should also use the default option to set the default value.
col_header_info - Set html attributes or css styles to go in the column header.
'col_header_info' => 'width="150" style="border: 1px solid #333;"'
display_mask - Mask a column with a mysql function.
input_info - html attribute hash that will be added to the input, select or textarea tags on add and edit.
file_upload - Upload a file to database or server (paid version only).
Usage 1: Upload to file system.
'file_upload' => array('upload_fun' => array(&$this,'handleUpload'))
If the upload_fun option is used the script will not insert anything into the database. It is up to the callback function to handle moving the file and updating the database. An example script is provided in the paid version to show how to do this.
Usage 2: Upload to database.
'file_upload' => array('type' => 'file_type', 'name' => 'file_name')
If the upload_fun is not used the script will automatically update the name, size and type columns if they are defined. The file data will be inserted into the column where the file upload was defined. An example script is provided in the paid version to show how to do this.
Optional Parameters:
upload_fun - Callback function that is called to handle the uploaded file. The following parameters will be passed to the callback function:
  • The id of the inserted or updated row
  • Column name where the upload was defined
  • An array of information about the uploaded
  • An array of validation errors (array will be empty if no validation errors have occurred)
The upload function should return the array of validation errors which will then be displayed to the user if it is not empty.
name - Specify the column where the name of the file should be stored.
type - Specify the column where the mime type of the file should be stored.
size - Specify the column where the size of the file should be stored.
max_size - Define the maximum file size (in bytes) that can be uploaded. A validation error will be displayed if the size is too big.
after_add_fun - Callback function to be called after a file has been added. The same parameters that are passed to the upload_fun callback will be passed.
after_edit_fun - Callback function to be called after a file has been updated. The same parameters that are passed to the upload_fun callback will be passed.
Sending Empty Files
The script detects if the file upload field has a value in it and if so sends a post message to the server. If no file has been selected to upload, the script will not post data back to the server and everything is done with ajax. To change this behavior and post an empty file back to the server, set the javascript variable mateSubmitEmptyUpload equal to true.

Column Callback Functions

format_input_fun - Format the input type on add and edit screens.
table_fun - Format, add or change data before it is displayed on the table screen.
csv_export_fun - Format, add or change data before it is exported.
view_fun - Format, add or change data before it is displayed on the view screen.
edit_fun - Format, add or change data before it is displayed on the edit screen.
add_fun - Format, add or change data before it is displayed on the add screen.
val_fun - Check field validation on add and edit screens.
on_edit_fun - Format/change user value before update.
on_add_fun - Format/change user value before insert.
on_copy_fun - Format/change value before copy.

Paid Version

Differences
One major difference in the paid version is that information about searches, column orders and other things the user may change are not stored in the php session variable. However this is not needed now since a user can navigate with the browser's forward and back buttons to return to previous searches or screens that he or she may have viewed. This is the default behavior of most websites and online applications.
Setting Configuration Options
In the paid version some configuration options should be set differently than in the free version. Any setting that could potentially be changed later on by the user, should be set in the default session data array in the display html function instead of in the initiate editor function. Examples on how to set these options can be seen in the scripts that come with the paid version.
Compatibility
Because of the complexity involved in making browser forward and back buttons work with ajax applications, configuration files for the paid version are not compatible with the free version. However coverting a configuration file from one version to the other is not difficult. To convert a configuration file from the free version to the paid version follow these steps:
  • Replace the code of the construct
  • Replace the code of the display html function
  • Remove configuration options mentioned above from the initiate editor function and add them to the display html function
  • Make sure to include the correct versions of AjaxTableEditor.php and ajax_table_editor.js
* Browser forward and back functionality has been tested to work in the latest versions of all major browsers.

Dependencies

Script.aculo.us (only needed for hiding and ordering columns)
Dynarch DHTML Calendar (optional calendar for selecting dates)