Editable Datatables PHP MySQL jQuery

Question

I have a table which is fed dynamically from the database, this works as it should, I've then put the datatables functionality on the top which is working well, the sorting, search, the pagination is again working as it should.

I'm now looking at being able to edit a row and update the data back into the db and refresh the table.

My table structure

<table class="table table-striped table-hover table-bordered" id="tobebookedtable">
<thead>
    <tr>
        <th style="display:none;">PropID</th>
        <th>ProjectNo</th>
        <th>Householder</th>
        <th>HouseNoName</th>
        <th>Street Name</th>
        <th>Postcode</th>
        <th>Telephone</th>
        <th>EPCPlanned</th>
        <th>TAM</th>
    </tr>
</thead>
<tbody>
    <tr>
        <?php
            $planning = db::getInstance()->query('CALL sp_tobebooked()');
            foreach ($planning->results() as $planning) {
        ?>
        <td style="display:none;"><?php echo $planning->PropID; ?></td>
        <td><?php echo $planning->ProjectNo; ?></td>
        <td><?php echo $planning->Householder; ?></td>
        <td><?php echo $planning->HouseNoName; ?></td>
        <td><?php echo $planning->StreetName; ?></td>
        <td><?php echo $planning->Postcode; ?></td>
        <td><?php echo $planning->Telephone; ?></td>
        <td><?php echo $planning->EPCPlanned; ?></td>
        <td><?php echo $planning->TAM; ?></td>
    </tr>
    <?php }; ?>
</tbody>
</table>

The EPCPlanned is the only field I want to update.

This here is my code for the jQuery side, the edit ability works, I can click into a cell and edit the record.

<script type="text/javascript">
$(document).ready(function() {
 $('#tobebookedtable').dataTable( {
        //"bProcessing": true,
        //"bServerSide": true,
        //"bJQueryUI": true,
        "bPaginate": true,
        "bStateSave": true
    } );

    /* Init DataTables */
    var oTable = $('#tobebookedtable').dataTable();

    /* Apply the jEditable handlers to the table */
    oTable.$('td').editable( 'tobebooked_edit.php', {
        "callback": function( sValue, y ) {
            var aPos = oTable.fnGetPosition( this );
            oTable.fnUpdate( sValue, aPos[0], aPos[1] );
            //window.location.reload();
        },
        "submitdata": function ( value, settings ) {
            console.log(value);
            console.log(settings);
            return {
                "row_id": this.parentNode.getAttribute('id'),
                "column": oTable.fnGetPosition( this )[2]
            };
        },
        "height": "26px",
        "width": "100%"
    } );    
   } );
  </script>

this is the tobebooked_edit.php

<?php 
require 'core/init.php';

    $table="temp_tobebooked";
$rawdata    = $_POST;
$query      = "show columns from $table";
$result= mysql_query($query) or die(mysql_error());

$fields     = array();
while ( $row = mysql_fetch_assoc($result) )
{
            $fieldname = $row['Field'];
            array_push($fields, $fieldname);
}

$id             = $rawdata['row_id'];
$value          = $rawdata['value'];
$column         = $rawdata['column'];
$column         = $column + 1;
$fieldname      = $fields[$column];
$value = utf8_decode($value);

$query  = "update $table set $fieldname = '$value' where PropID = '$id'";
$result = mysql_query($query);

if (!$result) { echo "Update failed"; }
else          { echo "UPD: $value"; }
?>

When it all runs and I update a record the field Updates on the screen and shows with the "UPD:" and the entered value.

But when I check my database there is no record updated. No errors show either

How can I get the data to update in my db?

Answer 1

Firstly as per rsella's comment you nee to move the row inside the loop. Secondly as suspected you are holding the ID in a hidden cell which is infact a sibling of the editable elements rather than the parent. Try changing the table structure to the following

<tbody>
    <?php
    $planning = db::getInstance()->query('CALL sp_tobebooked()');
    foreach ($planning->results() as $planning) {
    ?>
    <tr id="<?php echo $planning->PropID; ?>" >
        <td><?php echo $planning->ProjectNo; ?></td>
        <td><?php echo $planning->Householder; ?></td>
        <td><?php echo $planning->HouseNoName; ?></td>
        <td><?php echo $planning->StreetName; ?></td>
        <td><?php echo $planning->Postcode; ?></td>
        <td><?php echo $planning->Telephone; ?></td>
        <td><?php echo $planning->EPCPlanned; ?></td>
        <td><?php echo $planning->TAM; ?></td>
    </tr>
    <?php };
    ?>
</tbody>

This should mean that the parent has an ID and thus this.parentNode.getAttribute('id') should be able returning the required value.

As you're only editing the EPCPlanned cell then an alternative would be

<tbody>  
    <?php
    $planning = db::getInstance()->query('CALL sp_tobebooked()');
    foreach ($planning->results() as $planning) {
    ?>
    <tr>
        <td><?php echo $planning->ProjectNo; ?></td>
        <td><?php echo $planning->Householder; ?></td>
        <td><?php echo $planning->HouseNoName; ?></td>
        <td><?php echo $planning->StreetName; ?></td>
        <td><?php echo $planning->Postcode; ?></td>
        <td><?php echo $planning->Telephone; ?></td>
        <td id="<?php echo $planning->PropID; ?>"><?php echo $planning->EPCPlanned; ?></td>
        <td><?php echo $planning->TAM; ?></td>
    </tr>
    <?php };
    ?>
</tbody>

and in your JQuery change "row_id": this.parentNode.getAttribute('id') to this.getAttribute('id')

Answer 2

First of all, I think that this

<tr>
    <?php
        $planning = db::getInstance()->query('CALL sp_tobebooked()');
        foreach ($planning->results() as $planning) {
    ?>

should be

<?php
        $planning = db::getInstance()->query('CALL sp_tobebooked()');
        foreach ($planning->results() as $planning) {
    ?>

<tr>

Then, with

"row_id": this.parentNode.getAttribute('id'),

for each td element, you select the tr element and search for the id attribute, that doesn't exists