Updating MySQL tables with DataTables/ jEditable

bkemper

I am very interested in using DataTables, but am struggling to understand how to insert updates into a MySQL database.
I have created the example "Ajax" table : http://www.datatables.net/development/server-side/sql
I have set up the editable DataTables example : http://localhost:8888/datatables/examples/server_side/editable.html
I have replaced the server_processing.php example file with the script found here : http://www.datatables.net/development/server-side/php_mysql

But the missing link is how to actually insert the records into the MySQL database. Is there an example (using the "ajax" example) or instructions somewhere that I have missed?

allan

Hi bkemper,

The scripts that are in the downloaded DataTables package don't actually include an example of how to do the server-side integration with jEditable as the actual integration will be different for every use case! What I would suggest the first port of call is, is to enable jEditable as you would normally (just like in my example) and have a look at Firebug to see what parameters are being sent to the server. Then you can craft your script based on that.

I'm afraid I'm stuck on my mobile until tomorrow evening with limited reception, so I can't put together a working example at the moment, but if it's okay to wait until tomorrow evening I'll out together an example showing how it can be done :-).

Regards,
Allan

bkemper

Hello Allan, Tomorrow is fine! A simple example based on the "Ajax" table would be really helpful for me (and others I suspect) Thank you very much!

fbas

@bkemper:

the editable plugin page documentation is really pretty good. http://code.google.com/p/jquery-datatables-editable/wiki/Overview describes which fields are sent to the server side script for an edit operation:

[quote]One sever-side page that handles update AJAX requests need to be created. This page should accepts the following parameters:

value - that contains text used entered in the table cell while editing
id - id of the record that is deleted (id is placed in TR tag that surrounds the cell that has been edited)
columnId - position of the column of the cell that has been edited (hidden columns are counted also)
columnPosition - position of the column of the cell that has been edited (hidden columns are not counted)
rowId - id of the row containing cell the cell that has been edited
[/quote]

so you will need to write a server side script that accepts these params in order to edit a field

here's some code I've used in a project (there is more code above this snippet that connects to the db, but I am assuming you don't need that at this point, just an example of accepting the params from the editable call and committing to database). You would want to edit the $table and $id_column values to suit your db schema.

[code]
/*
* Main
*
*/
$return_val = "";

// allow either GET or POST vals
$req = array_merge($_GET, $_POST);

$table = "qabook";
$id_column = "id"; // default id column, will be changed to qaid if $table remains qabook
$id = "";
$col = "";
$val = "";

if (isset($req['id'])) $id = mysql_real_escape_string($req['id']);
if (isset($req['columnName'])) $col = mysql_real_escape_string($req['columnName']);
if (isset($req['value'])) $val = mysql_real_escape_string($req['value']);
if (isset($req['table'])) $table = mysql_real_escape_string($req['table']);
if ($table == "qabook") $id_column = "qaid";
if ($table == "qafiles") $id_column = "fileid";

if (!in_array($table, $valid_tables)) die("Invalid access to table $table.");

if (!$id || !$col ) { echo "Must provide parameters id, columnName"; return; }

if (strtolower($col) == "datereceived" || strtolower($col) == "datecompleted") {
if ($val) { // allow empty string to remove dates.. but if $val is not empty string, validate it as a date
$iTime = strtotime($val);
if ($iTime !== false) $val = date('y', $iTime) . '-' . date('m', $iTime) . '-' . date('d', $iTime);
else {
echo "Invalid date format";
return;
}
}
}

// convert empty values to null, wrap dates in quotes
if (strtolower($col) == "datereceived" || strtolower($col) == "datecompleted") {
if (!$val) $val = "null";
else $val = "'$val'";
}
elseif (!is_numeric($val)) $val = "'$val'"; // put in quotes if not numeric

$query = "UPDATE $table SET $col=$val WHERE $id_column = '$id'";


$result = mysql_query( $query, $link );
if (!$result) { echo "Database query error: " . mysql_error(); return; }

// query the value to return to client
$query = "SELECT $col FROM $table WHERE $id_column = '$id'";
$result = mysql_query( $query, $link );
if (!$result) { echo "Database query error: " . mysql_error(); return; }
if (mysql_num_rows($result) < 1) { echo "Database could not find row to update: " . $id; return; }
$row = mysql_fetch_array( $result );
$return_val = $row[0];

echo $return_val;
return;
[/code]

bkemper

@fbas,

Thank you for the code examples. But I would really like to have a full working example of the entire process to clear it up in my head. It seems that there are differences between this process (based on converting an HTML table) and the server-side processing example. It's still not clear to me how the user display is updated and the update is registered to the database in either scenario. I'm also confused regarding the datatables editable plugin as the download at http://code.google.com/p/jquery-datatables-editable/downloads/list seems to use a rather old version of datatables. My hope is to first get a simple working example of the process and then explore the plugin as well as the modifications I will require afterwards. Having a fully functional example will save me a great deal of time.

allan

Hi bkemper,

Here is an example of what might be needed on the client-side. Obviously it will probably need a few little tweaks for the exact situation in which you are using jEditable, but this will hopefully get you started:

[code]
$(document).ready(function() {
var oTable = $('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "scripts/server_processing.php",
"fnDrawCallback": function () {
$('#example tbody td').editable( '../examples_support/editable_ajax.php', {
"callback": function( sValue, y ) {
/* Redraw the table from the new data on the server */
oTable.fnDraw();
},
"height": "14px",
"submitdata": function(value, settings) {
var rowData = oTable.fnGetData( $(this).parents('tr')[0] );
return {
"rowId": rowData[0],
"columnId": rowData[1]
};
}
} );
}
} );
} );
[/code]

So the main part to look at here is the "submitdata" function. From the jEditable documentation ( http://www.appelsiini.net/projects/jeditable ) it allows:

> Extra parameters when submitting content. Can be either a hash or function returning a hash.

So we can make use of it in this case to identify the row and cell that we want the editing to occur on. Now in my example above I've pulled the row ID (assuming you are doing an UPDATE on an SQL DB you'll most likely want that!) from the first column in the table and the column name from the second. This is the part that you might well want to modify. If you are using mDataProp in DataTables ( http://datatables.net/blog/Extended_data_source_options_with_DataTables ) then this is stright forward - you just use the Javascript object notation as normal. If you aren't using mDataProp, then you'll need to have hidden columns for the ID and field name, or obtain them through some other method (again this comes down to your individual application / implementation - shout if you want me to help with this bit!).

Then, once this is done, the web-browser will send the following information to the server whenever an edit is made:

[code]
rowId
columnId
value
[/code]

Thus from that you can create an SQL UPDATE statement such as:

[code]
$query = "
UPDATE myTable
SET
".mysql_real_escape_string($_GET['columnId'])." =
'".mysql_real_escape_string($_GET['value'])."'
WHERE
id = ".mysql_real_escape_string($_GET['rowId']);

$rResult = mysql_query( $query, $db_link ) or die(mysql_error());
[/code]

That will update the DB and the Javascript callback will then reload the table with your new data - editing done :-)

Once you are comfortable with this, then it might indeed be worth looking at the editable plug-in as fbas suggests - it does offer a lot of options and flexibility, but its always a good idea to understand the basis of it, such as that above :-)

Regards,
Allan

bkemper

Hello allan,

I now realize that a mistake I was making was confusing the function of server_processing.php with that of editable_ajax.php. So while there is no issue with server_processing.php, I still cannot understand how to properly build editable_ajax.php.

In the example you provided above:

[code]
$(document).ready(function() {
var oTable = $('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "scripts/server_processing.php",
"fnDrawCallback": function () {
$('#example tbody td').editable( '../examples_support/editable_ajax.php', {
"callback": function( sValue, y ) {
/* Redraw the table from the new data on the server */
oTable.fnDraw();
},
"height": "14px",
"submitdata": function(value, settings) {
var rowData = oTable.fnGetData( $(this).parents('tr')[0] );
return {
"rowId": rowData[0],
"columnId": rowData[1]
};
}
} );
}
} );
} );
[/code]

you said

[quote]
Now in my example above I've pulled the row ID (assuming you are doing an UPDATE on an SQL DB you'll most likely want that!) from the first column in the table and the column name from the second.
[/quote]

If I use this code with the editable.html example, with the columns:
Rendering engine | Browser | Platform(s) | Engine version | CSS grade

and change "Engine version" to "TestValue", I get these values for the $_POST in editable_ajax.php:

Array
(
[value] => TestValue
[id] =>
[rowID] => Gecko
[columnId] => Firefox 1.5
)

So it is telling be the value contained in the first and second column, regardless of which column I changed. I don't see how this is usable for creating an update statement for the database.
It tells me the row that was edited, but nothing about the column/field.

I believe that ultimately, I will need to use the mDataProp method, but haven't gotten to that point yet.

allan

Hi,

> So it is telling be the value contained in the first and second column, regardless of which column I changed

Yup - that was my point that you will need to change the two lines:

[code]
"rowId": rowData[0],
"columnId": rowData[1]
[/code]

to be relevant to your specific case. So the first step in this is to know where (if anywhere currently) you have the row ID for the row? Is it in a hidden column, or the row ID perhaps? It is possible to have DataTables automatically add an ID to a TR element, which you could then use for this, as shown in this example: http://datatables.net/release-datatables/examples/server_side/ids.html (just return the DT_RowId property for each row).

The column ID might be a little more complex since you need to convert from the TD index to the column name in the database. Now you might know this as a static value (if for example you were only making one column editable), or you could convert from the TD index to the column name with something like:

[code]
var index = $('td', $(this).parents('tr')[0]).index( $(this).parents('td')[0] );
if ( index == 0 ) return 'field1';
else if ( index == 1 ) return 'field2';
... etc
[/code]

Hope that helps! Do just say if anything needs to be clarified.

Regards,
Allan

bkemper

Hi Allan,
Sorry for my confusion, but what field the user clicks on to edit is what I am missing, so I don't know what to change the two lines to. How do I determine that?

allan

No worries - sorry I'm not being clear!

So to determine which field is being used you basically need to map the column index to a database column. That's what the index code above should do - i.e. it uses the jQuery index function to figure out the column number in the table, and then you would say, if it is the first column then the DB field is "whatever_column_name_1", if it is the second column then its DB field "column_2" etc. That is what you would use to build up the query on the server-side.

Do that explain it okay?

There are actually other options for this which can be considered as well (I mention the one above as it is a good starting point) - for example you could attach an attribute to the HTML cell for the columns (probably the TH element) which will let you assign the columns as needed. Actually - I'll put together a quick example of that and get back to you in a few minutes, as I think it might be quite useful :-)

Allan

allan

I've put together a working example here: http://live.datatables.net/isifus/edit . Note that the Ajax request will fail, that just a cross site scripting issue for the browsers security policies, but the console will show the information that is needed.

Again, let me know if you'd like anything clarified.

Regards,
Allan