Sijeveux 0 Newbie Poster

Hi there,

I've been struggling for several days now with datatables, and I don't know how to get my individual columns being searched thanks to their each own input. When I type something in my inputs nothing happends, no filtering is done.
Many thanks if yo can take that thorn out of my side, it's very painful !!!!!!!

Here's my PHP

<?php

     //connect to database
     include '../pdo_connect.php';


    $aColumns = array( 'job_id', 'job_intitule', 'job_contrat', 'job_date_insertion', 'cp', 'ville_nom');

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

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

    //Join to use
   $sJoin = ' JOIN marimmo_villes v ON marinterim_job_offers.job_ville = v.ville_id    ';
  // $sJoin .= ' JOIN Employees e ON RMA.Employee_ID  = e.Employee_ID';


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


    /*
     * Ordering
     */
    $sOrder = "";
    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] ) ]."
                 ".($_GET['sSortDir_'.$i]==='asc' ? 'asc' : 'desc') .", ";
               }
        }

        $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
     */
    $sWhere = "";
    if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" )
    {
        $sWhere = "WHERE (";
        for ( $i=0 ; $i<count($aColumns) ; $i++ )
        {
            if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" )
            {
                $sWhere .= $aColumns[$i]." LIKE '%".  htmlspecialchars( $_GET['sSearch'] )."%' OR ";
            }
        }
        $sWhere = substr_replace( $sWhere, "", -3 );
        $sWhere .= ')';
    }

    /* Individual column filtering */
    for ( $i=0 ; $i<count($aColumns) ; $i++ )
    {
        if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
        {
            if ( $sWhere == "" )
            {
                $sWhere = "WHERE ";
            }
            else
            {
                $sWhere .= " AND ";
            }
            $sWhere .= $aColumns[$i]." LIKE '%".  $_GET['sSearch_'.$i]."%' ";
        }

    }


    /*
     * SQL queries
     * Get data to display
     */
    $sQuery = "
        SELECT SQL_CALC_FOUND_ROWS " . str_replace(" , ", " ", implode(", ", $aColumns)) . "
        FROM   $sTable
        $sJoin
        $sWhere
        $sOrder
        $sLimit
    ";
try {   $rResult = $marInterim->query($sQuery);
    } catch (PDOException $e) {
    $error = 'Error getting data: ' . $e->getMessage();
    echo $error;
    exit();
}

/* Data set length after filtering */
    $sQuery = "
        SELECT FOUND_ROWS()
    ";
    try{
    $rResultFilterTotal = $marInterim->query($sQuery);
    $aResultFilterTotal = $rResultFilterTotal->fetch();
    $iFilteredTotal = $aResultFilterTotal[0];
    }catch (PDOException $e) {
    $error = 'Error getting found rows: ' . $e->getMessage();
    echo $error;
    exit();
}
    /* Total data set length */
    $sQuery = "
        SELECT COUNT(".$sIndexColumn.")
        FROM   $sTable
    ";
    $rResultTotal = $marInterim->query($sQuery);
    $aResultTotal = $rResultTotal->fetch();
    $iTotal = $aResultTotal[0];


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

    while ( $aRow =  $rResult->fetch())
    {
        $row = array();
        for ( $i=0 ; $i<count($aColumns) ; $i++ )
        { 
             if (!empty($aColumns[$i]) )
            {
                /* General output */
                $row[] = $aRow[ $aColumns[$i] ];
            }
        }
        $output['aaData'][] = $row;
    }

    echo json_encode($output);

And my js

// JavaScript Document

$(document).ready(function() {


  oTable =  $('#datos').dataTable({
     "sAjaxSource": 'pff/new.php',  
    bProcessing: true,
    bServerSide: true,
    "aaSorting": [[ 3, "desc" ]],
    "lengthMenu": [[15, 25, 50, -1], [15, 25, 50, "Afficher tout"]],

    "aoColumns": [
                    {"bSearchable":false}, // job_id
                    {"bSearchable":true}, // job_intitulé
                    {"bSearchable":true}, // job_contrat
                    {  // job_date_insertion
                       "bSearchable":true,
                      "aTargets": [ 3 ], "aaSorting": [[ 3, "desc" ]],
                       "mRender": function(date, type, full) {return moment(new Date()).format('DD-MM-YYYY'); }                  
                    }, 
                   { // job_cp + ville
                     "bSearchable":true,
                     "aTargets": [ 4 ] ,  
                      "mData": 4 ,  
                      "mRender" : function ( data, type, full ) 
                                 {  var texte=  data+' '+full[5];
                                    var sReturn = full[0];
                                    return "<a href='../offredemploi.php?id="+sReturn+"' target='_Blank'>"+ texte + "</a>"; }
                   },
                   null, // job_date_descriptif
                   { // VOIR
                     "bsortable" : false,
                     "ordering": false,
                     "mRender": function(data,type) 
                                 { var sReturn = data;
                                   return "<a href='../offredemploi.php?id="+sReturn+"' target='_Blank'> Voir </a>";
                                 }  , "mData": null,
                                "sDefaultContent": " ",                
                   }
                  ],



        "spaginationType" :  "full_numbers",
        language: {
                    processing:     "Traitement en cours...",
                    search:         "Rechercher&nbsp;:",
                    lengthMenu:    "Afficher _MENU_ &eacute;l&eacute;ments",
                    info:           "Affichage de l'&eacute;lement _START_ &agrave; _END_ sur _TOTAL_ &eacute;l&eacute;ments",
                    infoEmpty:      "Affichage de l'&eacute;lement 0 &agrave; 0 sur 0 &eacute;l&eacute;ments",
                    infoFiltered:   "(filtr&eacute; de _MAX_ &eacute;l&eacute;ments au total)",
                    infoPostFix:    "",
                    loadingRecords: "Chargement en cours...",
                    zeroRecords:    "Aucun &eacute;l&eacute;ment &agrave; afficher",
                    emptyTable:     "Aucune donnée disponible ",
                    paginate: {
                                first:      "Premier",
                                previous:   "Pr&eacute;c&eacute;dent",
                                next:       "Suivant",
                                last:       "Dernier"
                            }

                }
    });

    //var oTable = $('#datos').dataTable();

    oTable.fnSetColumnVis( 5, false );
    oTable.fnSort( [ [3,'desc'] ] );
    $('thead th:last-child').unbind(); 
    /**************/
    $('#datos tfoot th').not(":eq(0),:eq(3),:eq(5),:eq(6)") //Exclude columns 
                         .each( function ()
                                            {
                                            var title = $('#datos tfoot th').eq( $(this).index() ).text(); 
                                            $(this).html( '<input type="text" placeholder="Rechercher '+title+'" />' );
                                           });

       var oTable = $('#datos').dataTable();


       oTable.columns().eq( 0 ).each( function ( colIdx ) {
        if (colIdx == 0 || colIdx == 3 || colIdx == 5 || colIdx == 6) return; //Do not add event handlers for these columns

         $( 'input', oTable.column( colIdx ).footer() ).on( 'keyup  change', function () {  oTable
                                                                                              .column( colIdx )
                                                                                              .search( this.value )
                                                                                              .draw();
                                                                                            });
         });  

});