This documentation has been updated for version 4.2. For older versions view the archived docs.
The best way to get started is by downloading MySQL Ajax Table Editor and modifying one of the examples to fit your needs.
Each MySQL Ajax Table Editor is powered by a configuration file which has the following sections:
Currently MySQL Ajax Table Editor is available in Chinese, English, Dutch, French, German, Italian, Portuguese, Russian, Spanish and Turkish.
Creating a new translation can be done quickly by editing the php language variables file (php/lang/LangVars-en.php) and the javascript language variables file (js/lang/ang_vars-en.js). If you do 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>
NOTE: In order for some international date formats to work correctly, the corresponding locale needs to be installed on the server. To get a list of available locales installed on your linux server run the "locale -a" command. To install the spanish locale for example run "locale-gen es_ES.utf8" . The list of locales that MySQL Ajax Table Editor looks for can be found in the corresponding LangVars file in the $locales array.
function initiateEditor()
{
...
$permissions = 'EAVDQCSXHOMF';
$this->Editor = new AjaxTableEditor(
$tableName,
$primaryCol,
$errorFun,
$permissions,
$tableColumns
);
}
function initiateEditor()
{
$tableColumns['first_name'] = array(
'display_text' => 'First Name',
'perms' => 'EVCTAXQSHOF'
);
...
}
If a column is to be displayed in the table screen the T permission must be set for that column and for the table.
This can be used to protect against xss attacks or to display input back to the user in the form they are expecting. To enable html escaping for a column set the escape_html column option.
function initiateEditor()
{
$tableColumns['notes'] = array(
'display_text' => 'Notes',
'perms' => 'EVCTAXQSHO',
'textarea' => array('rows' => 8, 'cols' => 25),
'escape_html' => true,
);
...
}
function initiateEditor()
{
$tableColumns['first_name'] = array('display_text' => 'First Name', 'perms' => 'EVCTAXQS');
...
}
When using the join option, the display_mask option must be defined inside the join array.
function initiateEditor()
{
$tableColumns['full_name'] = array(
'display_text' => 'Full Name',
'perms' => 'VTXQ',
'display_mask' => "concat(first_name,' ',last_name)"
);
...
}
function initiateEditor()
{
$tableColumns['active'] = array(
'display_text' => 'Active',
'perms' => 'EVCTAXQ',
'col_header_info' => 'width="150" style="border: 1px solid #333;"'
);
...
}
To avoid display issues, html tags will automatically be stripped when the data is truncated.
function initiateEditor()
{
$tableColumns['notes'] = array(
'display_text' => 'Notes',
'perms' => 'EVCTAXQSHO',
'textarea' => array('rows' => 8, 'cols' => 25),
'sub_str' => 30
);
...
}
function initiateEditor()
{
$tableColumns['url'] = array(
'display_text' => 'URL',
'perms' => 'EVCAXTQSFHO',
'table_fun' => array(&$this,'formatLink'),
'view_fun' => array(&$this,'formatLink')
);
...
}
function formatLink($col,$val,$row,$instanceName,$rowNum)
{
$html = '';
if(strlen($val) > 0)
{
$html = '<a target="_blank" href="'.$val.'">'.$val.'</a>';
}
return $html;
}
This can be used to protect against xss attacks or to display input back to the user in the form they are expecting. To enable html escaping for the whole table editor, set the escapeHtml configuration option.
function initiateEditor()
{
...
$this->Editor->setConfig('escapeHtml',true);
}
function initiateEditor()
{
...
$this->Editor->setConfig('tableInfo','cellpadding="1" width="800" class="mateTable"');
}
This option was available in previous free and paid versions (mate-free-2.2 and mate-paid-3.2).
In newer versions of MATE the css classes "even" and "odd" can be modified to change the appearance of the rows.
function initiateEditor()
{
...
$this->Editor->setConfig('oddRowColor','#E0E0E0');
}
This option was available in previous free and paid versions (mate-free-2.2 and mate-paid-3.2).
In newer versions of MATE the css classes "even" and "odd" can be modified to change the appearance of the rows.
function initiateEditor()
{
...
$this->Editor->setConfig('evenRowColor','#FFFFFF');
}
function initiateEditor()
{
...
$this->Editor->setConfig('tableTitle','Employees');
}
function initiateEditor()
{
...
$this->Editor->setConfig('displayNum','30');
}
function initiateEditor()
{
...
$this->Editor->setConfig('displayNumInc','10');
}
function initiateEditor()
{
...
$this->Editor->setConfig('maxDispNum','100');
}
The strings #primaryColValue# and #rowNum# will be replaced with the corresponding values.
function initiateEditor()
{
...
$this->Editor->setConfig('extraRowInfo','onclick="showRowDetails(\'#primaryColValue#\',\'#rowNum#\');" style="cursor: pointer;"');
}
function initiateEditor()
{
...
$this->Editor->setConfig('paginationLinks',true);
}
function initiateEditor()
{
...
$this->Editor->setConfig('filterPosition','top');
}
Parameters - No data is passed to this callback function.
Return Values - No data must be returned by this function.
function initiateEditor()
{
...
$this->Editor->setConfig('tableScreenFun',array(&$this,'removeTable'));
}
function removeTable($instanceName)
{
$this->Editor->setInnerHtml('tableLayer','');
}
Parameters - An array of row sets (attributes), an array of row information and a row # will be passed to the callback function.
Return Values - An array of row sets must be returned by this function.
function initiateEditor()
{
...
$this->Editor->setConfig('modifyRowSets',array(&$this,'changeBgColor'));
}
function changeBgColor($rowSets,$rowInfo,$rowNum)
{
$rowSets['bgcolor'] = '#ffffff';
if($rowInfo['account_type'] == 'Admin')
{
$rowSets['bgcolor'] = 'red';
}
return $rowSets;
}
function initiateEditor()
{
...
$this->Editor->setConfig('modifyRowSets',array(&$this,'addStyle'));
}
function addStyle($rowSets,$rowInfo,$rowNum)
{
if($rowInfo['account_type'] == 'Admin')
{
$rowSets['style'] .= ' font-weight: bold;';
}
return $rowSets;
}
function initiateEditor()
{
...
$this->Editor->setConfig('modifyRowSets',array(&$this,'overrideClass'));
}
function overrideClass($rowSets,$rowInfo,$rowNum)
{
if($rowInfo['account_type'] == 'Admin')
{
$rowSets['class'] = 'admin-row';
}
return $rowSets;
}
function initiateEditor()
{
...
$userColumns[] = array('call_back_fun' => array(&$this,'getFullName'), 'title' => 'Full Name');
$this->Editor->setConfig('userColumns',$userColumns);
}
function getFullName($row)
{
$html = '<td>'.$row['first_name'].' '.$row['last_name'].'</td>';
return $html;
}
If a column is to be displayed in the view screen the V permission must be set for that column and for the table.
When using the join option, the display_mask option must be defined inside the join array.
function initiateEditor()
{
$tableColumns['full_name'] = array(
'display_text' => 'Full Name',
'perms' => 'VTXQ',
'display_mask' => "concat(first_name,' ',last_name)"
);
...
}
function initiateEditor()
{
...
$this->Editor->setConfig('viewRowTitle','View Employee');
}
function initiateEditor()
{
$tableColumns['url'] = array(
'display_text' => 'URL',
'perms' => 'EVCAXTQSFHO',
'table_fun' => array(&$this,'formatLink'),
'view_fun' => array(&$this,'formatLink')
);
...
}
function formatLink($col,$val,$row,$instanceName)
{
$html = '';
if(strlen($val) > 0)
{
$html = '<a target="_blank" href="'.$val.'">'.$val.'</a>';
}
return $html;
}
To allow adding rows to a table the "A" permission must be set for the table. To allow input for a column on the add screen, the "A" permission must be set for that column.
To make a column editable, the "E" permission must be set for the column and for the table.
function initiateEditor()
{
$tableColumns['status'] = array(
'display_text' => 'Status',
'perms' => 'EVCTAXQS',
'default' => 'open'
);
...
}
function initiateEditor()
{
$tableColumns['first_name'] = array(
'display_text' => 'First Name',
'perms' => 'EVCTAXQS',
'req' => true
);
...
}
The array keys will be the drop down values and the array values will be displayed in the drop down list.
function initiateEditor()
{
$statusArr = array(
'open' => 'Open',
'closed' => 'Closed',
'pending' => 'Pending'
);
$tableColumns['status'] = array(
'display_text' => 'Status',
'perms' => 'EVCTAXQS',
'select_array' => $statusArr,
'default' => 'open'
);
...
}
The query must select 2 columns. The first column will be the drop down values and the second column will be displayed in the drop down list.
function initiateEditor()
{
$tableColumns['status'] = array(
'display_text' => 'Status',
'perms' => 'EVCTAXQS',
'select_query' => "select status, status from status_table",
'default' => 'open'
);
...
}
function initiateEditor()
{
$tableColumns['status'] = array(
'display_text' => 'Status',
'perms' => 'EVCTAXQS',
'hidden_add' => true,
'default' => 'open'
);
...
}
function initiateEditor()
{
$tableColumns['status'] = array(
'display_text' => 'Status',
'perms' => 'EVCTAXQS',
'hidden_edit' => true
);
...
}
The #VALUE# string will be replaced with the user input value.
function initiateEditor()
{
$tableColumns['password'] = array(
'display_text' => 'Password',
'perms' => 'EVCAXQT',
'mysql_add_fun' => "PASSWORD('#VALUE#')",
'mysql_edit_fun' => "PASSWORD('#VALUE#')",
);
...
}
function initiateEditor()
{
$tableColumns['created'] = array(
'display_text' => 'Created',
'perms' => 'VCAXQT',
'mysql_add_fun' => "NOW()",
);
...
}
The #VALUE# string will be replaced with the user input value.
function initiateEditor()
{
$tableColumns['password'] = array(
'display_text' => 'Password',
'perms' => 'EVCAXQT',
'mysql_add_fun' => "PASSWORD('#VALUE#')",
'mysql_edit_fun' => "PASSWORD('#VALUE#')",
);
...
}
function initiateEditor()
{
$tableColumns['updated'] = array(
'display_text' => 'Updated',
'perms' => 'EVCXQT',
'mysql_edit_fun' => "NOW()",
);
...
}
function initiateEditor()
{
$tableColumns['notes'] = array(
'display_text' => 'Notes',
'perms' => 'EVCTAXQ',
'textarea' => array('rows' => 5, 'cols' => 30)
);
...
}
When using the checkbox option you should also use the default option to set the default value.
function initiateEditor()
{
$tableColumns['active'] = array(
'display_text' => 'Active',
'perms' => 'EVCTAXQ',
'checkbox' => array(
'checked_value' => '1',
'un_checked_value' => '0'
),
'default' => '1'
);
...
}
function initiateEditor()
{
$tableColumns['user_name'] = array(
'display_text' => 'User Name',
'perms' => 'EVCTAXQS',
'input_info' => 'onblur="checkUserName(this.value);"'
);
...
}
delete_fun - Callback function that will be called to delete an uploaded file. When this option is set a delete icon will automatically be displayed next to the uploaded file name. See the pro demo for an example. This option is currently only available in the pro version.
upload_fun - Callback function that is called to handle the uploaded file. The following parameters will be passed to the callback function:
The upload function should return the array of validation errors which will then be displayed to the user if it is not empty. For more information see the "Upload To Directory" example below.
The following parameters are used when uploading the file to the database. For more information see the "Upload To Database" example below.
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.
function initiateEditor()
{
$tableColumns['file_name'] = array(
'display_text' => 'Image',
'perms' => 'EVCAXTQSFHO',
'file_upload' => array(
'upload_fun' => array(&$this,'handleUpload')
),
'table_fun' => array(&$this,'formatImage'),
'view_fun' => array(&$this,'formatImage')
);
...
}
function formatImage($col,$val,$row,$instanceName,$rowNum)
{
$html = '';
if(strlen($val) > 0)
{
$html .= '<a target="_blank" href="uploads/'.$val.'"><img style="border: none;" src="uploads/'.$val.'" alt="'.$val.'" width="100" /></a>';
}
return $html;
}
function handleUpload($id,$col,$filesArr,$valErrors)
{
if(count($valErrors) == 0)
{
// Delete image file if it already existed
$query = "select file_name from emp_upload_dir where id = :id";
$queryParams = array('id' => $id);
$stmt = $this->Editor->doQuery($query,$queryParams);
if($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
if(file_exists('uploads/'.$row['file_name']))
{
unlink('uploads/'.$row['file_name']);
}
}
// Copy file to data directory and update database with the file name.
if(move_uploaded_file($filesArr['tmp_name'],'uploads/'.$filesArr['name']))
{
$queryParams = array('fileName' => $filesArr['name'], 'id' => $id);
$query = "update emp_upload_dir set file_name = :fileName where id = :id";
$stmt = $this->Editor->doQuery($query,$queryParams);
if(!$stmt)
{
$valErrors[] = 'There was an error updating the database.';
unlink('uploads/'.$filesArr['name']);
}
}
else
{
$valErrros[] = 'The file could not be moved';
}
}
return $valErrors;
}
function initiateEditor()
{
$tableColumns['file_data'] = array(
'display_text' => 'Image',
'perms' => 'EVCAXTQSFHO',
'file_upload' => array(
'type' => 'file_type',
'size' => 'file_size',
'name' => 'file_name'
),
'display_mask' => 'file_name',
'table_fun' => array(&$this,'formatImage'),
'view_fun' => array(&$this,'formatImage')
);
...
}
function formatImage($col,$val,$row,$instanceName,$rowNum)
{
$html = '';
if(strlen($val) > 0)
{
$html .= '<a target="_blank" href="DisplayFileFromDb.php?emp_id='.$row['id'].'"><img style="border: none;" src="DisplayFileFromDb.php?emp_id='.$row['id'].'" alt="'.$val.'" width="100" /></a>';
}
return $html;
}
function initiateEditor()
{
$tableColumns['null_column'] = array(
'display_text' => 'Null Column',
'perms' => 'EVCTAXQSFHO',
'null_array' => array('','0'),
);
...
}
Column callback functions are callback functions that can be defined for each column.
function initiateEditor()
{
$tableColumns['active']= array(
'display_text' => 'Active',
'perms' => 'TAEVQS',
'add_fun' => array(&$this,'replaceBool'),
'display_mask' => 'replace(replace(active,"1","Yes"),"0","No")'
);
...
}
function replaceBool($col,$val,$row,$instanceName)
{
return str_replace(array('0','1'),array('No','Yes'),$val);
}
function initiateEditor()
{
$tableColumns['active']= array(
'display_text' => 'Active',
'perms' => 'TAEVQS',
'edit_fun' => array(&$this,'replaceBool'),
'display_mask' => 'replace(replace(active,"1","Yes"),"0","No")'
);
...
}
function replaceBool($col,$val,$row,$instanceName)
{
return str_replace(array('0','1'),array('No','Yes'),$val);
}
If the callback function returns false, the column will not be inserted. This can be used for columns that should only be inserted when they have a value such as password fields.
function initiateEditor()
{
$tableColumns['active']= array(
'display_text' => 'Active',
'perms' => 'TAEVQS',
'on_add_fun' => array(&$this,'replaceBool'),
'display_mask' => 'replace(replace(active,"1","Yes"),"0","No")'
);
...
}
function replaceBool($col,$val,$row,$instanceName)
{
return str_replace(array('No','Yes'),array('0','1'),$val);
}
If the callback function returns false, the column will not be updated. This can be used for password fields that should only be updated when they have a value.
function initiateEditor()
{
$tableColumns['active']= array(
'display_text' => 'Active',
'perms' => 'TAEVQS',
'on_edit_fun' => array(&$this,'replaceBool'),
'display_mask' => 'replace(replace(active,"1","Yes"),"0","No")'
);
...
}
function replaceBool($col,$val,$row,$instanceName)
{
return str_replace(array('No','Yes'),array('0','1'),$val);
}
function initiateEditor()
{
$tableColumns['email'] = array(
'display_text' => 'Email',
'perms' => 'EVCTAXQS',
'val_fun' => array(&$this,'valEmail')
);
...
}
function valEmail($col,$val,$row,$instanceName)
{
if(preg_match('/(@.*@)|(\.\.)|(@\.)|(\.@)|(^\.)/', $val) || preg_match('/^.+\@(\[?)[a-zA-Z0-9\-\.]+\.([a-zA-Z]{2,3}|[0-9]{1,3})(\]?)$/',$val))
{
return true;
}
else
{
// Create custom validation message and return false
$this->Editor->showDefaultValidationMsg = false;
$this->Editor->addTooltipMsg('Please enter a valid email address');
return false;
}
}
function initiateEditor()
{
$tableColumns['active']= array(
'display_text' => 'Active',
'perms' => 'TAEVQS',
'format_input_fun' => array(&$this,'getRadioBtns'),
'display_mask' => 'replace(replace(active,"1","Yes"),"0","No")'
);
...
}
function getRadioBtns($colName,$value,$row)
{
$opYes = 'checked="checked"';
$opNo = '';
if($value==0)
{
$opYes = '';
$opNo = 'checked="checked"';
}
$html = '<label><input name="'.$colName.'" type="radio" id="'.$colName.'" value="1" '.$opYes.'" />Yes</label><label><input name="'.$colName.'" type="radio" id="'.$colName.'" value="0" onchange="updateRadioValueNo(this);"'.$opNo.' />No</label>';
return $html;
}
function initiateEditor()
{
...
$this->Editor->setConfig('addRowTitle','Add Employee');
}
function initiateEditor()
{
...
$this->Editor->setConfig('editRowTitle','Edit Employee');
}
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.
function initiateEditor()
{
...
$this->Editor->setConfig('allowEditMult',false);
}
function initiateEditor()
{
...
$this->Editor->setConfig('addInPlace',true);
}
function initiateEditor()
{
...
$this->Editor->setConfig('editInPlace',true);
}
Set this option to false to make the in-line add form only visible after clicking an add button (default is true).
function initiateEditor()
{
...
$this->Editor->setConfig('persistentAddForm',false);
}
Parameters - No data is passed to this callback function.
Return Values - No data must be returned by this function.
function initiateEditor()
{
...
$this->Editor->setConfig('addScreenFun',array(&$this,'addCkEditor'));
}
function addCkEditor()
{
$this->Editor->addJavascript('addCkEditor("'.$this->instanceName.'notes");');
}
<script type="text/javascript">
function addCkEditor(id)
{
if(CKEDITOR.instances[id])
{
CKEDITOR.remove(CKEDITOR.instances[id]);
}
CKEDITOR.replace(id);
}
</script>
Parameters - The new row's insert id, primary column name and an associative array of columns and values are passed to this callback function.
Return Values - No data must be returned by this function.
function initiateEditor()
{
...
$this->Editor->setConfig('afterAddFun',array(&$this,'changeTotal'));
$this->Editor->setConfig('afterEditFun',array(&$this,'changeTotal'));
}
function changeTotal($id,$col,$info)
{
// Code to update the total
}
Parameters - No data is passed to this callback function.
Return Values - No data must be returned by this function.
function initiateEditor()
{
...
$this->Editor->setConfig('editScreenFun',array(&$this,'addCkEditor'));
}
function addCkEditor($instanceName)
{
$this->Editor->addJavascript('addCkEditor("'.$this->instanceName.'notes");');
}
<script type="text/javascript">
function addCkEditor(id)
{
if(CKEDITOR.instances[id])
{
CKEDITOR.remove(CKEDITOR.instances[id]);
}
CKEDITOR.replace(id);
}
</script>
Parameters - The primary column value, primary column name and an associative array of columns and values are passed to this callback function.
Return Values - No data must be returned by this function.
function initiateEditor()
{
...
$this->Editor->setConfig('afterEditFun',array(&$this,'changeTotal'));
$this->Editor->setConfig('afterAddFun',array(&$this,'changeTotal'));
}
function changeTotal($id,$col,$info)
{
// Code to update the total
}
Parameters - No data is passed to this callback function.
Return Values - No data must be returned by this function.
function initiateEditor()
{
...
$this->Editor->setConfig('viewScreenFun',array(&$this,'removeEditButton'));
}
function removeEditButton()
{
$this->Editor->retArr[] = array(
'layer_id' => 'viewRowButtons',
'where' => 'innerHTML',
'value' => '<button class="ajaxButton" onclick="toAjaxTableEditor(\'update_html\',\'\');">Back</button>'
);
}
Parameters - An associative array of columns and values for each row are passed to this callback function.
Return Values - True if the checkbox should be disabled and false if it should not.
function initiateEditor()
{
...
$this->Editor->setConfig('disableMultCbFun',array(&$this,'checkRowStatus'));
}
function checkRowStatus($info)
{
if($info['status'] == 'closed')
{
return true;
}
else
{
return false;
}
}
Parameters - Row number (to maintain compatibility with the editInPlaceFun callback) and the instance name will be passed to this function.
Return Values - Nothing must be returned by this function.
function initiateEditor()
{
...
$this->Editor->setConfig('addInPlaceFun',array(&$this,'initializeAutoComplete'));
}
function initializeAutoComplete($instanceName)
{
// Call javascript function to initialize auto complete
$this->Editor->addJavascript('initializeAutoComplete();');
}
<script type="text/javascript">
function initializeAutoComplete()
{
// Javascript code to initialize auto complete.
}
</script>
Parameters - Row number and the instance name will be passed to this function.
Return Values - Nothing must be returned by this function.
function initiateEditor()
{
...
$this->Editor->setConfig('editInPlaceFun',array(&$this,'initializeAutoComplete'));
}
function initializeAutoComplete($instanceName,$rowNum)
{
// Call javascript function to initialize auto complete
$this->Editor->addJavascript('initializeAutoComplete();',1);
}
<script type="text/javascript">
function initializeAutoComplete()
{
// Javascript code to initialize auto complete.
}
</script>
function initiateEditor()
{
...
$this->Editor->setConfig('searchType','advanced');
}
function initiateEditor()
{
...
$this->Editor->setConfig('defNumAdvSearches','2');
$this->Editor->setConfig('numAdvSearches','2');
}
function initiateEditor()
{
...
$this->Editor->setConfig('defNumAdvSearches','2');
$this->Editor->setConfig('numAdvSearches','2');
}
function initiateEditor()
{
...
$this->Editor->setConfig('useHightlight',false);
}
This option was available in previous free and paid versions (mate-free-2.2 and mate-paid-3.2).
In newer versions of MATE the css "highlight" class can be modified.
function initiateEditor()
{
...
$this->Editor->setConfig('highlightHash','color: red; font-weight: bold;');
}
function initiateEditor()
{
...
$this->Editor->setConfig('sqlFilters',"department = 'Engineering'");
}
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.
function initiateEditor()
{
$tableColumns['first_name'] = array(
'display_text' => 'First Name',
'perms' => 'EVCAXQT',
'data_filters' => array('filters' => array("like '%c%'","like '%f%'"), 'criteria' => 'any')
);
...
}
To display table icons, the I permission must be set for the table.
For user icons to be displayed the I permission must be set.
Examples:
function initiateEditor()
{
...
$userIcons[] = array(
'icon_html' => '<a onclick="iconAction('#primaryColValue#');" class="icon-class" title="icon-title"></a>'
);
$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.
function initiateEditor()
{
...
$userIcons[] = array('format_fun' => array(&$this,'getUserIcons'));
$this->Editor->setConfig('userIcons',$userIcons);
}
function getUserIcons($info)
{
$iconHtml = '';
$numIcons = 0;
$iconHtml .= '<li class="delete"><a href="javascript: customDeleteRow(\''.$info['id'].'\');" title="Delete"></a></li>';
$numIcons++;
return array('icon_html' => $iconHtml, 'num_icons' => $numIcons);
}
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').
function initiateEditor()
{
...
$userIcons[] = array(
'class' => 'delete',
'title' => 'Delete',
'call_back_fun' => array(&$this,'customDeleteRow'),
'no_update' => false,
'confirm_msg' => 'Are you sure?'
);
$this->Editor->setConfig('userIcons',$userIcons);
}
function customDeleteRow($info)
{
$query = "delete from table_name where id = :id";
$queryParams = array('id' => $info['id'];
$stmt = $this->Editor->doQuery($query,$queryParams);
if($stmt)
{
// code to delete associated records
}
}
An associative array of the clicked row's information will be passed to the function.
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.
function initiateEditor()
{
...
$userButtons[] = array(
'button_html' => '<button onclick="customJsFunction();">Custom Button</button>'
);
$this->Editor->setConfig('userButtons',$userButtons);
}
function initiateEditor()
{
...
$userButtons[] = array(
'label' => 'Archive',
'call_back_fun' => array(&$this,'archiveRows'),
'pass_id_array' => false,
'confirm_msg' => 'Are you sure you would like to archive the selected rows?',
'no_update' = false
);
$this->Editor->setConfig('userButtons',$userButtons);
}
function archiveRows($info)
{
// Code to archive
}
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.
no_update - Set this to true if you do not want the table html to automatically update after the callback function is executed, default is false.
function initiateEditor()
{
...
$userButtons[] = array(
'label' => 'Button Label',
'button_info' => 'onclick="runJsFun();" style="color: red;"'
);
$this->Editor->setConfig('userButtons',$userButtons);
}
function initiateEditor()
{
...
$this->Editor->setConfig('iconTitle','Actions');
}
function initiateEditor()
{
...
$this->Editor->setConfig('replaceWithId','%primaryColValue%');
}
Possible values are first and last.
function initiateEditor()
{
...
$this->Editor->setConfig('iconColPosition','first');
}
This option can be used in conjunction with the userIcons option to insert custom icons or for conditional editing.
function initiateEditor()
{
...
// remove edit icon
$this->Editor->setConfig('removeIcons','E');
}
function displayHtml()
{
...
echo $html;
// Set default session configuration variables here
$defaultSessionData['orderByColumn'] = 'first_name';
...
}
function displayHtml()
{
...
echo $html;
// Set default session configuration variables here
$defaultSessionData['ascOrDesc'] = 'asc';
...
}
function initiateEditor()
{
...
$this->Editor->setConfig('extraOrderByInfo','column_2 asc, column_3 desc');
}
If a column is to be exported the X permission must be set for that column and for the table.
Column callback function to modify exported data.
function initiateEditor()
{
$tableColumns['active']= array(
'display_text' => 'Active',
'perms' => 'TAEVQS',
'csv_export_fun' => array(&$this,'replaceBool'),
'format_input_fun' => array(&$this,'getRadioBtns'),
'display_mask' => 'replace(replace(active,"1","Yes"),"0","No")'
);
...
}
function replaceBool($col,$val,$row,$instanceName)
{
return str_replace(array('0','1'),array('No','Yes'),$val);
}
If column data is to be copied to a new row the C permission must be set for that column and for the table.
Parameters - The new row's insert id and an associative array of columns and values are passed to this callback function.
Return Values - No data must be returned by this function.
function initiateEditor()
{
...
$this->Editor->setConfig('afterCopyFun',array(&$this,'copyAssociatedRecords'));
}
function copyAssociatedRecords($id,$info)
{
// Code to copy associated records
}
function initiateEditor()
{
$tableColumns['created']= array(
'display_text' => 'Created',
'perms' => 'TACVQS',
'on_copy_fun' => array(&$this,'updateToNow'),
);
...
}
function updateToNow($col,$val,$row,$instanceName,$nextRowNum)
{
return date('Y-m-d H:i:s');
}
If table data is to be deleted, the D permission must be set for the table.
Parameters - The primary column name, the deleted row's primary column value and an associative array of deleted data are passed to this callback function.
Return Values - No data must be returned by this function.
function initiateEditor()
{
...
$this->Editor->setConfig('afterDeleteFun',array(&$this,'deleteAssociatedRecords'));
}
function deleteAssociatedRecords($id,$col)
{
// Code to delete associated records
}
To give users the ability to hide a column the H permission must be set for that column and for the table.
Hidden columns with the required validation option set will not be validated.
function initiateEditor()
{
$tableColumns['notes']= array(
'display_text' => 'Notes',
'perms' => 'EVCTAXQSHOF',
'textarea' => array('rows' => 8, 'cols' => 25),
'hidden' => true,
);
...
}
Parameters - The mate instance name is passed to this function.
Return Values - No data must be returned by this function.
This option is similar to the editScreenFun option which has example usage.
To give users the ability to change the order of a column the O permission must be set for that column and for the table.
Parameters - The mate instance name is passed to this function.
Return Values - No data must be returned by this function.
This option is similar to the editScreenFun option which has example usage.
js_format - Set the date format. See all format options
options - Array to set other jquery ui datepicker options. See all date picker options
function initiateEditor()
{
$tableColumns['hire_date'] = array(
'display_text' => 'Hire Date',
'perms' => 'EATVXSQ',
'display_mask' => 'date_format(`hire_date`,"%d %M %Y")',
'order_mask' => 'date_format(`hire_date`,"%Y-%m-%d %T")',
'range_mask' => 'date_format(`hire_date`,"%Y-%m-%d %T")',
'calendar' => array(
'js_format' => 'dd MM yy',
'options' => array('showButtonPanel' => true)
)
)
...
}
function initiateEditor()
{
$tableColumns['hire_date'] = array(
'display_text' => 'Hire Date',
'perms' => 'EATVXSQ',
'display_mask' => 'date_format(`hire_date`,"%d %M %Y")',
'order_mask' => 'date_format(`hire_date`,"%Y-%m-%d %T")',
'range_mask' => 'date_format(`hire_date`,"%Y-%m-%d %T")',
'calendar' => array(
'js_format' => 'dd MM yy',
'options' => array('showButtonPanel' => true)
)
)
...
}
function initiateEditor()
{
$tableColumns['hire_date'] = array(
'display_text' => 'Hire Date',
'perms' => 'EATVXSQ',
'display_mask' => 'date_format(`hire_date`,"%d %M %Y")',
'order_mask' => 'date_format(`hire_date`,"%Y-%m-%d %T")',
'range_mask' => 'date_format(`hire_date`,"%Y-%m-%d %T")',
'calendar' => array(
'js_format' => 'dd MM yy',
'options' => array('showButtonPanel' => true)
)
)
...
}
This option was available in previous free and paid versions (mate-free-2.2 and mate-paid-3.2).
For newer versions use the calendar js_format option.
function initiateEditor()
{
...
$this->Editor->setConfig('defaultJsCalFormat','%B %d, %Y');
}
This option only needs to be changed when saving dates to the database in a non-standard format.
function initiateEditor()
{
...
$this->Editor->setConfig('defaultDbDateFormat','d F Y');
}
This option facilitates date range searches. When it is set to true, user inputted dates will automatically be converted to the the defaultDbDateFormat when performing date range searches. This allows users to enter any date format when searching on dates and the query will still be executed as expected.
User input will be converted ONLY if the calendar option is set.
The join database can also be specified in the column join option.
function initiateEditor()
{
...
$this->Editor->setConfig('dbName','db_name');
}
display_mask - The display_mask option is used to display different columns from the joined table or to apply mysql functions on the column.
type - The type option sets the join type. The default join type is inner.
db - The db option is used to join tables in a different database.
alias - Manually set the alias for the joined table. This is useful when pulling in other columns from the joined table or accessing the joined table with other options such as sqlFilters or orderByColumn.
real_column - With this option it is possible to use the same column to create multiple joins. The alias option can be used to pull in multiple columns from a joined table but if separate join instances are wanted on the same table this option can be used.
no_auto_select - When joining on other tables, a drop down list will automatically be created for adding/editing rows. To override this behavior set the no_auto_select to true.
function initiateEditor()
{
$tableColumns['employee_id'] = array(
'display_text' => 'Name',
'perms' => 'EVCTAXQ',
'join' => array(
'table' => 'employees',
'column' => 'id',
'display_mask' => "concat(employees.first_name,' ',employees.last_name)",
'type' => 'left'
)
);
...
}
function initiateEditor()
{
$tableColumns['employee_id'] = array(
'display_text' => 'Name',
'perms' => 'EVCTAXQ',
'join' => array(
'table' => 'employees',
'column' => 'id',
'display_mask' => "concat(employees.first_name,' ',employees.last_name)",
'alias' => 'emp',
'type' => 'left'
)
);
// Bring in second column from joined table (notice the alias option above).
$tableColumns['department'] = array(
'display_text' => 'Department',
'perms' => 'VTXQ',
'display_mask' => "emp.department"
);
...
}
Custom joins are for reporting only. Editing and adding data is not supported when using custom joins.
function initiateEditor()
{
$tableColumns['employee_id'] = array(
'display_text' => 'Name', 'perms' => 'EVCTAXQS',
'display_mask' => "concat(emp.first_name,' ',emp.last_name)",
);
...
$this->Editor->setConfig('customJoin',"left join `employees` as `emp` on `login_info`.`employee_id` = `emp`.`id`");
...
}
function initiateEditor()
{
$tableColumns['user_name'] = array(
'display_text' => 'User Name',
'perms' => 'EVCTAXQS',
'input_info' => 'onblur="'.$this->mateInstances[0].'.toAjaxTableEditor(\'check_user_name\',this.value);"'
);
...
$userActions['check_user_name'] = array(&$this,'checkUserName');
$this->Editor->setConfig('userActions',$userActions);
}
function checkUserName($userName,$instanceName)
{
$query = "select id from users where user_name = :userName";
$queryParams = array('userName' => $userName);
$stmt = $this->Editor->doQuery($query,$queryParams);
if($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
$this->Editor->addTooltipMsg('This user name is already used');
}
}
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.
To execute javascript or manipulate html from a server side callback function, the global return array can be appended to.
function callBackFunction($col,$val,$row,$instanceName,$rowNum)
{
$this->Editor->addJavascript('alert("Test Javascript");');
}
function callBackFunction($col,$val,$row,$instanceName,$rowNum)
{
$this->Editor->setInnerHtml('id_of_html_elem','inner_html_of_elem');
}
function callBackFunction($col,$val,$row,$instanceName,$rowNum)
{
$this->Editor->setHtmlValue('id_of_input_elem','new_value_of_input_elem');
}
Sometimes when working with views it is necessary to override the defaut insert, update and delete functions in order to correctly update all of the tables in the view.
function initiateEditor()
{
...
$this->Editor->setConfig('userInsertFun',array(&$this,'insertRow'));
}
public function insertRow($col,$val,$insertInfo)
{
// Insert row into first table
$insertInfo['table1Id'] = $this->insertIntoTable1($insertInfo);
// Use generated auto increment key to insert into second table
$insertInfo['table2Id'] = $this->insertIntoTable2($insertInfo);
// Return primary key of view
return $insertInfo['table1Id'].'-'.$insertInfo['table2Id'];
}
function initiateEditor()
{
...
$this->Editor->setConfig('userUpdateFun',array(&$this,'updateRow'));
}
public function updateRow($col,$val,$updateInfo)
{
// Update first table
$this->updateTable1($updateInfo);
// Update second table
$this->updateTable2($updateInfo);
// Return primary key of view
return $updateInfo['primaryKeyColumn'];
}
function initiateEditor()
{
...
$this->Editor->setConfig('userDeleteFun',array(&$this,'deleteRow'));
}
public function deleteRow($col,$val,$deleteInfo)
{
// Delete row in first table
$this->deleteTableRow1($deleteInfo);
// Delete row in second table
$this->deleteTableRow2($deleteInfo);
// Return true if no errors
return true;
}
function callbackFunction()
{
// Prevent default error message from being shown
$this->Editor->showDefaultValidationMsg = false;
// Create new error message
$this->Editor->addTooltipMsg('Please enter a valid email address');
}
function callbackFunction()
{
// Create new message
$this->Editor->addTooltipMsg('The rows have been updated','msg');
}
Grouping data is for reporting only. Editing and adding data is not supported when grouping data.
function initiateEditor()
{
...
$this->Editor->setConfig('groupByClause',"group by department");
}
function initiateEditor()
{
...
$this->Editor->setConfig('havingFilters',"count(*) > 3");
}
function initiateEditor()
{
$tableColumns['count'] = array(
'display_text' => 'Count',
'perms' => 'VTXQSHOF',
'display_mask' => "count(*)",
'having' => true
);
...
}
Custom joins are for reporting only. Editing and adding data is not supported when using custom joins.
function initiateEditor()
{
$tableColumns['employee_id'] = array(
'display_text' => 'Name', 'perms' => 'EVCTAXQS',
'display_mask' => "concat(emp.first_name,' ',emp.last_name)",
);
...
$this->Editor->setConfig('customJoin',"left join `employees` as `emp` on `login_info`.`employee_id` = `emp`.`id`");
...
}
function initiateEditor()
{
...
$this->Editor->setConfig('viewQuery',true);
}