Editor Create : SQLSTATE[42000]: Syntax error or access violation: 1064 .....

fc338339

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as cl ( course_id, room_id, lecturer_id, class_date, class_time ) VAL' at line 1

I followed tutorial "Join tables - self referencing join＂to create an editor datatables by servicer side processing method,

http://editor.datatables.net/examples/advanced/joinSelf.html

• Inline editing OK
• Editor Create: return above quoted error, please help

1. Service-Side Scripting : php/cour2.php

<?php
// DataTables PHP library
include( $_SERVER['DOCUMENT_ROOT']."/modules/Editor-PHP-1.5.3/php/DataTables.php" );

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

// ensure can show chinese text     
$db->sql( "SET NAMES 'utf8'" );

// Build our Editor instance and process the data coming from _POST  
// Define parent table alias :  'abc_cou_course_class as cl'
// Define parent table key :  'cl.id' 
Editor::inst( $db, 'abc_cou_course_class as cl', 'cl.id' )
    ->fields(
        Field::inst( 'cl.course_id' )
            ->options( 'abc_cou_course', 'id', 'name_en' ), 
        Field::inst( 'c.name_en' ),     
        Field::inst( 'cl.room_id' )
            ->options( 'abc_cou_room', 'id', 'name_en' ),
        Field::inst( 'r.name_en' ),     
        Field::inst( 'cl.lecturer_id' )
            ->options( 'abc_cou_lecturer', 'id', 'name_en' ),   
        Field::inst( 'le.name_en' ),
        Field::inst( 'cl.class_date' )
                ->validator( 'Validate::dateFormat', array(
                    "format"  => Format::DATE_ISO_8601,
                    "message" => "Please enter a date in the format yyyy-mm-dd"
                ))
                ->getFormatter( 'Format::date_sql_to_format', Format::DATE_ISO_8601 )
                ->setFormatter( 'Format::date_format_to_sql', Format::DATE_ISO_8601 ),      
        Field::inst( 'cl.class_time' )  
    )
// leftJoin 3 tables here
    ->leftJoin( 'abc_cou_course as c ', 'c.id', '=', 'cl.course_id' )
    ->leftJoin( 'abc_cou_room as r ', 'r.id', '=', 'cl.room_id' )
    ->leftJoin( 'abc_cou_lecturer as le ', 'le.id', '=', 'cl.lecturer_id' ) 
    ->process( $_POST )
    ->json();

1. Javascript + Html

script type="text/javascript" language="javascript" class="init">
// use a global for the submit and return data rendering in the examples
var editor; 
var table; 
 jQuery(document).ready(function() {
/* 1. editor start */           
    editor = new jQuery.fn.dataTable.Editor({           
        "ajax"  : "/modules/mod_aa_editor2/php/cour2.php",
        "table" : "#myTable2",
        "fields": [{
                label   : "Course:",
                name    : "cl.course_id",
                type    : "select"
            }, {
                label   : "Room:",
                name    : "cl.room_id",
                type    : "select"
            }, {
                label   : "Lecturer:",
                name    : "cl.lecturer_id",
                type    : "select"
            }, {
                label   : "Class Date:",
                name    : "cl.class_date",
                type    : "datetime",
                def     : function () { return new Date(); }
            }, {
                label   : "Class Time:",
                name    : "cl.class_time",
                type    : "datetime",
                def     : function () { return new Date(); }, 
                format  : 'HH:mm'
            }]
        }); 

/* 2. DataTable is initialised here */   
     table = jQuery('#myTable2').DataTable({    
        "lengthMenu": [[5,15, 50, 100,-1 ], [5,15, 50, 100,"All"]],     
        dom: "BCfrltip",        
        ajax: {
            url     :"/modules/mod_aa_editor2/php/cour2.php",
            type    :"POST" 
            },
        serverSide  : true,             
        columns: [
            { data: "c.name_en",  editField: "cl.course_id" }, 
            { data: "cl.class_date" },
            { data: "cl.class_time" },
            { data: "r.name_en",  editField: "cl.room_id" },
            { data: "le.name_en", editField: "cl.lecturer_id" }
        ],  
        select  : true,
        buttons     : [
            {   extend  : "create", 
                editor  : editor,
                formButtons     : ['Create', { label: 'Cancel', fn: function () { this.close(); } }]},
            {   extend  : "edit",   
                editor  : editor,
                formButtons     : ['Edit',   { label: 'Cancel', fn: function () { this.close(); } }]},
            {   extend  : "remove", 
                editor  : editor,
                formButtons     : ['Delete', { label: 'Cancel', fn: function () { this.close(); } }]},
            {   extend  : 'collection',
                text    : 'Export',     
                buttons     : [
                    'copy',
                    'excel',
                    'csv',
                    'pdf',
                    'print'
                ]
            }]
    }); 
         
/* 3. datatables column filter start */
     jQuery('#myTable2 tfoot th').each(function () {
         var title = jQuery('#myTable2 thead th').eq(jQuery(this).index()).text();
         jQuery(this).html('<input type="text" placeholder="' + title + '" />');
     }); 
     table.columns().every(function () {
            var that = this;
            jQuery('input', this.footer()).on('keyup change', function () {
                that
                        .search(this.value)
                        .draw();
            });
     });    
}); 
</script>

<body class="dt-example">
    <div class="container">
        <section>
  <table id="myTable2" class="display" cellspacing="0" width="100%">
        <thead>
            <tr>
                <th>Course Name</th>
                <th>Class Date</th>
                <th>Class Time</th>
                <th>Room</th>
                <th>Lecturer</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
                <th>Course Name</th>
                <th>Class Date</th>
                <th>Class Time</th>
                <th>Room</th>
                <th>Lecturer</th>
            </tr>
        </tfoot>
    </table>
  </section>  
 </div><!--end container-->
</body> 

fc338339

I try to amend the problem by referring below link:
https://www.datatables.net/forums/discussion/30402/override-editor-data-default-function

1. correct method 1

  editor = new jQuery.fn.dataTable.Editor({          
        "ajax"  : "/modules/mod_aa_editor2/php/cour2.php",
        "table" : "#myTable2",

1. wrong method 2 : now can generate json data but still cannot insert into database

editor = new jQuery.fn.dataTable.Editor({   
        "ajax": {
                url: '/modules/mod_aa_editor2/php/cour2.php',
                contentType: 'application/json',
                data: function ( d ) {
                        return JSON.stringify( d );
                }
        },
        "table": "#myTable2",

wrong result from 2. because I wrongly applied Alias for parent table

{action: "create", data: {0: {,…}}}
action: "create"
data: {0: {,…}}
0: {,…}
cl: {course_id: "3631", room_id: "4", lecturer_id: "1", class_date: "2016-02-06", class_time: "20:02"}
class_date: "2016-02-06"
class_time: "20:02"
course_id: "3631"
lecturer_id: "1"
room_id: "4"

allan

Good to hear you got it working - thanks for posting back.

I will look into why the default doesn't allow this.

Allan

fc338339

Thanks for Allan teaching, finally i can create new record as -

1. No alias for parent table
   previously I set alias 'cl' at '/modules/mod_aa_editor2/php/cour2.php'

Editor::inst( $db, 'abc_cou_course_class as cl', 'cl.id' )
    ->fields(

now I replace all 'cl.' back to 'abc_cou_course_class.'

Editor::inst( $db, 'abc_cou_course_class' , 'id' )
    ->fields(

and editor back to simple way , means

editor = new jQuery.fn.dataTable.Editor({         
      "ajax"  : "/modules/mod_aa_editor2/php/cour2.php",
      "table" : "#myTable2",

Thanks again from Allan