DataTables

With that large of a dataset, the only reasonable option in my opinion is to use Server-Side Processing.

http://datatables.net/usage/server-side

It will allow you to send your server application pagination information, meaning you could retrieve only as many records as you need to keep visible. The search field will send search terms to the server, allowing the filtering to happen in the server, not just the records stored on the client side.

Using server-side processing, your UI will be responsive and searching/filtering will cover your hundreds of thousands of records.

Keep in mind, the sample code reflects how you might implement it, but you're not limited in any way. As long as the server can read in the variables from the POST/GET, and return JSON (or other valid data), you're golden.

I'm clearly missing something then. With server-side processing enabled, you are handing off the filtering to the server side. There should be no reason you'd be doing the search on the subset of 2000 records on the client side, and I'm not sure you even COULD, which leads me to believe there's an issue with the server-side code.

My best guess (and it's ONLY a guess) is that you're 'paginating' on the SERVER side before doing the search. The server-side code is first saying, "Here are my 2000 records, now let's filter by the parameter I found in the sSearch variable." Whereas the logic should be "Let's filter by the sSearch variable; there are more than 2000 records, so let's paginate them."

Here's the new backend code:

<?php
  /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
   * 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)
  */

require_once "libs/Core.php";
$db = new Database;
$id = $_SESSION['user_id'];

/*
    <td align="center"><input type="checkbox" name="delete[]" value="{$inv.inventory_id}"></td>
    <td><a href="account.php?mode=edit&id={$inv.inventory_id}">{$inv.inventory_part_number|stripslashes}</a></td>
    <td>{$inv.inventory_alt_part_number|stripslashes}</td>
    <td>{$inv.inventory_condition_code|stripslashes}</td>
    <td>{$inv.inventory_quantity|stripslashes}</td>
    <td>{$inv.inventory_description|stripslashes}</td>
    <td>{$inv.last_update|date_format}</td>
*/
  $aColumns = array('i.inventory_id', 'i.inventory_part_number', 'i.inventory_alt_part_number', 'i.inventory_condition_code', 
                    'i.inventory_quantity', 'i.inventory_description', 'i.last_update');
    /* 
   * Paging
  */
  $sLimit = "";
  if ( isset( $_GET['iDisplayStart'] ) && $_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<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 = "WHERE i.user_id = ".$id;
  if ( $_GET['sSearch'] != "" ) {
    $sWhere = " AND (";
    for ( $i=0 ; $i<count($aColumns) ; $i++ ) {
      $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
    }
    $sWhere = substr_replace( $sWhere, "", -3 );
    $sWhere .= ')';
  }
  
  /* Individual column filtering */
  for ( $i=0 ; $i<count($aColumns) ; $i++ ) {
    if ( $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' ) {
      if ( $sWhere == "" ) {
        $sWhere = "WHERE ";
      } else {
        $sWhere .= " AND ";
      }
      $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%' ";
    }
  }
  
  
  /*
   * SQL queries
   * Get data to display
   */
  $sQuery = "SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
             FROM linv_inventory i 
             $sWhere
             $sOrder
             $sLimit";
  $rResult = $db->query($sQuery) or die(mysql_error());
  
  /* Data set length after filtering */
  $sQuery = "SELECT FOUND_ROWS()";
  $rResultFilterTotal = $db->query($sQuery) or die(mysql_error());
  $aResultFilterTotal = $db->fetchArray($rResultFilterTotal);
  $iFilteredTotal = $aResultFilterTotal[0];
  
  /* Total data set length */
  $sQuery = "SELECT COUNT(inventory_id) FROM linv_inventory";
  $rResultTotal = $db->query($sQuery) or die(mysql_error());
  $aResultTotal = $db->fetchArray($rResultTotal);
  $iTotal = $aResultTotal[0];
  
  /* Output */
  $output = array(
    "sEcho" => intval($_GET['sEcho']),
    "iTotalRecords" => $iTotal,
    "iTotalDisplayRecords" => $iFilteredTotal,
    "aaData" => array()
  );

  while ( $aRow = $db->fetchArray( $rResult ) ) {
    $row = array();
    foreach($aColumns as $name=> $value) {
      $row[] = $aRow[$name];
    }
    $output['aaData'][] = $row;
  }
  
  echo json_encode( $output );
?>

Hard to say. Looks like the table in the 'proper' screenshot is using jQuery UI, and the second one isn't. That doesn't mean the option isn't enabled, but it might mean that it's not finding the CSS related to the jQuery UI styles.

As for the checkboxes, that's probably not a CSS issue, but I can't say for sure what it is. In my own application, to replace pure data with visual elements and controls, I'm using a combination of fnRowCallback (to implement controls on individual rows) and fnDrawCallback (to add controls to the page or table as a whole after it has been drawn).

Can't speak intelligently to the templating engine you're using, but I do see a commented area in your code where there's a reference to checkboxes. Is it intentionally commented out? (ie. does the templating engine grab the markup from inside the comments?)

Added a bit to my previous comment about the commented-out markup. I don't know Smarty. :(

The only reason for the styles to not work is that the stylesheet isn't found or the classes aren't present. Firebug should help you see if any of your styles have somehow gone missing or gotten mismatched. Could be as simple as missing a wrapper class that used to be there...?

At a loss. Not sure if the new script in 1.7.5 appends different classes, or different wrapper classes, etc. There HAS to be a mismatch between the table and the CSS file, though, either in names, specificity, or otherwise. Sorry I've been a pathetic (zero) amount of help. ;-)

Ok, got some of it. I figure out whay it wasn't looking correctly.
Forgot to put the bJQueryUI parameter in the init call.

Now, if I can only get the checkboxes back. Would you use fnRowCallback or fnDrawCallback to put them there is every row required one?

Ok, got it worked out like this:

"aoColumnDefs": [{ "sClass": "center", "aTargets": [ 0 ] }],

And added a td.center selector in my CSS.