Problem with datatable search

Question

Problem with datatable search

Navigation

#1 by (1 votes)

1

I am currently using the datatables plugin to dynamically list data from tables I have made for a system I am working on.

Currently I have read enough of the plugin and I have not had many problems to use it, but today I have found a big problem. Having a datatable to list people by a specific type, everything works very well, shows me the information by the type that I am indicating (only one person in this case), but what is my surprise that at the time of doing a search , by placing the letter "M", the datatable automatically finds EVERYTHING that is inside the table people that begins or contains the letter M. I have searched but I can not find how to keep that limitation that I have to show the information so that it also stays in the search.

I have the code in the following way to show the information and to perform the search:

//Ordenacion
    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 .= $aColumnas[ intval( $_GET['iSortCol_'.$i] ) ]."
                ".$_GET['sSortDir_'.$i] .", ";
            }
        }

        $sOrder = substr_replace( $sOrder, "", -2 );
        if ( $sOrder == "ORDER BY" )
        {
            $sOrder = "";
        }
    }

    //Filtracion
    $sWhere = " WHERE  s.id_tipo = 1";
    if ( $_GET['sSearch'] != "" )
    {
        $sWhere = "WHERE (";
        for ( $i=0 ; $i<count($aColumnas) ; $i++ )
        {
            $sWhere .= $aColumnas[$i]." LIKE '%".$_GET['sSearch']."%' OR ";
        }
        $sWhere = substr_replace( $sWhere, "", -3 );
        $sWhere .= ')';
    }

    // Filtrado de columna individual 
    for ( $i=0 ; $i<count($aColumnas) ; $i++ )
    {
        if ( $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
        {
            if ( $sWhere == "" )
            {
                $sWhere = "WHERE ";
            }
            else
            {
                $sWhere .= " AND ";
            }
            $sWhere .= $aColumnas[$i]." LIKE '%".$_GET['sSearch_'.$i]."%'";
        }
    }


    //Obtener datos para mostrar SQL queries

    $sQuery = "
    SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumnas))."
    FROM personas p
    INNER JOIN sesion s ON s.personas_cedula=p.cedula 
    $sWhere
    $sOrder
    $sLimit
    ";

    $rResult = $mysqli->query($sQuery);


    /* Data set length after filtering */
    $sQuery = "
    SELECT FOUND_ROWS()
    ";
    $rResultFilterTotal = $mysqli->query($sQuery);
    $aResultFilterTotal = $rResultFilterTotal->fetch_array();
    $iFilteredTotal = $aResultFilterTotal[0];

    /* Total data set length */
    $sQuery = "
    SELECT COUNT(".$sIndexColumn.")
    FROM   $sTabla
    ";
    $rResultTotal = $mysqli->query($sQuery);
    $aResultTotal = $rResultTotal->fetch_array();
    $iTotal = $aResultTotal[0];

I'm a bit worried about the search situation because other data should not be shown. Maybe it's something silly what's happening, but I really can not figure out how to solve it, that's why I'm looking for help.

mysql jquery php datatables

asked by rodrigo2324 10.08.2017 в 16:29

source

1 answer

error 400 (bad request) with jquery Variable loss in Android Studio

score 1 · Accepted Answer

Based on the comments, when you search, sweep the columns by constructing a generic condition with like for the search element stored in variable $_GET['sSearch'] , and rewrite the condition before the loop.

To maintain this condition when doing a search you must add it concatenating it to the other elements. For example:

//Filtracion
    $sWhere = " WHERE  s.id_tipo = 1";
    if ( $_GET['sSearch'] != "" )
    {
        $sWhere = "WHERE (";
        for ( $i=0 ; $i<count($aColumnas) ; $i++ )
        {
            $sWhere .= $aColumnas[$i]." LIKE '%".$_GET['sSearch']."%' OR ";
        }
        $sWhere = substr_replace( $sWhere, "", -3 );
        $sWhere .= ')';
        $sWhere .= ' AND s.id_tipo = 1 ';
    }