DataTables
Advanced interaction
features for your tables.
Editor
Comprehensive editing
library for DataTables.
Manual
Download
≡ Show site navigation

Delete not working

Delete not working

map@odenterprise.orgmap@odenterprise.org Posts: 14Questions: 4Answers: 0
in Free community support

Hi,

I have joined 3-4 tables using the Datatables editor, but when I try to delete the row it is giving me the follwing error -

Notice: Undefined variable: ids in C:\xampp\htdocs\impact-map\html\survey\templates\dbadmin\Datatables-editor\php\Editor\Join.php on line 692
{"fieldErrors":[],"error":"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'org_profiles.profile_id' in 'where clause'","data":[],"ipOpts":[],"cancelled":[]}

Here is my code -

simple.html

    var table = $('#example').DataTable( {
        dom: "Bfrtip",
        ajax: "../php/staff.php",
        order: [[ 1, 'asc' ]],
        "autoWidth": false,
        "scrollY": 400,
        "scrollX": true,
        "scrollCollapse": true,
        "pageLength": 50,
/*        "paging":         false,*/

        columns: [
            {
                data: null,
                defaultContent: '',
                className: 'select-checkbox',
                orderable: false
            },
            //{ data: "org_profiles.profile_id", editField: "", "visible" : false},
            { data: "org_profiles.profile_id", editField: ""},
            { data: "org_profiles.org_name" },
            
            { data: "org_profiles.industry_id", editField: "org_profiles.industry_id"},
            { data: "org_profiles.industry_other" , editField: "org_profiles.industry_other"},

            { data: "org_profiles.org_description" },
            { data: "org_profiles.org_profile_status"},
            { data: "org_profiles.createdAt" , editField: "" },
            { data: "org_profiles.updatedAt" , editField: ""},

            { data: "org_profiles.org_additional" , editField: "org_profiles.org_additional"},
            { data: "org_profiles.org_confidence" , editField: "org_profiles.org_confidence"},
            { data: "org_profiles.org_greatest_impact" , editField: "org_profiles.org_greatest_impact"},
            { data: "org_profiles.org_greatest_impact_detail" , editField: "org_profiles.org_greatest_impact_detail"},
            { data: "org_profiles.org_profile_category" , editField: "org_profiles.org_profile_category"},
            { data: "org_profiles.org_profile_src" , editField: "org_profiles.org_profile_src" },
            { data: "org_profiles.org_profile_year" , editField: "org_profiles.org_profile_year"},
            { data: "org_profiles.org_type" , editField:  "org_profiles.org_type"},
            { data: "org_profiles.org_type_other" , editField: "org_profiles.org_type_other"},
            { data: "org_profiles.org_url" , editField: "org_profiles.org_url"},
            { data: "org_profiles.org_year_founded" , editField: "org_profiles.org_year_founded" },

            { data: "org_profiles.machine_read" , editField: "org_profiles.machine_read"},


            { data: "org_profiles.org_size" },

            
            { data: "org_locations.org_hq_city", editField: ""},
            { data: "org_locations.org_hq_st_prov", editField: ""},
            
            { data: "org_contacts.survey_contact_email", editField: "org_contacts.survey_contact_email"},
            { data: "org_contacts.survey_contact_first", editField: "org_contacts.survey_contact_first"},
            { data: "org_contacts.survey_contact_last", editField: "org_contacts.survey_contact_last"},
            { data: "org_contacts.survey_contact_phone", editField: "org_contacts.survey_contact_phone"},
            { data: "org_contacts.survey_contact_title", editField: "org_contacts.survey_contact_title"},
            
            { data: "org_country_info.org_hq_country", editField: ""},
            { data: "org_country_info.org_hq_country_income", editField: ""},
/*          { data: "org_country_info.org_hq_country_income_code", editField: ""},
            { data: "org_country_info.org_hq_country_locode", editField: ""},*/
            { data: "org_country_info.org_hq_country_region", editField: ""},
/*          { data: "org_country_info.org_hq_country_region_code", editField: ""},
            { data: "org_country_info.ISO2", editField: ""},*/
            { data: "org_country_info.c_lat", editField: ""},
            { data: "org_country_info.c_lon", editField: ""},
            { data: "org_data_use", render: "[, ].data_type"},

            { data: "data_applications.advocacy", editField: "data_applications.advocacy"},
            { data: "data_applications.advocacy_desc", editField: "data_applications.advocacy_desc"},
            { data: "data_applications.org_opt", editField: "data_applications.org_opt"},
            { data: "data_applications.org_opt_desc", editField: "data_applications.org_opt_desc"},
            { data: "data_applications.use_other", editField: "data_applications.use_other"},
            { data: "data_applications.use_other_desc", editField: "data_applications.use_other_desc"},
            { data: "data_applications.prod_srvc", editField: "data_applications.prod_srvc"},
            { data: "data_applications.prod_srvc_desc", editField: "data_applications.prod_srvc_desc"},
            { data: "data_applications.research", editField: "data_applications.research"},
            { data: "data_applications.research_desc", editField: "data_applications.research_desc"}

        ],
        keys: {
            columns: ':not(:first-child)',
            editor:  editor
        },

        select: {
            style:    'os',
            selector: 'td:first-child'
        },
        buttons: [
    //        { extend: "create", editor: editor },
     //       { extend: "edit",   editor: editor },
            { extend: "remove", editor: editor },
             
             {
                extend: 'colvis',
                columns: ':not(.noVis)'
            },

staff.php

Editor::inst( $db, 'org_profiles' )
->pkey( 'profile_id' )
    ->fields(
        Field::inst( 'org_profiles.profile_id' ),
        Field::inst( 'org_profiles.org_name' ),
        Field::inst( 'org_profiles.org_description' ),
        Field::inst( 'org_profiles.org_size' ),
        Field::inst( 'org_profiles.org_profile_status' ),

        Field::inst( 'org_profiles.industry_id' )
                ->validator( 'Validate::dbValues' ),
        Field::inst( 'org_profiles.industry_other' ),
        Field::inst( 'org_profiles.org_additional' ),
        Field::inst( 'org_profiles.org_confidence' ),
        Field::inst( 'org_profiles.org_greatest_impact' ),
        Field::inst( 'org_profiles.org_greatest_impact_detail' ),
        Field::inst( 'org_profiles.org_profile_category' ),
        Field::inst( 'org_profiles.org_profile_src' ),
        Field::inst( 'org_profiles.org_profile_year' ),
        Field::inst( 'org_profiles.org_type' ),
        Field::inst( 'org_profiles.org_type_other' ),
        Field::inst( 'org_profiles.org_url' ),
        Field::inst( 'org_profiles.org_year_founded' ),
        Field::inst( 'org_profiles.createdAt' ),
        Field::inst( 'org_profiles.updatedAt' ),
        Field::inst( 'org_profiles.machine_read' ),

        Field::inst( 'org_profiles.location_id' )
                ->options( Options::inst()
                ->table( 'org_locations' )
                ->value( 'location_id' )
                ->label( 'org_hq_city' )
            )
            ->validator( 'Validate::dbValues' ),
        Field::inst( 'org_locations.org_hq_city' ),
        Field::inst( 'org_locations.org_hq_st_prov' ),


        Field::inst( 'org_contacts.survey_contact_email' ),
        Field::inst( 'org_contacts.survey_contact_first' ),
        Field::inst( 'org_contacts.survey_contact_last' ),
        Field::inst( 'org_contacts.survey_contact_phone' ),
        Field::inst( 'org_contacts.survey_contact_title' ),
        
                Field::inst( 'org_profiles.country_id' )
                ->options( Options::inst()
                ->table( 'org_country_info' )
                ->value( 'country_id' )
                ->label( 'org_hq_country' )
            )
            ->validator( 'Validate::dbValues' ),

         Field::inst( 'org_country_info.org_hq_country' ),

         Field::inst( 'org_country_info.org_hq_country_income' ),
         Field::inst( 'org_country_info.org_hq_country_income_code' ),
         Field::inst( 'org_country_info.org_hq_country_locode' ),
         Field::inst( 'org_country_info.org_hq_country_region' ),
         Field::inst( 'org_country_info.org_hq_country_region_code' ),
         Field::inst( 'org_country_info.ISO2' ),
         Field::inst( 'org_country_info.c_lat' ),
         Field::inst( 'org_country_info.c_lon' ),
                    

         Field::inst( 'data_applications.advocacy_desc' ),
         Field::inst( 'data_applications.org_opt_desc' ),
         Field::inst( 'data_applications.use_other_desc' ),
         Field::inst( 'data_applications.prod_srvc_desc' ),
         Field::inst( 'data_applications.research_desc' ),

         Field::inst( 'data_applications.advocacy' ),
         Field::inst( 'data_applications.org_opt' ),
         Field::inst( 'data_applications.use_other' ),
         Field::inst( 'data_applications.prod_srvc' ),
         Field::inst( 'data_applications.research' )

    )
    ->leftJoin( 'org_locations', 'org_locations.location_id', '=', 'org_profiles.location_id' )
    ->leftJoin( 'org_contacts', 'org_contacts.profile_id', '=', 'org_profiles.profile_id' )
    ->leftJoin( 'org_country_info', 'org_country_info.country_id', '=', 'org_profiles.country_id' )
    ->leftJoin( 'data_applications', 'data_applications.profile_id', '=', 'org_profiles.profile_id' )
    //->leftJoin( 'org_data_use', 'org_data_use.profile_id', '=', 'org_profiles.profile_id' )


    ->join(
        Mjoin::inst( 'org_data_use' )
            ->link( 'org_data_use.profile_id', 'org_profiles.profile_id' )
            //->link( 'permission.id', 'user_permission.permission_id' )
            //->order( 'name asc' )
            ->fields(
                Field::inst( 'object_id' )
                    //->validator( 'Validate::required' )
                    ->options( Options::inst()
                        ->table( 'org_data_use' )
                        ->value( 'profile_id' )
                        ->label( 'data_type' )
                    ),
                Field::inst( 'data_type' )  
            )
    )
    ->process( $_POST )
    ->json();

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 63,891Questions: 1Answers: 10,530 Site admin

    Can you confirm what version of Editor you are using please? And also that the PHP library versions match the client-side? And finally, can you show me your Javascript initialisation for the client-side Editor?

    Sorry for all the questions :smile:

    Allan

  • map@odenterprise.orgmap@odenterprise.org Posts: 14Questions: 4Answers: 0

    Sure, Allan. It's absolutely fine. Here are all the details. Kindly let me know if you need anything else -

    DataTables Editor v1.6.1
    Current PHP version: 5.6.30

    Javascript initialisation for the client-side Editor

    var editor; // use a global for the submit and return data rendering in the examples

$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        ajax: "../php/staff.php",
        table: "#example",
        fields: [ 
            {
                label: "profile_id:",
                name: "org_profiles.profile_id"
            },  
            {
                label: "org_name:",
                name: "org_profiles.org_name"
            }, 
            {
                label: "industry_id:",
                name: "org_profiles.industry_id"
            }, 
            {
                label: "industry_other:",
                name: "org_profiles.industry_other"
            },

            {
                label: "org_description:",
                name: "org_profiles.org_description"
            }, 

            {
                label: "org_additional:",
                name: "org_profiles.org_additional"
            }, 

            {
                label: "org_confidence:",
                name: "org_profiles.org_confidence"
            }, 
            {
                label: "org_greatest_impact:",
                name: "org_profiles.org_greatest_impact"
            }, 

            {
                label: "org_greatest_impact_detail:",
                name: "org_profiles.org_greatest_impact_detail"
            }, 
            {
                label: "org_profile_category:",
                name: "org_profiles.org_profile_category"
            }, 
            {
                label: "org_profile_src:",
                name: "org_profiles.org_profile_src"
            }, 
            {
                label: "org_profile_year:",
                name: "org_profiles.org_profile_year"
            }, 
            {
                label: "org_type:",
                name: "org_profiles.org_type"
            }, 
            {
                label: "org_type_other:",
                name: "org_profiles.org_type_other"
            }, 
            {
                label: "org_url:",
                name: "org_profiles.org_url"
            }, 
            {
                label: "org_year_founded:",
                name: "org_profiles.org_year_founded"
            }, 
            {
                label: "createdAt:",
                name: "org_profiles.createdAt"
            }, 
            {
                label: "updatedAt:",
                name: "org_profiles.updatedAt"
            }, 
            {
                label: "machine_read:",
                name: "org_profiles.machine_read"
            }, 

            {
                label: "org_size:",
                name: "org_profiles.org_size"
            }, 
            {
                label: "org_profile_status:",
                name: "org_profiles.org_profile_status"
            }, 
            {
                label: "org_hq_city:",
                name: "org_profiles.location_id"
 /*               type: "select",
                placeholder: "Select a location"*/
            },
            {
                label: "org_hq_st_prov:",
                name: "org_locations.org_hq_st_prov"

            },
            {
                label: "survey_contact_email:",
                name: "org_contacts.survey_contact_email"

            },
                        {
                label: "survey_contact_first:",
                name: "org_contacts.survey_contact_first"

            },
                        {
                label: "survey_contact_last:",
                name: "org_contacts.survey_contact_last"

            },
                        {
                label: "survey_contact_phone:",
                name: "org_contacts.survey_contact_phone"

            },
                        {
                label: "survey_contact_title:",
                name: "org_contacts.survey_contact_title"

            },
                        {
                label: "org_hq_country:",
                name: "org_profiles.country_id",
               // type: "select",
                //placeholder: "Select a country"
            },

                        {
                label: "org_hq_country_income:",
                name: "org_country_info.org_hq_country_income"

            },
/*                      {
                label: "org_hq_country_income_code:",
                name: "org_country_info.org_hq_country_income_code"

            },
                        {
                label: "org_hq_country_locode:",
                name: "org_country_info.org_hq_country_locode"

            },*/
                        {
                label: "org_hq_country_region:",
                name: "org_country_info.org_hq_country_region"

            },
/*                      {
                label: "org_hq_country_region_code:",
                name: "org_country_info.org_hq_country_region_code"

            },
                        {
                label: "ISO2:",
                name: "org_country_info.ISO2"

            },*/
                                    {
                label: "c_lat:",
                name: "org_country_info.c_lat"

            },
                                    {
                label: "c_lon:",
                name: "org_country_info.c_lon"

            },
            {
                "label": "data_type:",
                "name": "org_data_use[].object_id",
                //"type": "select"
            },
            {
                label: "advocacy:",
                name: "data_applications.advocacy"

            },
            {
                label: "advocacy_desc:",
                name: "data_applications.advocacy_desc"

            },
            {
                label: "org_opt:",
                name: "data_applications.org_opt"

            },
            {
                label: "org_opt_desc:",
                name: "data_applications.org_opt_desc"

            },
            {
                label: "use_other:",
                name: "data_applications.use_other"

            },
            {
                label: "use_other_desc:",
                name: "data_applications.use_other_desc"

            },
            {
                label: "prod_srvc:",
                name: "data_applications.prod_srvc"

            },
            {
                label: "prod_srvc_desc:",
                name: "data_applications.prod_srvc_desc"

            },
            {
                label: "research:",
                name: "data_applications.research"

            },
            {
                label: "research_desc:",
                name: "data_applications.research_desc"

            }
        ]
    } );


    // Setup - add a text input to each footer cell
   //  $('#example tfoot th').each( function () {
     $('#example tfoot th:nth-child(35)').each( function () {
        var title = $(this).text();
        //console.log("this", $('#example tfoot th'));
        $(this).html( '<input type="text" placeholder="Search '+title+'" />' );
    } );


    // Activate an inline edit on click of a table cell
    $('#example').on( 'click', 'tbody td:not(:first-child, :nth-child(2))', function (e) {
        editor.inline( this , {
            submit: 'allIfChanged'  //Added by Vinayak
        }
        );
    } );
  • map@odenterprise.orgmap@odenterprise.org Posts: 14Questions: 4Answers: 0

    I used this post (http://byjm.datatables.net/forums/discussion/40454/editor-1-6-1-system-error-on-delete) by you to make the changes mentioned in the post, The Error now has changed to this -

    SQLSTATE[42S22]: Column not found: 1054 Unknown column 'org_profiles.profile_id' in 'where clause'

    and staff.php is returned this json -

    {fieldErrors: [],…}
    cancelled:[]
    data:[]
    error:"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'org_profiles.profile_id' in 'where clause'"
    fieldErrors:[]
    ipOpts:[]

  • allanallan Posts: 63,891Questions: 1Answers: 10,530 Site admin

    Could you update to Editor 1.6.2, both the client-side and server-side libraries please. There were a couple of fixes in this area which should help.

    Thanks,
    Allan

  • map@odenterprise.orgmap@odenterprise.org Posts: 14Questions: 4Answers: 0

    Sure. Could you tell me which files do I need to replace to upgrade from 1.6.1 to 1.6.2 ?

  • allanallan Posts: 63,891Questions: 1Answers: 10,530 Site admin
    Answer ✓

    The Editor Javascript file (dataTables.editor.js and its min counterpart) and all of the files in the PHP library for Editor, minus the config.php file, which you should keep as you currently have.

    Allan

  • map@odenterprise.orgmap@odenterprise.org Posts: 14Questions: 4Answers: 0

    Brilliant. I upgraded all the files and multiple delete is now working. Thanks a ton for all your help! Really appreciate it! :)

This discussion has been closed.

DataTables

DataTables designed and created by SpryMedia Ltd.
© 2007-2023 MIT licensed. Privacy policy. Supporters.
SpryMedia Ltd is registered in Scotland, company no. SC456502.