jEditable updating MySQL database - PHP code?

college_dev

There are quite a few discussions (like http://datatables.net/forums/comments.php?DiscussionID=32&page=1#Item_0) that deal with updating a MySQL database from within a DataTable using jEditable. However, each thread is lacking one thing, the actual PHP source.

There are a couple questions that need answering.

Firstly, how do you identify the cell that's being updated, so you can match it against your database?

Second, how do you do the actual updating (PHP source)?

In my particular case, I'm using PHP to echo a MySQL database into HTML, which DataTables is then acting upon.

I noted the offer of $100 in another thread for a workable solution, but one was never provided. Can we get this figured out? It'd be a wonderful addition to this community.

Thanks!

Miles

Here, take a look at lines 30+. It shows, where I've declared a column to edit, and it's ID is in hidding column 0 or 4 of the same row. There are two IDs are there are two fields. The SQL is a inner join.

My javascript:
[code]var oTable;

$(document).ready(function() {

/* Init DataTables */
oTable = $('#dt').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "./includes/grouptable.php",
"aaSorting": [[ 1, "asc" ]],
"bPaginate":true ,
"aoColumns": [
{ "bSearchable": false,
"bVisible": false },
null,
null,
null,
{ "bSearchable": false,
"bVisible": false }
],
"fnDrawCallback": function() {
$('#dt tbody td').editable( './includes/editultable.php', {
"callback": function( sValue, y ) {
var aPos = oTable.fnGetPosition( this );
oTable.fnUpdate( sValue, aPos[0], aPos[1] );
},
"onsubmit": function ( settings, self ) {
var aPos = oTable.fnGetPosition( self );
var aData = oTable.fnSettings().aoData[ aPos[0] ]._aData;
/* Link a column to it's correct ID for jeditable! */
if(aPos[1] == 1){
settings.submitdata = {id: aData[0],type:"ul"};
}else if(aPos[1] == 2){
settings.submitdata = {id: aData[0],type:"home"};
}else if(aPos[1] == 0){
settings.submitdata = {id: aData[4],type:"group"};
}
return true;

},
"height": "24px"
} );

}
} );


} );
[/code]

Miles

Here is my server side script.

In the order segment, I took out the two columns I'm hiding as bSearchable: false also fails to work for whatever reason, I still need to find out.

Sorting & filtering still works. Just the pagination buttons come up as deactivated for some reason. Even when I declare it as true.

[code]<?php
/* MySQL connection */
include_once("./dbc.php");
/* Paging */
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) )
{
$sLimit = "LIMIT ".$dbc->real_escape_string( $_GET['iDisplayStart'] ).", ".
$dbc->real_escape_string( $_GET['iDisplayLength'] );
}

/* Ordering */
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i<$dbc->real_escape_string( $_GET['iSortingCols'] ) ; $i++ )
{
$sOrder .= fnColumnToField($dbc->real_escape_string( $_GET['iSortCol_'.$i] ))."
".$dbc->real_escape_string( $_GET['iSortDir_'.$i] ) .", ";
}
$sOrder = substr_replace( $sOrder, "", -2 );
}

/* 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 ( $_GET['sSearch'] != "" )
{
$sWhere = "WHERE groups.name LIKE '%".$dbc->real_escape_string( $_GET['sSearch'] )."%' OR ".
"user_level.userlevel LIKE '%".$dbc->real_escape_string( $_GET['sSearch'] )."%' OR ".
"user_level.home LIKE '%".$dbc->real_escape_string( $_GET['sSearch'] )."%'";
}




$sQuery = "SELECT user_level.id, user_level.userlevel, user_level.home, user_level.groupid, groups.id AS \"g_id\", groups.name FROM user_level INNER JOIN groups ON user_level.groupid = groups.id $sWhere $sOrder $sLimit";
$rResult = $dbc->query($sQuery) or die($dbc->error());

$sQuery = "
SELECT FOUND_ROWS()
";
$rResultFilterTotal = $dbc->query( $sQuery) or die($dbc->error());
$aResultFilterTotal = $rResultFilterTotal->fetch_array();
$iFilteredTotal = $aResultFilterTotal[0];

$sQuery = "
SELECT COUNT(id)
FROM user_level
";
$rResultTotal = $dbc->query($sQuery) or die($dbc->error());
$aResultTotal = $rResultTotal->fetch_array();
$iTotal = $aResultTotal[0];

$sOutput = '{';
$sOutput .= '"sEcho": '.$_GET['sEcho'].', ';
$sOutput .= '"iTotalRecords": '.$iTotal.', ';
$sOutput .= '"iTotalDisplayRecords": '.$iFilteredTotal.', ';
$sOutput .= '"aaData": [ ';
while ( $aRow = $rResult->fetch_assoc() )
{
$sOutput .= "[";
$sOutput .= '"'.addslashes($aRow['id']).'",';
$sOutput .= '"'.addslashes($aRow['name']).'",';
$sOutput .= '"'.addslashes($aRow['userlevel']).'",';
$sOutput .= '"'.addslashes($aRow['home']).'",';
$sOutput .= '"'.addslashes($aRow['g_id']).'"';
$sOutput .= "],";
}
$sOutput = substr_replace( $sOutput, "", -1 );
$sOutput .= '] }';

echo $sOutput;
function fnColumnToField( $i )
{
if ( $i == 0 )
return "id";
else if ( $i == 1 )
return "name";
else if ( $i == 2 )
return "userlevel";
else if ( $i == 3 )
return "home";
else if ( $i == 4 )
return "g_id";
}

?>
[/code]

Miles

Obviously when it's passed to editutlevel.php it's receives post values of 'id','value' and 'type'. That I can do whatever I want with in my DB.

Downsides are that currently the entire table will become editable, I have yet to figure out a way to fix this. More importantly I want to know why pagination doesn't work :(

college_dev

Thanks for the code. Is there a way to do this if DataTables is being called on a purely HTML table?

My table is just HTML echoed out by PHP:

[code]
echo "\r";
echo "\t" . $first_name . "\r";
echo "\t" . $last_name . "\r";
...
echo ""
[/code]

[code]

John
Doe
jdoe@example.com
1234
Main Street

[/code]

Can jEditable still be implemented and how?

P.S. The reason I'm using PHP echo instead of DataTable's server-side processing is so the table data is still accessible to those with Javascript disabled.

Miles

Hmm, as jeditable is applied to each td in a datatable...I like making theories up, but they are usually wrong.

Echo out the ID of the record to the ID of the td element!

[code]echo "\t".$firstname."\r";[/code]

Could work! You'd just need to apply jeditable to #datatable tbody td

college_dev

Yes, but I have only one ID per row, not for each field in the row. So how would the specific field get identified?

And what PHP would I use?

Miles

jeditable takes the ID of the element it's targeted to wrap around, in this case it would be the TD.

From my javascript example you'd just remove the section of "submitdata" that specifies an ID as the ID would be set by the TD tag.

college_dev

You mean something like this:

[code]
echo "\r";
[/code]
?

I only have a unique identifier for each , not each .

Would it be possible to see your HTML source for your table, so we can see how it's set up?