Why are a incomplete number of records available in a HTML table created using Editor?

Why are a incomplete number of records available in a HTML table created using Editor?

cchintall83cchintall83 Posts: 36Questions: 11Answers: 0
edited July 2023 in Editor

Link to test case: Below are the files:
table.personnel_w_termination.php

<?php

/*
 * Editor server script for DB table personnel_w_termination
 * Created by http://editor.datatables.net/generator
 */

// DataTables PHP library and database connection
include( "lib/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

// The following statement can be removed after the first run (i.e. the database
// table has been created). It is a good idea to do this to help improve
// performance.
$db->sql( "CREATE TABLE IF NOT EXISTS `personnel_w_termination` (
    `idPersonnel` int(10) NOT NULL auto_increment,
    `FName` varchar(255),
    `LName` varchar(255),
    `HireDate` date,
    `Termination` date,
    PRIMARY KEY( `idPersonnel` )
);" );

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'personnel_w_termination', 'idPersonnel' )
    ->fields(
        Field::inst( 'FName' ),
        Field::inst( 'LName' ),
        Field::inst( 'HireDate' )
            ->validator( Validate::dateFormat( 'm/d/y' ) )
            ->getFormatter( Format::dateSqlToFormat( 'm/d/y' ) )
            ->setFormatter( Format::dateFormatToSql( 'm/d/y' ) ),
        Field::inst( 'Termination' )
            ->validator( Validate::dateFormat( 'm/d/y' ) )
            ->getFormatter( Format::dateSqlToFormat( 'm/d/y' ) )
            ->setFormatter( Format::dateFormatToSql( 'm/d/y' ) )
    )
    ->process( $_POST )
    ->json();

**This file produces 231 records but the MySQL database has 277 records. **

Next File: table.personnel_w_termination.js

/*
 * Editor client script for DB table personnel_w_termination
 * Created by http://editor.datatables.net/generator
 */

(function($){

$(document).ready(function() {
/*  $.fn.dataTable.moment('YYYY, MM, DD');
    $.fn.dataTable.moment('MM, DD. YYYY ');
 */
    var editor = new $.fn.dataTable.Editor( {
        ajax: 'php/table.personnel_w_termination.php',
        table: '#personnel_w_termination',
        fields: [
            {
                "label": "FName:",
                "name": "FName"
            },
            {
                "label": "LName:",
                "name": "LName"
            },
            {
                "label": "HireDate:",
                "name": "HireDate",
                "type": "datetime",
                // "format": "YYYY/MM/DD"
                "format": "MM/DD/YY"
            },
            {
                "label": "Termination:",
                "name": "Termination",
                "type": "datetime",
                "format": "MM/DD/YY"
                // "format": "YYYY/MM/DD"
            }
        ]
    } );

    var table = $('#personnel_w_termination').DataTable( {

        dom: 'QBfrtip',
        ajax: 'php/table.personnel_w_termination.php',
        columns: [
            {
                "data": "FName"
            },
            {
                "data": "LName"
            },
            {
                "data": "HireDate"
/*              "type": "date",
                "format": "MM\/DD\/YY"
 */         },
            {
                "data": "Termination"
            }
        ],
        select: true,
        // lengthChange: false,
        buttons: [
            { extend: 'create', editor: editor },
            { extend: 'edit',   editor: editor },
            { extend: 'remove', editor: editor },
            { 
                extend: 'collection',
                text: 'Export',
                buttons: [
                    'copy',
                    'excel',
                    'csv',
                    'print'
        
                ]
            },
            { 
                extend: 'print',
                //autoPrint: false,
                text: 'Print Results',
                title: 'Personnel',
                exportOptions:
                {
                    columns: ':visible'
                },
                //title: 'Customisation of the print view window',
                messageTop: 'Employee Hire / Termination ',
                //messageTop: 'This print was produced using the Print button for DataTables',
               customize: function ( win ) {
                   $(win.document.body)
                       .css( 'font-size', '16pt' )
                       .css( 'color', 'blue')
                       .css('font-variant','small-caps')
                       .prepend(
                           // '<img src="http://datatables.net/media/images/logo-fade.png" style="position:absolute; top:0; left:0;" />'
                       );
       
                   $(win.document.body).find( 'table' )
                       .addClass( 'compact' )
                       .css( 'font-size', '14pt' )
                       .css( 'color', 'black')
                       .css( 'text-align', 'center')
                       .css('background-color', 'bisque')
                       .css('border', 'blue');
       
                   $(win.document.body).find('thead')
                           .css('font-variant', 'small-caps')
                           .css('color', 'firebrick');
                           
           }
       
            }   
        ]
    } );
} );

}(jQuery));

Debugger code (debug.datatables.net): None
Error messages shown: No errors produced
Description of problem: These files produce a HTML table with 231 files paginated over 24 pages. However there are 277 records in the MySQL database table. Why are a limited number of records resulting?

Thank you for any help.
John Chintall

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

Answers

  • allanallan Posts: 61,972Questions: 1Answers: 10,160 Site admin

    You don't have any conditions set, so I don't see and reason why less rows than there are would be returned - they all should be.

    Immediately before ->process( $_POST ) could you add ->debug(true) and then show me the full JSON returned from the server when the Ajax request is made? You might need to drop it into Pastebin or similar.

    Thanks,
    Allan

Sign In or Register to comment.