Trouble filtering server-side data

joshgateway · April 2014

Hi guys,

So I got server-side processing working and doing all I need it to do in my application with on exception, entering any text into the filter/search box crashes it. sProcessing is triggered and never goes away and nothing happens. I have my code below...any ideas on what could be causing this? (I have 'pushed extra data to the sender' which is all working well and giving me the desired result)

Initialization:

Server Side (PHP) code:

[code]
<?php

$aColumns = array('id', ' ', ' ', 'fname', 'lname', 'email_address', 'username', 'type_id', 'phone', 'profile_image',
'address1', 'address2', 'country', 'state', 'city', 'zipcode',
'shipping_address1', 'shipping_address2', 'shipping_country', 'shipping_state', 'shipping_city', 'shipping_zipcode');

/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "id";

/* DB table to use */
$sTable = "users";

/* Database connection information */
$gaSql['user'] = "gateway";
$gaSql['password'] = "picard!picard!";
$gaSql['db'] = "solomon-beta";
$gaSql['server'] = "us-west-2-mysql-vpc-instance1.c6ukgbmvu5lk.us-west-2.rds.amazonaws.com";

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP server-side, there is
* no need to edit below this line
*/

/*
* MySQL connection
*/
$gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password'] ) or
die( 'Could not open connection to server' );

mysql_select_db( $gaSql['db'], $gaSql['link'] ) or
die( 'Could not select database '. $gaSql['db'] );

/*
* Paging
*/
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ".mysql_real_escape_string( $_GET['iDisplayStart'] ).", ".
mysql_real_escape_string( $_GET['iDisplayLength'] );
}

/*
* Ordering
*/
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i array()
);

// ChromePHP::log('whatttaaadfasfd');

while ( $aRow = mysql_fetch_array( $rResult ) )
{
$row = array();
for ( $i=0 ; $i

[/code]

THanks again for any help.

joshgateway · April 2014

My Initialization code

[code]
$(document).ready(function() {
var remove_perm = '<?= $admin_permissions ?>';
console.log('remove_perm: '+remove_perm);
if (remove_perm == 1) {
var aiex = [0];
} else {
var aiex = [0,2];
}

oTable = $('#test_main_list').dataTable( {
"sDom": 'C<"clearfix"f<"fll"l>>rtp',
"oColVis": {
"iOverlayFade": 200,
"aiExclude": aiex,
},
"bProcessing": true,
"bServerSide": true,
"sScrollY" : "405px",
"sScrollX" : "100%",
"bPaginate": false,
"sAjaxSource": ROOT + '_ajax/ss_get_users.php',
"aoColumns": [
/* id */ {"bVisible": false, "bSortable": false },
/* pic */ {"bSortable": false},
/* pic */ {"bVisible": false, "bSortable": false},
/* fname */ null,
/* lname */ null,
/* email */ null,
/* uname */ {"bVisible": false},
/* type */ null,
/* phone */ {"bVisible": false},
/* img */ {"bVisible": false, "bSortable": false},
/*Billing Address*/ {"bVisible": false },
/*Billing Address2*/ {"bVisible": false },
/*Billing City*/ {"bVisible": false },
/*Billing State*/ {"bVisible": false },
/*Billing Zip*/ {"bVisible": false },
/*Billing Country*/ {"bVisible": false },
/*Shipping Address*/ {"bVisible": false },
/*Shipping Address2*/{"bVisible": false },
/*Shipping City*/ {"bVisible": false },
/*Shipping State*/ {"bVisible": false },
/*Shipping Zip*/ {"bVisible": false },
/*Shipping Country*/ {"bVisible": false }

],
"fnServerData": function ( sSource, aoData, fnCallback ) {
/* Add some extra data to the sender */
aoData.push( { "name": "company_id", "value": "<?= $company_id ?>" } );
aoData.push( { "name": "term_admin", "value": "<?= $db->base_company->get_term('company') ?> admin" } );
aoData.push( { "name": "term_dep_admin", "value": "<?= $db->base_company->get_term('department admin') ?>" } );
aoData.push( { "name": "term_instructor", "value": "<?= $db->base_company->get_term('instructor') ?>" } );
aoData.push( { "name": "term_student", "value": "<?= $db->base_company->get_term('student') ?>" } );
$.getJSON( sSource, aoData, function (json) {
fnCallback(json)
} );
}
});
} );
[/code]

joshgateway · April 2014

Also, column filtering works fine

joshgateway · May 2014

Anyone have any ideas :(

allan · May 2014

Can you link to a test case please.

Allan

joshgateway · May 2014

Hi Allan, can I PM you a login?

allan · May 2014

Sure - click my forum user name and then the Message button.

Allan

joshgateway · May 2014


<?php
    


    /*
     * Script:    DataTables server-side script for PHP and MySQL
     * Copyright: 2010 - Allan Jardine
     * License:   GPL v2 or BSD (3-point)
     */
    
    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * Easy set variables
     */
    
    /* Array of database columns which should be read and sent back to DataTables. Use a space where
     * you want to insert a non-database field (for example a counter or static image)
     */
    $aColumns = array('id', ' ', ' ', 'fname', 'lname', 'email_address', 'username', 'type_id', 'phone', 'profile_image',
        'address1', 'address2', 'country', 'state', 'city', 'zipcode', 
        'shipping_address1', 'shipping_address2', 'shipping_country', 'shipping_state', 'shipping_city', 'shipping_zipcode');
    
    
    /* Indexed column (used for fast and accurate table cardinality) */
    $sIndexColumn = "id";
    
    /* DB table to use */
    $sTable = "users";
    
    /* Database connection information */
    $gaSql['user']       = "gateway";
    $gaSql['password']   = "picard!picard!";
    $gaSql['db']         = "solomon-beta";
    $gaSql['server']     = "us-west-2-mysql-vpc-instance1.c6ukgbmvu5lk.us-west-2.rds.amazonaws.com";
    
    
    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * If you just want to use the basic configuration for DataTables with PHP server-side, there is
     * no need to edit below this line
     */
    
    /* 
     * MySQL connection
     */
    $gaSql['link'] =  mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password']  ) or
        die( 'Could not open connection to server' );
    
    mysql_select_db( $gaSql['db'], $gaSql['link'] ) or 
        die( 'Could not select database '. $gaSql['db'] );
    
    
    /* 
     * Paging
     */
    $sLimit = "";
    if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
    {
        $sLimit = "LIMIT ".mysql_real_escape_string( $_GET['iDisplayStart'] ).", ".
            mysql_real_escape_string( $_GET['iDisplayLength'] );
    }
    
    
    /*
     * Ordering
     */
    if ( isset( $_GET['iSortCol_0'] ) )
    {
        $sOrder = "ORDER BY  ";
        for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ )
        {
            if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
            {
                $sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
                    ".mysql_real_escape_string( $_GET['sSortDir_'.$i] ) .", ";
            }
        }
        
        $sOrder = substr_replace( $sOrder, "", -2 );
        if ( $sOrder == "ORDER BY" )
        {
            $sOrder = "";
        }
    }
    
    
    /* 
     * Filtering
     * NOTE this does not match the built-in DataTables filtering which does it
     * word by word on any field. It's possible to do here, but concerned about efficiency
     * on very large tables, and MySQL's regex functionality is very limited
     */
    
    $company_id = $_GET['company_id'];
    $sWhere = "";
    $sAnd = "";
    if ( $_GET['sSearch'] != "" ) {
        
        $aWords = preg_split('/\s+/', $_GET['sSearch']);
        $sWhere = "WHERE (";
         
        for ( $j=0 ; $j<count($aWords) ; $j++ )
        {
            if ( $aWords[$j] != "" )
            {
                $sWhere .= "(";
                for ( $i=0 ; $i<count($aColumns) ; $i++ )
                {
                    $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string( $aWords[$j] )."%' OR ";
                }
                $sWhere = substr_replace( $sWhere, "", -3 );
                $sWhere .= ") AND ";
            }
        }
        $sWhere = substr_replace( $sWhere, "", -4 );
        $sWhere .= ')';

        $sAND .= ' AND company_id = '.$company_id;
    } else {
        $sAND = 'WHERE company_id = '.$company_id;
    }
    /* Individual column filtering */
    for ( $i=0 ; $i<count($aColumns) ; $i++ )
    {
        if ( $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
        {
            if ( $sWhere == "" )
            {
                $sWhere = "WHERE ";
            }
            else
            {
                $sWhere .= " AND ";
            }
            $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%' ";
        }
    }
    
    
    /*
     * SQL queries
     * Get data to display
     */
   
    $sQuery = "
        SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
        FROM   $sTable
        $sWhere
        $sAND
        $sOrder
        $sLimit
    ";

    $rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
    
    /* Data set length after filtering */
    $sQuery = "
        SELECT FOUND_ROWS()
    ";
    $rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
    $aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
    $iFilteredTotal = $aResultFilterTotal[0];
    
    /* Total data set length */
    $sQuery = "
        SELECT COUNT(".$sIndexColumn.")
        FROM   $sTable
    ";
    $rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
    $aResultTotal = mysql_fetch_array($rResultTotal);
    $iTotal = $aResultTotal[0];
    

    
    /*
     * Output
     */
    $output = array(
        "sEcho" => intval($_GET['sEcho']),
        "iTotalRecords" => $iTotal,
        "iTotalDisplayRecords" => $iFilteredTotal,
        "aaData" => array()
    );
    

    while ( $aRow = mysql_fetch_array( $rResult ) )
    {
        $row = array();
        for ( $i=0 ; $i<count($aColumns) ; $i++ )
        {
            if ( $aColumns[$i] == "version" )
            {
                /* Special output formatting for 'version' column */
                $row[] = ($aRow[ $aColumns[$i] ]=="0") ? '-' : $aRow[ $aColumns[$i] ];

            }
            else if ( $aColumns[$i] != ' ' )
            {
                /* General output */
                $row[] = $aRow[ $aColumns[$i] ];
                /* get user type */
                if ($row[7] == '1') {
                    $row[7] = $_GET['term_admin'];
                } elseif ($row[7] == '2') {
                   $row[7] = $_GET['term_admin'];
                } elseif($row[7] == '3') {
                    $row[7] = $_GET['term_dep_admin'];
                } elseif($row[7] == '6') {
                    $row[7] = $_GET['term_instructor'];
                } elseif($row[7] == '5') {
                    $row[7] = $_GET['term_student'];
                } 
                /* additional row element */
                if ($row[9]) {
                    $row[1] = '<div id="mini_prof_pic"><img src="../users/bio_image/'.$row[9].'"><a class="user-detail-display detail_link" id="user-id-'.$row[0].'"></a></div>';
                } else {
                    $row[1] = '<div id="mini_prof_pic"><img src="../_images/new/icon_no_prof_img.png"><a class="user-detail-display detail_link" id="user-id-'.$row[0].'"></a></div>';
                }

                $row[2]='<a href="/admin/user_list?delete='.$row[0].'" title="'.$row[3].' '.$row[4].'" class="a_is_button button_DG delete-record">Remove</a>';
                // $row[21] = 'fun';
            }

        }
        $output['aaData'][] = $row;
    }
    echo json_encode( $output );
    
?>

Trouble filtering server-side data

Trouble filtering server-side data

Replies

Howdy, Stranger!

Categories

DataTables

Trouble filtering server-side data

Trouble filtering server-side data

Replies

Howdy, Stranger!

Quick Links

Categories

DataTables