Join Query Autocomplete

notyou61

I have a join query which provides data from two different tables. Data does appear in the grid but no filtering is done when attempting to use the search. The results message is "Showing 0 to 0 of 0 entries (filtered from NaN total entries)". The code is as follows:

Javascript

            // Actions Datagrid
            var actionTable = $('#tblActions').DataTable({
                // Grid Options
                dom: "Tfrtip",
                lengthChange: false,
                bAutoWidth: false,
                jQueryUI: true,
                bProcessing: true,
                bServerSide: true,
                // Grid Ajax
                ajax: {
                    url: "dataGridQuery.php?gridNumber=2",
                    type: 'GET',
                    dataType: 'json'
                },
                // Grid Columns
                columns: [
                    // Action ID
                    {   
                        title: "Action ID", 
                        data: "tblActions.actionID",
                        render: function (data, type, row) 
                            {   // Format Primary Key
                                return formatPrimaryKeyNumber(row.tblActions.actionID, 5, 'ACT');
                            },
                        width: "05%"                        
                    },
                    // Action Time
                    {   
                        title: "Action Time",
                        data: null,
                        render: function ( data, type, row )
                            {
                                // Format Time
                                return row.tblActions.actionTime;
                            }, 
                        width: "20%" 
                    },
                    // Action Taken
                    { title: "Action Taken", data: "tblActions.actionTaken", width: "10%" },
                    // User Name
                    {   title: "User",
                        data: null,
                        render: function ( data, type, row ){
                            // Combine the first and last names into a single table field
                            return data.tblUsers.userHonorific + ' ' + data.tblUsers.userFirstName + ' ' + data.tblUsers.userLastName;
                        }, 
                        width: "25%"
                    }
                ],
                // Grid Buttons
                tableTools: {
                    sRowSelect: "os",
                    aButtons: [
                        // Add Event Test
                        { 
                            "sExtends": "text",
                            "sButtonText": "Event Test",
                            "sToolTip": "This is a button adds a test event.", 
                            "fnClick": function ( nButton, oConfig, oFlash ) {
                                createUserActionFunction("Test Action");
                            }
                        },
                        // Add Alert Test
                        { 
                            "sExtends": "text",
                            "sButtonText": "Alert Test",
                            "sToolTip": "This is a button provides an alert.", 
                            "fnClick": function ( nButton, oConfig, oFlash ) {
                                testAlert("Test Event");
                            }
                        },
                        // Refresh Datagrid Test
                        { 
                            "sExtends": "text",
                            "sButtonText": "Refresh Test",
                            "sToolTip": "This is a button refreshes the datagrid.", 
                            "fnClick": function ( nButton, oConfig, oFlash ) {
                                actionTable.ajax.reload();
                            }
                        }
                    ]
                }
            });

PHP

        // Obtain Action Grid
        if(isset($_GET['gridNumber']) && $_GET['gridNumber']==2){ 
        //
        //$data = Editor::inst( $db, 'tblActions' )
        $data = Editor::inst( $db, 'tblActions', 'actionID' )
            ->field(
                Field::inst( 'tblActions.actionID' ),
                Field::inst( 'tblActions.actionTime' )
                    ->getFormatter( function ($val) {
                        return date( 'D, d M Y, h:m:s a', $val );
                }),
                Field::inst( 'tblActions.actionTaken' ),
                Field::inst( 'tblActions.actionUserID' ),
                Field::inst( 'tblUsers.userID' ),
                Field::inst( 'tblUsers.userHonorific' ),
                Field::inst( 'tblUsers.userFirstName' ),
                Field::inst( 'tblUsers.userLastName' )
            )
            ->leftJoin( 'tblUsers', 'tblUsers.userID', '=', 'tblActions.actionUserID' )
            //->where( $key = "tblActions.actionTimeStamp", $value = CURDATE(), $op = '=' ) //  DATE(actionTimeStamp) = CURDATE()
            ->process($_POST)
            ->data();
        //
        if ( ! isset($_POST['action']) ) {
            // Get a list of sites for the `select` list
            $data['tblUsers'] = $db
                ->selectDistinct( 'tblUsers', 'userID as value, userID as label' )
                ->fetchAll();
        }
        // Echo 
        echo json_encode( $data );
        }

Please let me know if additional information is needed to provide an answer. Thanks :)

allan

type: 'GET',

but

->process($_POST)

Is the problem there I think! You are telling DataTables to send data by GET, but telling Editor to use the data from POST. Change DataTables to POST the data and I think it should be good to go (how I wish I'd made POST the default all those years ago... :-) ).

Allan

notyou61

Thanks for the quick response. Make the change to 'POST' solved part of the problem however I still am unable to use the Autocomplete. When attempting to enter characters the following error:

<br />
<b>Fatal error</b>: Using $this when not in object context in <b>/home/content/88/10775688/html/timeclockservice/DataTables-1.10.0/extensions/Editor-1.3.0/php/Editor/Editor.php</b> on line <b>849</b><br />

I think it has to do with using the join query.

allan

Could you try updating to Editor 1.3.1 please. I think this issue should be resolve in the update. It was an error triggered only in PHP 5.3 (which I guess you are using?) as the behaviour of $this in a closure changed in PHP 5.4.

Allan

notyou61

After performing the upgrade I now receive the following:

<b>Parse error</b>: syntax error, unexpected $end in <b>/home/content/88/10775688/html/timeclockservice/DataTables-1.10.0/extensions/Editor-1.3.1/php/Editor/Editor.php</b> on line <b>1017</b><br />

notyou61

I still have the same error when attempting to use Editor-1.3.1 however am now able to use the search input field after removing "bServerSide: true,". Please let me know of possible additional configurations needed to use version 1.3.1

Thanks