MySQL and Jeditable (almost there!)

eyeruleall

Hey everyone.

I'd like to start off by saying that I am really looking forward to using datatables for my project, and a big shoutout to Allan for trying so patiently to try to help all of us understand how to use it.

I've been trying to research this and figure out how it works for days, and I think I'm almost there, but it just isn't updating the database. Not only would I like to get help getting this to work, but I'd like to supply the code I've come up with so far to help the community, because I see a lack of certain examples that I think I've included nicely.

I think I'm very close to getting this right, but I honestly am new to programming. Don't laugh at me too hard guys. :-)

This is a simple customers database with simple customer data. A total of 9 columns. Right now I just want to edit, but if I can get this working I'd like to include adding and deleting rows as well, and I'd like to get all of the code necessary to get the whole thing up and running up on the forums for the world to see.

From what I can see, nobody has been able to get this working right. I know that can't possibly be the case, but I can't find a working example anywhere.

Once again, this code is not working, and is not complete. Please help if you know more than me, or have completed this task.

Anyways, disclaimers aside, here's my table.

[code]




ID
Email
First Name
Last Name
Address
City
State
Zip
Phone




Loading Data from Server




ID
Email
First Name
Last Name
Address
City
State
Zip
Phone



[/code]

As you can see it populates 9 columns from the mySQL database.

Next we initialize Datatables

[code]
$(document).ready(function() {
var oTable = $('#example').dataTable( {
"aoColumns": [
{"sClass": "ID", "aTargets": [ 0 ]},
{"sClass":"Email", "aTargets": [ 1 ]},
{"sClass":"FirstName", "aTargets": [ 2 ]},
{"sClass":"LastName", "aTargets": [ 3 ]},
{"sClass":"Address", "aTargets": [ 4 ]},
{"sClass":"City", "aTargets": [ 5 ]},
{"sClass":"State", "aTargets": [ 6 ]},
{"sClass":"Zip", "aTargets": [ 7 ]},
{"sClass":"Phone", "aTargets": [ 8 ]}
],
"sPaginationType": "full_numbers",
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "../Connections/customers.php",
"fnDrawCallback": function () {
$('td', this.fnGetNodes()).editable( '../Connections/edit.customers.php', {
indicator : 'Saving...',
tooltip : "Double click to edit.",
event : "dblclick",
onblur : 'submit',
"callback": function( sValue, y ) {
var aPos = oTable.fnGetPosition(this);
oTable.fnUpdate( sValue, aPos[0], aPos[1] );
},
"submitdata": function ( value, settings ) {
return {
"id" : this.parentNode.getAttribute('id').substring(4)-1,
"row_id": this.parentNode.getAttribute('id'),
"column": this.getAttribute('class'),
};
},
"height": "14px"
});
}
});
} );
[/code]

To explain to those who are as confused as I was a week ago, I've used aoColumns and sClass to set the class to my columns equal the database table names. Then I used "column": this.getAttribute('class') to send this to the edit.customers.php file, along with row_id (being added by the DT_RowId from the example in /scripts/id.php.)

So when I populate my table, I get a class added onto each equal to the database column name, and also an id added to each tag equal to it's row (this is from DT_RowID). These then go to the php file specified.

[code]
<?php
require_once('*.php');
session_start();

$column = $_POST['column'];
$row_id = $_POST['row_id'];
$value = mysql_escape_string($_POST['value']);

$sql = "UPDATE `customers` SET `$column`=\'$value\' WHERE `customers`.`ID` = $row_id LIMIT 1";





mysql_query($sql);

echo $value;
?>
[/code]

I'm at the point now where I'm at a loss. I've been through firebug and everything seems like it should be working fine, but it's just not updating the database. If we can get one good example going showing everything in place, I think it would really help the community out and save a lot of people the frustration that I've been suffering through.

Do I just have one little thing wrong or do I just not understand how this works at all?

eyeruleall

This is what's in the Post, btw.

column State
id 0
row_id row_1
value FL
Source
value=FL&id=0&row_id=row_1&column=State

eyeruleall

Just figured it out! correct sql was $sql = "UPDATE customers SET ".$column." = '".$value."' WHERE ID = ".$row_id;