Datatable row count incorrect after server-side delete operations

aCoradatatbl

Hio
When i delete a record in my application via server-side PHP PDO/MySQL datatables incorrectly reports the number of records remaining. So instead of the row count decremented by one up to 3-4 pages @ 10 entries per page) are now being reported as removed!!! When i check the backend Database that performed the deletion it correctly shows that one record was deleted and indeed it is gone. Is this a known issue or do i need to refresh the datatable? if so how? Ohter than reinstall a new copy of the backend database i've yet to find a way to make all the missing rows appear again. After 2-3 test deletions all records are gone - from 156 records to zero having performed 3 or 4 single row deletions - HELP!

kthorngren

How are you deleting the record?

Are you using Editor to delete the record?

Are you using server side processing (serverSide is true)?

Maybe you can start with posting your Datatables code and how you are deleting the record.

Kevin

aCoradatatbl

Hi @kthorngren

Im building the rows with php after the query to attain the data then sending it backto the html/php page and echoing the resultant <tr> tags into the <tbody> of the hardcoded table markup. Im using the button library and many other datatables plugins and everything has worked well so far but now this problem has started. Im assuming datatables is somehow caching the rows and after a deletion it does not know about it and somehow is getting confused. My deletion process is simple: user clicks a row to delete (the Mark plugin) selects and highlights the row, then from a command in the side bar user clicks 'delete highlighted' The primary-key of the row is sent via ajax to the backend and a php class method takes care of the mysql deletion query.

The row IS deleted ok, reflected in the MySQL table - but on refreshing datatable via ctrl F5 up 30 or more rows are missing from view in datatables even though they do exist in MySQL.

I've summarised the code below (and 'implied' some data for brevity and privacy of my client):

PHP
public function build_agreements_dtble( $sessionDB )
{
$dbOb = $this->dbOb;
$dbconx = $dbOb->getdbconx();
$arryvals = array();

                $qry = "SELECT   // long comma seperates list of aliased column names 
                        FROM     // list of required table names
                        WHERE    // criteria to base query on 
                        ORDER BY // blah blah ...

                try 
                {
                    $stmt = $dbconx->prepare( $qry );
                    $stmt->execute();
                    $arr  = $stmt->fetchAll(PDO::FETCH_ASSOC);
                    $rows ="";

                    for(  $r=0;  $r <= (count($arr)-1);  $r++  ) 
                    {
                        $rows.="<tr id='".$arr[$r]['pkid'] ."' >

                           <td>"       . $arr[$r]['AgreePkid']                                    . "</td>
                           <td>"       . $reportext                                               . "</td>
                           <td>"       . $propref                                                 . "</td>
                           <td>"       . $arr[$r]['raer']                                         . "</td>
                           <td>"       . $arr[$r]['Name']                                         . "</td>               
                           <td>"       . $arr[$r]['Manager']                                      . "</td>
                           <td>"       . $arr[$r]['apsf']                                         . "</td>
                           <td>"       . $ervperannum                                             . "</td>
                           <td>"       . $nextrentpa                                              . "</td>
                           <td><span>" . $nextreview_us."</span>"      . $nextreview_uk           . "</td>                 
                           <td><span>" . $nextreviewplus1_us."</span>" . $nextreviewplus1_uk      . "</td>                  
                           <td><span>" . $nextbreak_us."</span>"       . $nextbreak_uk            . "</td>                 
                           <td>"       . $this->calcOb->get_next_breakinfo( $arr[$r]['auid'] )    . "</td>
                           <td><span>" . $nextcritical_us."</span>"    . $nextcritical_uk         . "</td>
                           <td>"       . $maxleasecomment                                         . "</td>

                        </tr>"; 
                    }
                    return $rows;       
                }   
                catch (PDOException $e) 
                {
                    return __FUNCTION__ . $e->getMessage();
                }                   
        }

the html/php page :

                    <div class="box-body table-responsive">
                        <table id="tblDatabaseOverview" class="table-condensed table-bordered table-striped row-border order-column nowrap" width="100%">
                                <thead>
                                <tr>
                                    <th class="noButtVis">ID</th>
                                    <th class="noButtVis">Report</th>
                                    <th>Property</th>
                                    <th>Unit</th>
                                    <th>Plan</th>
                                    <th>Owner</th>      
                                    <th>Manager</th>                    
                                    <th>Subletter</th>
                                    <th>Area SqFt</th>  
                                    <th>Rent P. A.</th>
                                    <th>Agree Start Date</th>
                                    <th>Agree End Date</th>
                                    <th>Est SqFt</th>
                                    <th>ERV Quote</th>
                                    <th>ERV Release</th>
                                </tr>
                                </thead>
                                <tbody><?php echo $dbOverviewTbl; ?></tbody>
                                <tfoot>
                                <tr>
                                    <th>ID</th>
                                    <th>Report</th>
                                    <th>Property</th>
                                    <th>Unit</th>
                                    <th>Plan</th>
                                    <th>Owner</th>      
                                    <th>Manager</th>                    
                                    <th>Subletter</th>
                                    <th>Area SqFt</th>  
                                    <th>Rent P. A.</th>
                                    <th>Agree Start Date</th>
                                    <th>Agree End Date</th>
                                    <th>Est SqFt</th>
                                    <th>ERV Quote</th>
                                    <th>ERV Release</th>
                                </tr>
                                </tfoot>
                          </table>
                    </div>

I guess i should be using one of the documented methods to acquire data to build my datatable??

kthorngren

I don't see any Datatable code?

Sounds like all the processes you are doing are not using Datatables APIs. Datatables will build a data cache and if you are manipulating the data without using the Datatables API's then you need to use something like rows().invalidate(), cells().invalidate (you can use the singular row() or cell() also) to have Datatables update its cache.

Since it sounds like all your operations are outside of Datatables maybe you should test them without Datatables. When you refresh the page if 30 rows are missing it is probably not Datatables but the code fetching and displaying the table.

Once you get the processes working then start using Datatables again. Thats the approach I would take.

Kevin

aCoradatatbl

the datatables javascript is :

        var dTblOverview = $('#tblDatabaseOverview').DataTable(
        {
              'paging'         : true,
              'lengthChange'   : true,
              'searching'      : true,
              'ordering'       : true,
              'info'           : true,
              'autoWidth'      : true,
              'orderCellsTop'  : true,
              'fixedHeader'    : true,
              'stateSave'      : false,
              'stateDuration'  : -1,            // -1 = session storage     0 = local storage
              'order'          : [ 2, 'asc' ],
              'select'         : { style: 'single' },
              'rowId'          : 'td#pkid', 
              'columnDefs'    : 
              [
                    {  targets : [ 0 ],  visible : false, searchable : false },
                    {  targets : [ 1 ],  visible : false }
                 /* {  targets : [ 10 ], 'type'  : 'date' } */
              ],

              'mark'           : true,

              'dom'            : '<"pull-left"B><"pull-right"l>frtip',

              'buttons'        : [ 
                                     // buttons settings here ... 
                                 ]

              // 'initComplete' : function ( settings ) {}    
        });

I can run CRUD operations directly against the DB in the SQL console - i guess the query which is pretty complex using joins is somewhere incorrect

aCoradatatbl

$('#tblDatabaseOverview').rows().invalidate().draw();

Hey Kevin,
Works like a dream - many thanks for the help and advice!

Ant.