enable search and filtering on server-side datatable using foreign keys names not id's

adam558

Hi guys,
i am using server-side datatable, i have 2 foreign keys, i am showing not id's but name in other table. But when i make search and enter names it doesn't work, i want to enable search only in showed attributes. Thanks

```
<?php
//include connection file
include_once("connection.php");

// initilize all variable
$params = $columns = $totalRecords = $data = array();

$params = $_REQUEST;


    $columns = array( 'id_doc','ref','taille','date_ajout' , 'type', 'nature', 'date_doc', 'date_echeance', 'date_notif', 'date_modif');

$where = $sqlTot = $sqlRec = "";

// check search value exist
if( !empty($params['search']['value']) ) {   
    $where .=" WHERE ";
    $where .=" ( id_doc LIKE '%".mysql_real_escape_string($params['search']['value'])."%' ";
    $where .=" OR date_ajout LIKE '%".mysql_real_escape_string($params['search']['value'])."%' ";
    $where .=" OR type LIKE '%".mysql_real_escape_string($params['search']['value'])."%' ";
    $where .=" OR nature LIKE '%".mysql_real_escape_string($params['search']['value'])."%' ";
    $where .=" OR date_doc LIKE '%".mysql_real_escape_string($params['search']['value'])."%' ";
    $where .=" OR date_echeance LIKE '%".mysql_real_escape_string($params['search']['value'])."%' ";
    $where .=" OR date_notif LIKE '%".mysql_real_escape_string($params['search']['value'])."%' ) ";

}

// getting total number records without any search
$sql = "SELECT * FROM `document` ";
$sqlTot .= $sql;
$sqlRec .= $sql;
//concatenate search sql if value exist
if(isset($where) && $where != '') {

    $sqlTot .= $where;
    $sqlRec .= $where;
}


$sqlRec .=  " ORDER BY ". $columns[$params['order'][0]['column']]."   ".$params['order'][0]['dir']."  LIMIT ".$params['start']." ,".$params['length']." ";

$queryTot = mysqli_query($conn, $sqlTot) or die("database error:". mysqli_error($conn));


$totalRecords = mysqli_num_rows($queryTot);

$queryRecords = mysqli_query($conn, $sqlRec) or die("erreur en affichant l historique");

//iterate on results row and create new index array of data

include 'connect.php';
while ( $aRow = mysqli_fetch_array( $queryRecords ) ){

$row = array();
        for ( $i=0 ; $i<count($columns) ; $i++ )
    {
        if ( $columns[$i] == "type" )
        {   
            /* Special output formatting for 'version' column */
            $comp=$aRow[ $columns[$i] ];

                    $sql="SELECT * FROM document_type where id_type='$comp'";
                    $req= mysql_query ($sql) ;
                    $datay= mysql_fetch_array($req);
                    $val = $datay['nom'];

                    $row[] = $val;

        } 
         else if ( $columns[$i] != ' ' )
        {
            /* General output */
            $row[] = $aRow[ $columns[$i] ];
        }

    }
 //$output['data'][] = $row;
    $data[] = $row;
}



$json_data = array(
        "draw"            => intval( $params['draw'] ),   
        "recordsTotal"    => intval( $totalRecords ),  
        "recordsFiltered" => intval($totalRecords),
        "data"            => $data   // total data array
        );

echo json_encode($json_data);  // send data as json format

<?php > ``` ?>

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

[And joined two threads together as both stuck in spam filter]

allan

Without question this is a difficulty with server-side processing. Indeed we don't even have a solution for it in our Editor libraries yet.

The only way I'm aware of this bring possible when server-side processing is used is to get the one-to-many joined data in an array as part of the original query. There might be some database specific way to do that depending on what you are using, but I think a common table expression is probably the way to go. With that you can built the one-to-many join together with the main query and then apply a WHERE condition into the array data.

I don't have any example code of that at this time. It is something I plan on tackling later this year though.

Allan

adam558

thanks, i am using formatter to show the data i want, but how to force search on shown data not id's @allan ??

adam558

any answer @allan