Put CRUD Button in last Column - ServerSide Processing
Put CRUD Button in last Column - ServerSide Processing
rlanhellas
Posts: 16Questions: 0Answers: 0
Hi Guys, i have a DataTable works fine with ServerSide Processing. But i wanna add in last Column some buttons like: Remove and Edit buttons. The button is like that: Remove
How can i Do it ? Bellow i putted all pages that a used for this.
Javascript Page
[code]
$('.listagem_dinamica').dataTable({
"bJQueryUI": true,
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "../srvprocess_datatable.php",
"aoColumns": [
{"sName": "cod_os"},
{"sName": "dthora_final"},
{"sName": "dthr_entrada"},
{"sName": "dthora_inicio"}
]
});
[/code]
srvprocess_datatable.php
[code]
<?php
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Easy set variables
*/
/* Array of database columns which should be read and sent back to DataTables. Use a space where
* you want to insert a non-database field (for example a counter or static image)
*/
$columns = $_GET['sColumns'];
$aColumns = explode(',', $columns);
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "cod_os";
/* DB table to use */
$sTable = "os";
/* Database connection information */
$gaSql['user'] = "root";
$gaSql['password'] = "123456";
$gaSql['db'] = "sicoti_db";
$gaSql['server'] = "localhost";
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP server-side, there is
* no need to edit below this line
*/
/*
* MySQL connection
*/
$gaSql['link'] = mysql_pconnect($gaSql['server'], $gaSql['user'], $gaSql['password']) or
die('Could not open connection to server');
mysql_select_db($gaSql['db'], $gaSql['link']) or
die('Could not select database ' . $gaSql['db']);
/*
* Paging
*/
$sLimit = "";
if (isset($_GET['iDisplayStart']) && $_GET['iDisplayLength'] != '-1') {
$sLimit = "LIMIT " . intval($_GET['iDisplayStart']) . ", " .
intval($_GET['iDisplayLength']);
}
/*
* Ordering
*/
$sOrder = "";
if (isset($_GET['iSortCol_0'])) {
$sOrder = "ORDER BY ";
for ($i = 0; $i < intval($_GET['iSortingCols']); $i++) {
if ($_GET['bSortable_' . intval($_GET['iSortCol_' . $i])] == "true") {
$sOrder .= $aColumns[intval($_GET['iSortCol_' . $i])] . " " .
($_GET['sSortDir_' . $i] === 'asc' ? 'asc' : 'desc') . ", ";
}
}
$sOrder = substr_replace($sOrder, "", -2);
if ($sOrder == "ORDER BY") {
$sOrder = "";
}
}
/*
* Filtering
* NOTE this does not match the built-in DataTables filtering which does it
* word by word on any field. It's possible to do here, but concerned about efficiency
* on very large tables, and MySQL's regex functionality is very limited
*/
$sWhere = "";
if (isset($_GET['sSearch']) && $_GET['sSearch'] != "") {
$sWhere = "WHERE (";
for ($i = 0; $i < count($aColumns); $i++) {
$sWhere .= $aColumns[$i] . " LIKE '%" . mysql_real_escape_string($_GET['sSearch']) . "%' OR ";
}
$sWhere = substr_replace($sWhere, "", -3);
$sWhere .= ')';
}
/* Individual column filtering */
for ($i = 0; $i < count($aColumns); $i++) {
if (isset($_GET['bSearchable_' . $i]) && $_GET['bSearchable_' . $i] == "true" && $_GET['sSearch_' . $i] != '') {
if ($sWhere == "") {
$sWhere = "WHERE ";
} else {
$sWhere .= " AND ";
}
$sWhere .= $aColumns[$i] . " LIKE '%" . mysql_real_escape_string($_GET['sSearch_' . $i]) . "%' ";
}
}
/*
* SQL queries
* Get data to display
*/
$sQuery = "
SELECT SQL_CALC_FOUND_ROWS " . str_replace(" , ", " ", implode(", ", $aColumns)) . "
FROM $sTable
$sWhere
$sOrder
$sLimit
";
$rResult = mysql_query($sQuery, $gaSql['link']) or die(mysql_error());
/* Data set length after filtering */
$sQuery = "
SELECT FOUND_ROWS()
";
$rResultFilterTotal = mysql_query($sQuery, $gaSql['link']) or die(mysql_error());
$aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
$iFilteredTotal = $aResultFilterTotal[0];
/* Total data set length */
$sQuery = "
SELECT COUNT(" . $sIndexColumn . ")
FROM $sTable
";
$rResultTotal = mysql_query($sQuery, $gaSql['link']) or die(mysql_error());
$aResultTotal = mysql_fetch_array($rResultTotal);
$iTotal = $aResultTotal[0];
/*
* Output
*/
$output = array(
"sEcho" => intval($_GET['sEcho']),
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => array()
);
while ($aRow = mysql_fetch_array($rResult)) {
$row = array();
for ($i = 0; $i < count($aColumns); $i++) {
if ($aColumns[$i] == "version") {
/* Special output formatting for 'version' column */
$row[] = ($aRow[$aColumns[$i]] == "0") ? '-' : $aRow[$aColumns[$i]];
} else if ($aColumns[$i] != ' ') {
/* General output */
$row[] = $aRow[$aColumns[$i]];
}
}
$output['aaData'][] = $row;
}
echo json_encode($output);
?>
[/code]
Page where I wanna show the datatable
[code]
OS
Fim
Entrada
Inicio
[/code]
How can i Do it ? Bellow i putted all pages that a used for this.
Javascript Page
[code]
$('.listagem_dinamica').dataTable({
"bJQueryUI": true,
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "../srvprocess_datatable.php",
"aoColumns": [
{"sName": "cod_os"},
{"sName": "dthora_final"},
{"sName": "dthr_entrada"},
{"sName": "dthora_inicio"}
]
});
[/code]
srvprocess_datatable.php
[code]
<?php
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Easy set variables
*/
/* Array of database columns which should be read and sent back to DataTables. Use a space where
* you want to insert a non-database field (for example a counter or static image)
*/
$columns = $_GET['sColumns'];
$aColumns = explode(',', $columns);
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "cod_os";
/* DB table to use */
$sTable = "os";
/* Database connection information */
$gaSql['user'] = "root";
$gaSql['password'] = "123456";
$gaSql['db'] = "sicoti_db";
$gaSql['server'] = "localhost";
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP server-side, there is
* no need to edit below this line
*/
/*
* MySQL connection
*/
$gaSql['link'] = mysql_pconnect($gaSql['server'], $gaSql['user'], $gaSql['password']) or
die('Could not open connection to server');
mysql_select_db($gaSql['db'], $gaSql['link']) or
die('Could not select database ' . $gaSql['db']);
/*
* Paging
*/
$sLimit = "";
if (isset($_GET['iDisplayStart']) && $_GET['iDisplayLength'] != '-1') {
$sLimit = "LIMIT " . intval($_GET['iDisplayStart']) . ", " .
intval($_GET['iDisplayLength']);
}
/*
* Ordering
*/
$sOrder = "";
if (isset($_GET['iSortCol_0'])) {
$sOrder = "ORDER BY ";
for ($i = 0; $i < intval($_GET['iSortingCols']); $i++) {
if ($_GET['bSortable_' . intval($_GET['iSortCol_' . $i])] == "true") {
$sOrder .= $aColumns[intval($_GET['iSortCol_' . $i])] . " " .
($_GET['sSortDir_' . $i] === 'asc' ? 'asc' : 'desc') . ", ";
}
}
$sOrder = substr_replace($sOrder, "", -2);
if ($sOrder == "ORDER BY") {
$sOrder = "";
}
}
/*
* Filtering
* NOTE this does not match the built-in DataTables filtering which does it
* word by word on any field. It's possible to do here, but concerned about efficiency
* on very large tables, and MySQL's regex functionality is very limited
*/
$sWhere = "";
if (isset($_GET['sSearch']) && $_GET['sSearch'] != "") {
$sWhere = "WHERE (";
for ($i = 0; $i < count($aColumns); $i++) {
$sWhere .= $aColumns[$i] . " LIKE '%" . mysql_real_escape_string($_GET['sSearch']) . "%' OR ";
}
$sWhere = substr_replace($sWhere, "", -3);
$sWhere .= ')';
}
/* Individual column filtering */
for ($i = 0; $i < count($aColumns); $i++) {
if (isset($_GET['bSearchable_' . $i]) && $_GET['bSearchable_' . $i] == "true" && $_GET['sSearch_' . $i] != '') {
if ($sWhere == "") {
$sWhere = "WHERE ";
} else {
$sWhere .= " AND ";
}
$sWhere .= $aColumns[$i] . " LIKE '%" . mysql_real_escape_string($_GET['sSearch_' . $i]) . "%' ";
}
}
/*
* SQL queries
* Get data to display
*/
$sQuery = "
SELECT SQL_CALC_FOUND_ROWS " . str_replace(" , ", " ", implode(", ", $aColumns)) . "
FROM $sTable
$sWhere
$sOrder
$sLimit
";
$rResult = mysql_query($sQuery, $gaSql['link']) or die(mysql_error());
/* Data set length after filtering */
$sQuery = "
SELECT FOUND_ROWS()
";
$rResultFilterTotal = mysql_query($sQuery, $gaSql['link']) or die(mysql_error());
$aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
$iFilteredTotal = $aResultFilterTotal[0];
/* Total data set length */
$sQuery = "
SELECT COUNT(" . $sIndexColumn . ")
FROM $sTable
";
$rResultTotal = mysql_query($sQuery, $gaSql['link']) or die(mysql_error());
$aResultTotal = mysql_fetch_array($rResultTotal);
$iTotal = $aResultTotal[0];
/*
* Output
*/
$output = array(
"sEcho" => intval($_GET['sEcho']),
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => array()
);
while ($aRow = mysql_fetch_array($rResult)) {
$row = array();
for ($i = 0; $i < count($aColumns); $i++) {
if ($aColumns[$i] == "version") {
/* Special output formatting for 'version' column */
$row[] = ($aRow[$aColumns[$i]] == "0") ? '-' : $aRow[$aColumns[$i]];
} else if ($aColumns[$i] != ' ') {
/* General output */
$row[] = $aRow[$aColumns[$i]];
}
}
$output['aaData'][] = $row;
}
echo json_encode($output);
?>
[/code]
Page where I wanna show the datatable
[code]
OS
Fim
Entrada
Inicio
[/code]
This discussion has been closed.
Replies
Allan
Datatable code
[code]
$('.listagem_dinamica').dataTable( {
"sDom": "Tfrtip",
"sAjaxSource": "../srvprocess_datatable.php",
"aoColumns": [
{ "mData": "cod_os" },
{ "mData": "dt_fim" },
{ "mData": "dt_entrada" },
{
"mData": null,
"sClass": "center",
"sDefaultContent": 'Edit / Delete'
}
]
} );
[/code]
srvprocess_datatable.php
[code]
<?php
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Easy set variables
*/
/* Array of database columns which should be read and sent back to DataTables. Use a space where
* you want to insert a non-database field (for example a counter or static image)
*/
$columns = $_GET['aoColumns'];
$aColumns = explode(',', $columns);
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "cod_os";
/* DB table to use */
$sTable = "os";
/* Database connection information */
$gaSql['user'] = "root";
$gaSql['password'] = "123456";
$gaSql['db'] = "sicoti_db";
$gaSql['server'] = "localhost";
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP server-side, there is
* no need to edit below this line
*/
/*
* MySQL connection
*/
$gaSql['link'] = mysql_pconnect($gaSql['server'], $gaSql['user'], $gaSql['password']) or
die('Could not open connection to server');
mysql_select_db($gaSql['db'], $gaSql['link']) or
die('Could not select database ' . $gaSql['db']);
/*
* Paging
*/
$sLimit = "";
if (isset($_GET['iDisplayStart']) && $_GET['iDisplayLength'] != '-1') {
$sLimit = "LIMIT " . intval($_GET['iDisplayStart']) . ", " .
intval($_GET['iDisplayLength']);
}
/*
* Ordering
*/
$sOrder = "";
if (isset($_GET['iSortCol_0'])) {
$sOrder = "ORDER BY ";
for ($i = 0; $i < intval($_GET['iSortingCols']); $i++) {
if ($_GET['bSortable_' . intval($_GET['iSortCol_' . $i])] == "true") {
$sOrder .= $aColumns[intval($_GET['iSortCol_' . $i])] . " " .
($_GET['sSortDir_' . $i] === 'asc' ? 'asc' : 'desc') . ", ";
}
}
$sOrder = substr_replace($sOrder, "", -2);
if ($sOrder == "ORDER BY") {
$sOrder = "";
}
}
/*
* Filtering
* NOTE this does not match the built-in DataTables filtering which does it
* word by word on any field. It's possible to do here, but concerned about efficiency
* on very large tables, and MySQL's regex functionality is very limited
*/
$sWhere = "";
if (isset($_GET['sSearch']) && $_GET['sSearch'] != "") {
$sWhere = "WHERE (";
for ($i = 0; $i < count($aColumns); $i++) {
$sWhere .= $aColumns[$i] . " LIKE '%" . mysql_real_escape_string($_GET['sSearch']) . "%' OR ";
}
$sWhere = substr_replace($sWhere, "", -3);
$sWhere .= ')';
}
/* Individual column filtering */
for ($i = 0; $i < count($aColumns); $i++) {
if (isset($_GET['bSearchable_' . $i]) && $_GET['bSearchable_' . $i] == "true" && $_GET['sSearch_' . $i] != '') {
if ($sWhere == "") {
$sWhere = "WHERE ";
} else {
$sWhere .= " AND ";
}
$sWhere .= $aColumns[$i] . " LIKE '%" . mysql_real_escape_string($_GET['sSearch_' . $i]) . "%' ";
}
}
/*
* SQL queries
* Get data to display
*/
$sQuery = "
SELECT SQL_CALC_FOUND_ROWS " . str_replace(" , ", " ", implode(", ", $aColumns)) . "
FROM $sTable
$sWhere
$sOrder
$sLimit
";
$rResult = mysql_query($sQuery, $gaSql['link']) or die(mysql_error());
/* Data set length after filtering */
$sQuery = "
SELECT FOUND_ROWS()
";
$rResultFilterTotal = mysql_query($sQuery, $gaSql['link']) or die(mysql_error());
$aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
$iFilteredTotal = $aResultFilterTotal[0];
/* Total data set length */
$sQuery = "
SELECT COUNT(" . $sIndexColumn . ")
FROM $sTable
";
$rResultTotal = mysql_query($sQuery, $gaSql['link']) or die(mysql_error());
$aResultTotal = mysql_fetch_array($rResultTotal);
$iTotal = $aResultTotal[0];
/*
* Output
*/
$output = array(
"sEcho" => intval($_GET['sEcho']),
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => array()
);
while ($aRow = mysql_fetch_array($rResult)) {
$row = array();
for ($i = 0; $i < count($aColumns); $i++) {
if ($aColumns[$i] == "version") {
/* Special output formatting for 'version' column */
$row[] = ($aRow[$aColumns[$i]] == "0") ? '-' : $aRow[$aColumns[$i]];
} else if ($aColumns[$i] != ' ') {
/* General output */
$row[] = $aRow[$aColumns[$i]];
}
}
$output['aaData'][] = $row;
}
echo json_encode($output);
?>
[/code]
But i got the error:
DataTables warning: JSON data from server could not be parsed. This is caused by a JSON formatting error.
Let's go...
i have 4 declared columns in Javascript file (cod_os, dt_inicio, dt_fim and a last column to put the buttons).
[code]
"aoColumns": [
{ "mData": "cod_os" },
{ "mData": "dt_fim" },
{ "mData": "dt_entrada" },
{
"mData": null,
"sClass": "center",
"sDefaultContent": 'Edit / Delete'
}
]
[/code]
When the srvprocess_datatable.php is called the first line do it:
[code]
$columns = $_GET['aoColumns'];
$aColumns = explode(',', $columns);
[/code]
Look, 4 itens are loaded in $aColumns array, but when the SQL SELECT is executed the error ocurrs because the last column (to put the button, don't exists).
Debugging the PHP page the select created in run-time is it:
[code]SELECT SQL_CALC_FOUND_ROWS cod_os, dt_fim, dt_entrada,
FROM os
[/code]
Look for the "," (comma) after "dt_entrada", this comma apper because the code will create another column but this is null.
Because this describe above the JSON Error happens.
That's a massive security risk. You can do it (and pop the last one off the array, since like you say, that will be causing an error), but you are leaving your database wide open to attacks.
Allan
So.. I found another problem for the error in JSON, when i debug the PHP code i saw that the value of $_GET['aoColumns'] aren't the Columns declared in Javascript method and a another "crazy" value.
See the PrintScreen of the value in DEBUG: http://s24.postimg.org/49b8wsit1/valor_get_aocolumns.png
How the datatable don't send the corret columns do PHP Page?
Allan
Allan
Just add a row array element in the output section of your server-side php script.
Sample code: server_processing.php
[code]
while ( $aRow = mysql_fetch_array( $rResult ) )
{
$row = array();
/* Add the details image at the start of the display array */
$row[] = '';
for ( $i=0 ; $i
Action
Loading data from server
[/code]
Hope this helps...
1--
2--After creating the table BIND a function for "click" on the table (all of it) to handle CRUD, so I don't have an event handler per row, and mark up on each.
3--this function tries to determine if the click is on this ICON and responds creating a small pane with buttons,
*the complex part is really handling clicks on each of this buttons with same handler
HOPE it helps-------------------------------------------------------------------
##true nested is a function to get a deep property that can exist and can be true
##My Action Icon can be iconic or textual, so i decide and use menuActionT and menuActionsV
the important parts are that the buttons presented can be decided at click time depending on record data, or table CRUD requirements
##the Key for the record can be all numeric or alphanumeric so I need to handle it, and is an attribute of the icon
##Do action does the actual execution of the action, needs action name, and key basically
## the states when clicking are icon is a triangle,
##................click creates pane menu, with action buttons
##................user can click last icon (close actions row menu)
##................user can click original triangle (also closes the row menu)
##................user clicks any other button and executes it
##
function set_tableClickHandler(tableID){
jQuery(tableID).bind('click', function(event) {
//subFUNCTION LIST
function renderRowButtonIF(conditionObjPath,theWidth,theTitle,theImageURL){
var s_s='';
if (isTrueNested(obj,conditionObjPath)) {
w += theWidth; //w is local to parent function
if (arguments.length==4) {
s_s += '';//onclick="doRowAction_main(this,this.title,'+key+');"
} else {
s_s += '';//onclick="doRowAction_main(this,this.value,'+key+');"
}
}
return s_s;
}//renderRowButton
//END subFUNCTION LIST
var b='',s='',f='',w=24,tID,key,act;
var obj=viewObj;
tID =jQuery(event.target).attr('ID');
key=jQuery(event.target).attr('rowid');
//if ( typeof key == "string" ) {key = '\''+key+'\''} //##OJO undefined
if (jQuery(event.target).hasClass("rowActions")) {
if (jQuery(event.target).hasClass("button-pressed")) {
//alert('the ID is is pressed, will unpress='+tID+', row='+tRow);
jQuery(event.target).toggleClass('button-pressed');
} else if (jQuery(event.target).hasClass("menuClose")) {
//alert('CLOSING the ID is is pressed, will unpress='+tID+', row='+tRow);
jQuery(event.target).parent().siblings('.rowActions').toggleClass('button-pressed');
jQuery(event.target).parent().remove();
} else if (jQuery(event.target).hasClass("menuActionT")) {
//alert('CLOSING the ID is is pressed, will unpress='+tID+', row='+tRow);
key=jQuery(event.target).parent().siblings('.rowActions').attr('rowid');
act=jQuery(event.target).attr('title');
doRowAction_main(event.target,act,key);
jQuery(event.target).parent().siblings('.rowActions').toggleClass('button-pressed');
jQuery(event.target).parent().remove();
} else if (jQuery(event.target).hasClass("menuActionV")) {
//alert('CLOSING the ID is is pressed, will unpress='+tID+', row='+tRow);
key=jQuery(event.target).parent().siblings('.rowActions').attr('rowid');
act=jQuery(event.target).val();
doRowAction_main(event.target,act,key);
jQuery(event.target).parent().siblings('.rowActions').toggleClass('button-pressed');
jQuery(event.target).parent().remove();
}else{//CREATE SHOW Menu
tID =jQuery(event.target).attr('ID');
//alert('the ID is is un-pressed, will press='+tID+', row='+tRow);
jQuery(event.target).toggleClass('button-pressed');
//Permits are List/Disable/Edit/Create from user there is a hierechy/not objectwise
b += renderRowButtonIF('model.valid_actions.canView' ,120,'View details' );
b += renderRowButtonIF('model.valid_actions.viewConcepts' ,140,'Budget items' );
b += renderRowButtonIF('model.valid_actions.viewColumns' ,170,'Column definitions');
b += renderRowButtonIF('model.valid_actions.viewRelatedProducts' ,160,'Agreement items' );
b += renderRowButtonIF('model.valid_actions.canEdit' ,110,'Edit record' );
b += renderRowButtonIF('model.valid_actions.canInactivate' ,130,'Change Status' );
b += renderRowButtonIF('model.valid_actions.canDelete' ,130,'Delete record' );
//FOR SE CASES ICONIC BUTTONS
b += renderRowButtonIF('model.valid_actions.canInfo' ,45,'open summary' ,'_img/app_cases/se_info_24.png');
b += renderRowButtonIF('model.valid_actions.canAttach' ,45,'open attachments' ,'_img/app_cases/se_clip_24.png');
b += renderRowButtonIF('model.valid_actions.canHistory',45,'open history' ,'_img/app_cases/se_history_22.png');
b += renderRowButtonIF('model.valid_actions.canComment',46,'open comments' ,'_img/app_cases/se_comments_24.png');
b += renderRowButtonIF('model.valid_actions.canFlag' ,45,'change flag' ,'_img/app_cases/_toggles/star_22_1.png');
b += renderRowButtonIF('model.valid_actions.canProcess',45,'open for processing','_img/app_cases/se_process_24.png');
//Width is calculated before putting the div
s += '';//onclick="$(\'more_actions_button_' + f + '\').toggleClassName(\'button-pressed\');$(\'more_actions_' + f + '\').toggle();"
s += b;
s += '';
s += '';
jQuery(event.target).before(s);
jQuery('#rowActionBttns_'+tID).toggle();
}
}else{
//alert(jQuery(event.target).html())
}
});
}