Server-Side multiple column filtering

gordyr

Hi,

Due to my inexperience with PHP I am struggling to get my server side processing set up the way I need it. Everything works beautifully for me when sorting the data client side, unfortunately I will likely need to filter datasets of at least 20,000 records.

Basically I am using a drag and drop interface to filter a column. Items are dragged into a box which then triggers fnFilter to a specific string which is obtained from the draggable object. Several objects can be dragged in to the box so therefore my search needs to be able to handle multiple words in any order. This all works wonderfully when fnFilter is set to use the global search using the following server side code:-

[code]
$sWhere = "";
if ( $_GET['sSearch'] != "" )
{
$sWhere = "(";
$aWords = preg_split('/\s+/', $_GET['sSearch']);
for ( $j=0 ; $j

fbas

individual column filter search string is sent in a parameter named $sSearch_{$i} where $i is the column number (datatables column)

another part of your code will need to check if $sSearch_0, $sSearch_1, etc is set (and is not an empty string), then add your search terms to the $sQuery.

see http://www.datatables.net/usage/server-side
see http://www.datatables.net/release-datatables/examples/server_side/server_side.html (code on "/* Individual column filtering */")

______

you could simplify your query a little if you're using MySQL by using the RLIKE (REGEXP) operator instead of concatenating a series of OR clauses with LIKE.

[code]
$sWhere = "";
if ( $_GET['sSearch'] != "" )
{
$sWhere = "(";
$aWords = preg_split('/\s+/', $_GET['sSearch']);
$sWords = join('|', $aWords); // '|' is Reg Expression for "OR"

$sWhere .= "(";
for ( $i=0 ; $i

gordyr

That's fantastic help fbas... Thank you.

I should get a chance to give it a go in about an hours time and be sure to let you know the outcome.

Cheers!

gordyr

I actually couldn't get your code example to work i'm afraid FBAS.... However I did miscopy my original example code for you, which you probably worked on.

My current working code for my general search is:-

[code]
$sWhere = "";
if ( $_GET['sSearch'] != "" )
{
$aWords = preg_split('/\s+/', $_GET['sSearch']);
$sWhere = "WHERE (";

for ( $j=0 ; $j

fbas

as mentioned, individual column searching is done separately than the global searching. the standard server_processing.php script has some code that loops through $sSearch_{$i} for $i = 0 to number of columns -1.

it looks very similar to the global search and operates similarly. obviously though it only uses one column's field name rather than looping through all field names when applying your $aWords

here's a selection from the ORIGINAL script. you'll need to modify it to use your $aWords splitting code
[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'] != "" ) // this is the global filter
{
$sWhere = "WHERE (";
for ( $i=0 ; $i