server side processing - multiple filter fields

juiceqr

My application requires multiple search fields. E.g., a date column and a client name column. Want to search by client, THEN ALSO by date, narrowing the results. Using the javascript dom filter methods, this works for small tables but begins to slow to a crawl with large tables, especially with date range filtering. I tried switching to server side processing, but I don't see any way to search multiple fields, due to the limitation of adding 'AND' keywords to the sql queries to add to the search filter. Has anyone done multiple search fields that are cumulative with server side processing ?

allan

Hi juiceqr,

Thanks for the donation. I've recently been working on updating my server-side processing script to include support for individual column filtering (sSearch_{i} parameters), and although the new section of this site which will contain it is not quite ready for publication, I've dropped you an e-mail with a link to the updated script.

Hopefully the new section of the site will be ready for publication in the next week.

Allan

montassis

Hi, I have done this. Basically what I did was create a view in the MYSQL database that contains all the data from tables that I select from, then I simply run the query like it is in the example. You could even create a temporary table and then run the filtering, but I haven't done that before. Maybe not the best solution, but a solution none the less.

[code]
/* 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 =
"firstname LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
"lastname LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
"tfirstname LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
"tlastname LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
"date_recieved LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
"orderid LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
"status LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' AND ";
}
// order_detail is a mysql view!
$sQuery = "
SELECT * FROM order_detail
WHERE $sWhere order_detail.facility_facilityid = '$facilityid'
$sOrder
$sLimit
";

[/code]

iuliandum

visit this thread. It is an example with server side processing with multiple filter

http://datatables.net/forums/comments.php?DiscussionID=1478&page=1#Comment_7568