the column search is not working

szd

i'm using the datatable search column with server side PHP / MYSQL
what should i add to the code
because it is not working

boicenet

In order to better help you can you post the code you already have? Someone may immediately notice the error or missing code and be able to help you more quickly.

szd

thank you for your suggestion, below is my code

PHP code - server side

$columns = array(
   // 0 => 'Number',
    0 =>'Full_name',
    1 =>'applicantemail',
    2 =>'applocationdate',
    3 =>'appcareer'
);
$sql = "SELECT CONCAT(firstname ,' ', middlename , ' ' ,lastname) as Full_name , applicantemail,  applocationdate ,careerscatname FROM tbluserapplicant , tblapplicants , tblcatcareers where tblcatcareers.careerscatid = tblapplicants.careerscatid and tbluserapplicant.userapplicant=  tblapplicants.mainuserid   ";
$query=mysqli_query($conn, $sql) or die(mysqli_error($conn));
$totalData = mysqli_num_rows($query);
$totalFiltered = $totalData;  





$sql = "SELECT CONCAT(firstname ,' ', middlename , ' ' ,lastname) as Full_name , applicantemail,  applocationdate  , careerscatname  ";
$sql.=" from tbluserapplicant , tblapplicants , tblcatcareers where tblcatcareers.careerscatid = tblapplicants.careerscatid and tbluserapplicant.userapplicant =  tblapplicants.mainuserid  ";
if( !empty($requestData['search']['value']) ) {
    $sql.=" and ( applicantemail like '".$requestData['search']['value']."%'";
    $sql.=" or  careerscatname like '".$requestData['search']['value']."%'";  
    $sql.=" or firstname  LIKE '".$requestData['search']['value']."%'";
    $sql.=" or middlename  LIKE '".$requestData['search']['value']."%'";
    $sql.=" or lastname  LIKE '".$requestData['search']['value']."%'";
    $sql.=" or  applocationdate LIKE '".$requestData['search']['value']."%')";
    }
    
$query=mysqli_query($conn, $sql) or die(mysqli_error($conn));
$totalFiltered = mysqli_num_rows($query); 
    
$sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]."   ".$requestData['order'][0]['dir']."  ";
$sql.=" LIMIT ".$requestData['start']." ,".$requestData['length']."   ";
$query=mysqli_query($conn, $sql) or  die(mysqli_error($conn));

$data = array();
while( $row=mysqli_fetch_array($query) ) {  
    $nestedData=array(); 
    //$nestedData[] = $row["i"];
    $nestedData[] = $row["Full_name"];
    $nestedData[] = $row["applicantemail"];
    $nestedData[] = $row["applocationdate"];
    $nestedData[] = $row["careerscatname"];
    
    $data[] = $nestedData;
}


$json_data = array(
            "draw"            => intval( $requestData['draw'] ),
            "recordsTotal"    => intval( $totalData ),  // total number of records
            "recordsFiltered" => intval( $totalFiltered ),
            "data"            => $data   // total data array
            );
    
echo json_encode($json_data);  // send data as json format

break;

---

javascript:

$(document).ready(function() {
    $('#example tfoot th').each( function () {
        var title = $(this).text();
        $(this).html( '<input type="text" placeholder="Search '+title+'" />' );
    } );
 
    // DataTable
    var table = $('#example').DataTable(
    
    {
            "processing": true,
             "serverSide": true,
            
         "ajax":{
                url :"mysql_api.php?actn=read", // json datasource
                type: "GET",  // method  , by default get
                  
                error:  function(){  // error handling
                   $(".example-error").html("");
                   $("#example").append('<tbody class="example-error"><tr><th colspan="3">**No data found in the server**</th></tr></tbody>');
                   $("#example_processing").css("display","none");
                }
                
           },
dom : 'lBfrtip',
buttons: [
           'copy', 'csv', 'excel', 'pdf', 'print' ]

     } );       
   
} );

and i used the files below:

 <script type="text/javascript" language="javascript" src="js/jquery-1.12.3.js"></script>
    <script type="text/javascript" language="javascript" src="js/jquery.dataTables.min.js"></script>
    <script type="text/javascript" language="javascript" src="js/dataTables.buttons.min.js"></script>
    <script type="text/javascript" language="javascript" src="js/buttons.flash.min.js"></script>
    <script type="text/javascript" language="javascript" src="js/jszip.min.js"></script>
    <script type="text/javascript" language="javascript" src="js/pdfmake.min.js"></script>
    <script type="text/javascript" language="javascript" src="js/vfs_fonts.js"></script>
    <script type="text/javascript" language="javascript" src="js/buttons.html5.js"></script>
    <script type="text/javascript" language="javascript" src="js/buttons.print.js"></script>
    
    <link rel="stylesheet" type="text/css" href="css/jquery.dataTables.min.css">
    <link rel="stylesheet" type="text/css" href="css/buttons.dataTables.min.css">     

please note that general search is working properly

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

allan

Is it the column search that isn't working? I don't see any column search code in your PHP. The documentation for the parameters sent to the server by DataTables is available here.

Allan

szd

this isn't enough

if( !empty($requestData['search']['value']) ) {
    $sql.=" and ( applicantemail like '".$requestData['search']['value']."%'";
    $sql.=" or  careerscatname like '".$requestData['search']['value']."%'"; 
    $sql.=" or firstname  LIKE '".$requestData['search']['value']."%'";
    $sql.=" or middlename  LIKE '".$requestData['search']['value']."%'";
    $sql.=" or lastname  LIKE '".$requestData['search']['value']."%'";
    $sql.=" or  applocationdate LIKE '".$requestData['search']['value']."%')";
    }

if i remove the php and use the html alone it worked so i know i have to add something in my code but couldn't figure out what it is..

allan

If you want to use column searching, you need to make use of the columns[i][search][value] values that are submitted by the client-side.

Do you not have PDO available on your server? If you did, you could just use the demo ssp.class.php file that is part of the DataTables package.

Allan