mySQL join Table Query + Datatable

jadhoo

Dear All,
Would appreciate if you could help me on this.
Refernce Ex: ( http://www.datatables.net/release-datatables/examples/server_side/server_side.html )

I am using datatable to display my server side data , only difference from the example code is that , example datatable uses only 1 database table, but in my case multiple mysql joined table. my datatable is being populated correctly for more than 10K records, but the issue i am having is with the Search, when I search , its trigger error saying that "DataTables warning: table id=example1 - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1" .

my client side code :-

$(document).ready(function() {
$('#example1').dataTable({
"bJQueryUI": true,
"bDeferRender": true,
"aLengthMenu": [[5, 10, 25, 50, 100], [5, 10, 25, 50, 100]],
"iDisplayLength": 10,
"sPaginationType": "full_numbers",
"aaSorting": [[ 1, "asc" ]] ,
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "/locdelegationac2015/ajax/locac2015" ,
"bSort" : true,
"aoColumns" : [
{"bSortable": false, "bSearchable": false},
{"bSearchable": false},
{"bSearchable": false},
{"bSortable": true, "sClass": "cell-actions", "bSearchable": false}
] ,
"oLanguage": {
"oPaginate": {
"sNext": ' NEXT ',
"sLast": ' LAST ',
"sFirst": ' FIRST ',
"sPrevious": ' PREVIOUS '
}
}
});
} );

my server side php code (customized from the example page mentioned above) :-

Note:- 1) The main difference from the example code & mine is that , i have placed 'WHERE' outside if ( isset($_GET['sSearch']) && strlen($_GET['sSearch']) > 0 ), is that could be the cause of datatable error ??

2) I have validated the returned json value based on the initial sql query and its found to be valid. but i cant get or print the sql or json values after i do a datatable search to check for any error.

$sWhere = " WHERE CO.ID = LM.Nationality
AND LM.ID = LGM.MemberID
AND E.ID = LG.EventID
AND LG.ID = LGM.GroupID
AND E.ID= 853 ";

        if ( isset($_GET['sSearch'])  && strlen($_GET['sSearch']) > 0 )
        {
            $sWhere .= " AND (";

            for ( $i=0 ; $i<count($aColumns) ; $i++ )
            {
                if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" )
                {
                    $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
                }
            }
            $sWhere = substr_replace( $sWhere, "", -3 );
            $sWhere .= ')';
        }

//-----------

$query1 = "SELECT
LM.ID AS ID,
LM.PassportName as PassportName,
LM.OtherName as OtherName,
LM.Sex as Sex
........
FROM
Event E, .....
LEFT JOIN Country as COO on (COO.ID=LM.OrganizationCountry),
Country CO {$sWhere} {$sOrder} {$sLimit} " ;
$dataArray1 = $db->fetchAll($query1); //---------------------------------------------------------------------------------------------
$query2 = "SELECT COUNT(LGM.ID) AS 'Cnt'
FROM
Event E.... LEFT JOIN
Country as COO on (COO.ID=LM.OrganizationCountry),
Country CO WHERE CO.ID = LM.Nationality
AND LM.ID = LGM.MemberID ... " ;

                $dataArray2 = $db->fetchAll($query2); 
                $iCnt = $dataArray2[0]['Cnt'];  
                //---------------------------------------------------------------------------------------------
                if (!isset($_GET['sEcho']))
                    $_GET['sEcho'] = 0;

                $output = array(
                    'sEcho' => intval($_GET['sEcho']),
                    'iTotalRecords' => count($dataArray1),
                    'iTotalDisplayRecords' => $iCnt,
                    'aaData' => array()
                );
                //---------------------------------------------------------------------------------------------
                foreach ($dataArray1 as $iID => $aInfo) {  
                    $aItem = array( 
                        $aInfo['PassportName'], $aInfo['OtherName'], $aInfo['Sex'], $aInfo['DOB'], 'DT_RowId' => $aInfo['ID']
                    );
                    $output['aaData'][] = $aItem;
                } //--------------------------------------------------------------------------------------------- 
            echo json_encode($output);

aperez21

Hello that such!

I share a possible solution to your problem

http://datatables.net/forums/discussion/21811/solution-serverside-using-join-pdo-php

regards