Editor - Not updating the table after a new record is posted

supportNE

Hi

Currently unable to add new records to an existing datatable. The records are successfully posted, and can be seen after a refresh, but are not viewable after the post.

Any help would be gratefully received. 6 days left of the trial here, which means I have to fix this bug and convince my boss to purchase in that time.

Here is my js :

$(document).ready(function() {
    var tomorrow = new Date();
    tomorrow.setDate(tomorrow.getDate() + 1);

    var editor = new $.fn.dataTable.Editor( {
        ajax: '/editor-php/controllers/quotes.php',
        table: '#example',
        fields: [
            { label: 'id', name: 'quote.id', type: 'hidden'},
            { label: 'Name', name: 'quote.client_name'},
            { label: 'Email',  name: 'quote.email'},
            { label: 'Phone',  name: 'quote.telephone'},
            { label: 'Quote Fees',  name: 'quote.quote_fees'},
            { label: 'Quote Dbs',  name: 'quote.quote_dbs'},
            { label: 'Source',  name: 'quote.source', type: 'select'},
            { label: 'Date Received',  name: 'quote.date_received', type: 'datetime', def: new Date().toJSON().slice(0,10).replace(/-/g,'-')},
            { label: 'Follow Up Date',  name: 'quote.follow_up_date', type: 'datetime', def: tomorrow.toJSON().slice(0,10).replace(/-/g,'-')},
            { label: 'Case Type',  name: 'quote.case_file_type', type: 'select', def: '2'},
            { label: 'Status', name: 'quote.status', type: 'select', def:'1'},
            { label: 'Quote Sales',  name: 'quote.sales', type: 'select'},
            { label: 'Notes', name: 'quote.notes'},
        ]
    } );

    // Activate an inline edit on click of a table cell
    $('#example').on('click', 'tbody td:not(:first-child):not(:nth-child(14))', function (e) {
        editor.inline(this);
    });

    $('#example').DataTable({
        dom: "Bfrtip",
        ajax: "/editor-php/controllers/quotes.php",
        order: [[7, 'desc']],
        columns: [
            {
                data: null,
                defaultContent: '',
                className: 'select-checkbox',
                orderable: false
            },
            {data: "quote.client_name"},
            {data: "quote.email"},
            {data: "quote.telephone"},
            {data: "quote.quote_fees", render: $.fn.dataTable.render.number(',', '.', 0, '£')},
            {data: "quote.quote_dbs", render: $.fn.dataTable.render.number(',', '.', 0, '£')},
            {data: "source.name", editField: "quote.source" },
            {data: "quote.date_received"},
            {data: "quote.follow_up_date"},
            {data: "case_file_type.name", editField: "quote.case_file_type"},
            {data: "quote_status.name", editField: "quote.status"},
            {data: "quote_sales.name", editField: "quote.sales"},
            {data: "quote.notes"},
            {
                data: null,
                defaultContent: '<input type="button" class="name" id="name" value=" Create Case "/>',
                orderable: false
            },
        ],
        select: {
            style: 'os',
            selector: 'td:first-child'
        },
        buttons: [
            {extend: "create", editor: editor},
            {extend: "edit", editor: editor},
            {extend: "remove", editor: editor}
        ]
    });

    $('#example tbody').on('click', '.name', function () {
        var row = $(this).closest('tr');
        var id  = row[0].id.replace('row_','');
        window.location.href = "/case-files/createFromQuote/" + id;
    });

});

And here is the php bit.

<?php

/*
 * Example PHP implementation used for the index.html example
 */

// DataTables PHP library
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;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'quote' )
    ->fields(
        Field::inst('quote.id'),
        Field::inst('quote.client_name')
            ->validator(Validate::notEmpty(ValidateOptions::inst()
                ->message( 'A Client Name is required' )
            )),
        Field::inst('quote.email')
            ->validator(Validate::notEmpty(ValidateOptions::inst()
                ->message('Please enter an email address')
            )),
        Field::inst('quote.telephone')
            ->validator(Validate::notEmpty(ValidateOptions::inst()
                ->message('Please enter a phone number')
            )),
        Field::inst('quote.quote_fees'),
        Field::inst('quote.quote_dbs'),
        Field::inst('quote.date_received')
            ->validator(Validate::dateFormat('Y-m-d'))
            ->getFormatter(Format::dateSqlToFormat('Y-m-d'))
            ->setFormatter(Format::dateFormatToSql('Y-m-d')),
        Field::inst('quote.follow_up_date')
            ->validator(Validate::dateFormat('Y-m-d'))
            ->getFormatter(Format::dateSqlToFormat('Y-m-d'))
            ->setFormatter(Format::dateFormatToSql('Y-m-d')),
        Field::inst('quote.notes'),
        Field::inst('quote.source')
            ->options(Options::inst()
                ->table('source')
                ->value('id')
                ->label('name')
            )
            ->validator(Validate::dbValues()),
        Field::inst('quote.case_file_type')
            ->options(Options::inst()
                ->table('case_file_type')
                ->value('id')
                ->label('name')
            )
            ->validator(Validate::dbValues()),
        Field::inst('source.name'),
        Field::inst('case_file_type.name'),
        Field::inst('quote.status')
            ->options(Options::inst()
                ->table('quote_status')
                ->value('id')
                ->label('name')
            )
            ->validator(Validate::dbValues()),
        Field::inst('quote_status.name'),
        Field::inst('quote.sales')
            ->options(Options::inst()
                ->table('quote_sales')
                ->value('id')
                ->label('name')
            )
            ->validator(Validate::dbValues()),
        Field::inst('quote_sales.name')
    )
    ->leftJoin('source', 'source.id', '=', 'quote.source')
    ->leftJoin('case_file_type', 'case_file_type.id', '=', 'quote.case_file_type')
    ->leftJoin('quote_status', 'quote_status.id','=','quote.status')
    ->leftJoin('quote_sales', 'quote_sales.id','=','quote.sales')
    ->process( $_POST )
    ->json();

supportNE

If I comment out all references to the left joins, it works. If I uncomment all references to a single left join, it doesn't. Curious.

supportNE

...further to the previous information, it appears that the ajax data is empty when a post is made.

{data: []}
data: []

allan

Could you comment them back in and add: ->debug(true) just before the ->process(...) call please? Then when you submit the "new record" request, the JSON return shown in the browser's network inspector for that request will include some debug information about the SQL. Can you show me the returned JSON please?

Thanks,
Allan

supportNE

Thanks for getting back to me @allan, very kind.

Here are the results :

{"data":[],"debug":[{"query":"SELECT  `id` as 'id' FROM  `source` WHERE `id` = :where_0 ","bindings":[{"name":":where_0","value":"20","type":null}]},{"query":"SELECT  `id` as 'id' FROM  `case_file_type` WHERE `id` = :where_0 ","bindings":[{"name":":where_0","value":"2","type":null}]},{"query":"SELECT  `id` as 'id' FROM  `quote_status` WHERE `id` = :where_0 ","bindings":[{"name":":where_0","value":"1","type":null}]},{"query":"SELECT  `id` as 'id' FROM  `quote_sales` WHERE `id` = :where_0 ","bindings":[{"name":":where_0","value":"1","type":null}]},{"query":"INSERT INTO  `quote`  ( `id`, `client_name`, `email`, `telephone`, `quote_fees`, `quote_dbs`, `source`, `date_received`, `follow_up_date`, `case_file_type`, `status`, `sales`, `notes` ) VALUES (  :id,  :client_name,  :email,  :telephone,  :quote_fees,  :quote_dbs,  :source,  :date_received,  :follow_up_date,  :case_file_type,  :status,  :sales,  :notes )","bindings":[{"name":":id","value":"","type":null},{"name":":client_name","value":"Jake Bugg","type":null},{"name":":email","value":"j.bugg@tricky.co.uk","type":null},{"name":":telephone","value":"01678-222787","type":null},{"name":":quote_fees","value":"500","type":null},{"name":":quote_dbs","value":"230","type":null},{"name":":source","value":"20","type":null},{"name":":date_received","value":"2020-02-04","type":null},{"name":":follow_up_date","value":"2020-02-05","type":null},{"name":":case_file_type","value":"2","type":null},{"name":":status","value":"1","type":null},{"name":":sales","value":"1","type":null},{"name":":notes","value":"","type":null}]},{"query":"SELECT  `quote`.`id` as 'quote.id', `quote`.`client_name` as 'quote.client_name', `quote`.`email` as 'quote.email', `quote`.`telephone` as 'quote.telephone', `quote`.`quote_fees` as 'quote.quote_fees', `quote`.`quote_dbs` as 'quote.quote_dbs', `quote`.`source` as 'quote.source', `source`.`name` as 'source.name', `quote`.`date_received` as 'quote.date_received', `quote`.`follow_up_date` as 'quote.follow_up_date', `quote`.`case_file_type` as 'quote.case_file_type', `case_file_type`.`name` as 'case_file_type.name', `quote`.`status` as 'quote.status', `quote_status`.`name` as 'quote_status.name', `quote`.`sales` as 'quote.sales', `quote_sales`.`name` as 'quote_sales.name', `quote`.`notes` as 'quote.notes' FROM  `quote` LEFT JOIN `source` ON `source`.`id` = `quote`.`source`  LEFT JOIN `case_file_type` ON `case_file_type`.`id` = `quote`.`case_file_type`  LEFT JOIN `quote_status` ON `quote_status`.`id` = `quote`.`status`  LEFT JOIN `quote_sales` ON `quote_sales`.`id` = `quote`.`sales` WHERE `quote`.`id` = :where_0 ","bindings":[{"name":":where_0","value":"","type":null}]}]}

Thanks for any help this allows you to give.

allan

Thanks!

That shows that the insert is happening okay, but when Editor is then trying to read the data back from the newly inserted row it is doing:

WHERE `quote`.`id` = :where_0 

That is correct, but the problem is in the binding: "value": "",. Specifically it is looking for quote.id = '' which it won't find!

I presume quote.id is a primary key and a sequence / auto increment? If not - then it has to be! Also, could you change:

Field::inst('quote.id'),

to be:

Field::inst('quote.id')->set(false),

Thanks,
Allan

supportNE

Hi Allan, set false has fixed it - many thanks !