title: Jquery datatables - Dropdown filter for server side processing from INNER JOIN
body: <p><strong>I have modified serverside processing script with $acolumns and $bcolumns.
  This is because i have used 'LEFT OUTER JOIN' for the SQL query and 'To and from date filter' to filter one of the columns for the table. 
  For this i have modified both $sWhere and $sQuery.
  This is all working brilliantly but i cant populate the filters in the table with the values from the fields.</strong></p>
  
  <p>Any help/ guidance on this would be greatly appreciated.</p>
  
  <p><strong>Javascript:</strong></p>
  
  <hr>
  
  <pre><code>var $oTable;
  var asInitVals = new Array();
  var aSelected = [];
  
  $(document).ready(function() {
          var $oTable= $("#example").dataTable( {
          "oLanguage": {
              "sSearch": "Search:"
          },
          "sDom":"lfrtip", // default is lfrtip, where the f is the filter
          "bProcessing": true,
          "bServerSide": true,
          "bFilter": true,
          "bSearchable": true,
          "sAjaxSource": "scripts/server_processing.php",
          "aoColumns": [
              { "sName": "catname", "sTitle": "Division"} ,
              { "sName": "cname", "sTitle": "Course"} ,
              { "sName": "status", "sTitle": "Code"} ,
              { "sName": "mname","sTitle": "Module"} ,
              { "sName": "searchdateadded", "sTitle": "Created"},
              ],
          "fnRowCallback": function( nRow, aData, iDisplayIndex ) {
              if ( jQuery.inArray(aData.DT_RowId, aSelected) !== -1 ) {
                  $(nRow).addClass('row_selected');
              }
          },
          //"sDom": '&lt;"clear"fl&gt;t&lt;"clear"&gt;&lt;"bottom"ip&gt;&lt;"clear"&gt;',
          "iDisplayLength": 20,       
          "bJQueryUI": true,
          "sPaginationType": "full_numbers",
          "fnServerParams": function (aoData, fnCallback) {
                 aoData.push( { "name": "min", "value":  $('#min').val() } );
                 aoData.push( { "name": "max", "value":  $('#max').val() } );
          }
          }).columnFilter({aoColumns:[
                  { type:"select"},
                  { type:"select"},
                  { type:"select"},
                  { type:"select"},
                  { type:"select"}
                  ]}
              );  
  
  
  // Implements the jQuery UI Datepicker widget on the date controls
  $('#min, #max').daterangepicker({
      dateFormat: 'yymmdd',
       onClose: function(event) {
          $oTable.fnDraw();
      }
      //showOn: 'button', 
      //buttonImage: '../images/calendar.jpg', 
      //buttonImageOnly: true
  }).change(function() {
      $oTable.fnDraw();
  });        
  
  
  //Button to clear the filter
  $('#clear').click(function(){ 
     $('#min').val('') ;
     $('#max').val('') ;
     $oTable.fnDraw('');
  }); 
  
  /* Add event listeners to the two range filtering inputs */
  $('#min').keyup( function() { $oTable.fnDraw(); });
  $('#min').change( function() { $oTable.fnDraw(); });
  
  /* Add event listeners to the two range filtering inputs */
  $('#max').keyup( function() { $oTable.fnDraw(); });
  $('#max').change( function() { $oTable.fnDraw(); });
  
  
  $('input[type=text]').addClass("idleField");  
  
  $('input[type=text]').focus(function() {  
              $(this).removeClass("idleField").addClass("focusField");  
              if (this.value == this.defaultValue){  
              }  
              if(this.value != this.defaultValue){  
                  this.select();  
              }  
          });  
  $('input[type=text]').focusout(function() {  
              $(this).removeClass("focusField").addClass("idleField");  
  });
  
  
  /* Click event handler */
  $('#example tbody tr').live('click', function () {
          var id = this.id;
          var index = jQuery.inArray(id, aSelected);
  
          if ( index === -1 ) {
              aSelected.push( id );
          } else {
              aSelected.splice( index, 1 );
          }
  
          $(this).toggleClass('row_selected');
      } );
  } );
  </code></pre>
  
  <hr>
  
  <p><strong>My Serverside processing as follows:</strong></p>
  
  <hr>
  
  <pre><code>&lt;?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('catname','cname', 'short', 'mname', 'searchdateadded');
      $bColumns = array('cc.name','c.fullname', 'c.shortname', 'm.name', 'cm.added');
  
      /* Indexed column (used for fast and accurate table cardinality) */
      $sIndexColumn = "id";
  
      /* DB table to use */
      $sTable = "mdl_course_modules";
  
      /* Database connection information */
      $gaSql['user']       = "user";
      $gaSql['password']   = "password";
      $gaSql['db']         = "db";
      $gaSql['server']     = "localhost";
  
  
      /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
       * 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'] ) &amp;&amp; $_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&lt;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
       */
      $sWhere = "";
      if ( $_GET['sSearch'] != "" )
      {
          $sWhere = "AND (";
          for ( $i=0 ; $i&lt;count($bColumns) ; $i++ )
          {
              $sWhere .= $bColumns[$i]." LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
          }
          $sWhere = substr_replace( $sWhere, "", -3 );
          $sWhere .= ') ';
      }
  
  
  
      /* Individual column filtering */
      for ( $i=0 ; $i&lt;count($bColumns) ; $i++ )
      {
          if ( $_GET['bSearchable_'.$i] == "true" &amp;&amp; $_GET['sSearch_'.$i] != '' )
          {
              if ( $sWhere == "" )
              {
                  $sWhere = " AND ";
              }
              else
              {
                  $sWhere .= " AND ";
              }
              $sWhere .= $bColumns[$i]." LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%'";
          }
      }
  
  
      if(isset($_GET['min']) &amp;&amp; isset($_GET['max']) &amp;&amp; $_GET['min'] != '' &amp;&amp; $_GET['max'] != ''){
      $sWhere = "AND FROM_UNIXTIME(cm.added, '%Y%m%d') BETWEEN '$_GET[min]' AND '$_GET[max]'";
      }
  
  
      /*
       * SQL queries
       * Get data to display
       */
      $sQuery = "
          SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", "c.fullname AS cname, c.shortname AS short, m.name AS mname, cc.name AS catname, FROM_UNIXTIME(cm.added, '%Y - %M %D') AS dateadded, 
          FROM_UNIXTIME(cm.added, '%d %M - %Y') AS searchdateadded")."
          FROM mdl_course_modules cm 
          LEFT OUTER JOIN mdl_modules m  on cm.module=m.id 
          LEFT OUTER JOIN mdl_course c  on cm.course=c.id 
          LEFT OUTER JOIN mdl_course_categories cc  on c.category=cc.id
          WHERE c.category != 0 $sWhere
          $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" =&gt; intval($_GET['sEcho']),
          "iTotalRecords" =&gt; $iTotal,
          "iTotalDisplayRecords" =&gt; $iFilteredTotal,
          "aaData" =&gt; array()
      );
  
      while ( $aRow = mysql_fetch_array( $rResult ) )
      {
          $row = array();
          for ( $i=0 ; $i&lt;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] ];
              }
          }
          $output['aaData'][] = $row;
      }
  
      echo json_encode( $output );
  ?&gt;
  </code></pre>