Put CRUD Button in last Column - ServerSide Processing

rlanhellas

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]

allan

Use mRender or sDefaultContent . This example shows the use with Editor, but the same principle applies regardless of your data controls: http://editor.datatables.net/release/DataTables/extras/Editor/examples/inlineControls.html

Allan

rlanhellas

Well, i tryed the following:

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.

allan

The error means what it says - you have a JSON error in the return. http://datatables.net/faqs#json .

rlanhellas

Well. i think that is the error (describe bellow) but i don't know how to fix it.

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.

allan

> $columns = $_GET['aoColumns'];

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

rlanhellas

I know that is a security risk, when everything is working i'll change for PDO in PHP but for now don't have problem because i'm testing in local host.

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

Not sure. Can you use the DataTables debugger please?

Allan

rlanhellas

Well, i used the debugger and was generated this link: http://debug.datatables.net/ekinad

allan

mData was a parameter introduced in v1.9.4, but you are only using 1.8.2. I'd suggest you upgrade.

Allan

rlanhellas

I upgraded to v.19.4 but the errors continue, see the debug: http://debug.datatables.net/epelep

aldhryne

Hi rlanhellas,

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...

chuy

I have a BETTER solution, I dont put markup on the column, just create an icon as place holder

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())
}
});
}