Server-Side Processing and Regex Search / Filter

Julan

Unfortunately I didn't see that regex searching was not possible with server-side processing.

I had everything installed and working and then as soon as I typed in more than 1 word I got "no results", it doesn't even search for multiple words that are in the same string!

So I need a solution really... whether that means multiple boxes for each column or just being able to search a string with spaces from one column... This has got to work.

I have experimented a little with my server_processing.php, and was able to connect each column into a single string in one column, but the search filter still doesn't find anything if I put in more than 1 word.

I'm using an additional WHERE clause in the MySQL string, connected with a $_GET... this is for different tables to different things (easier that way than having dozens of datatables installations).

Also note the following comment in the example server_processing.php :
[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

*/
[/code]

Particularly "it's possible to do here"... please explain how. My datatables are pretty big, some with over 20,000 rows, so it's particularly important for filtering to work with more than just 1 word.

Thanks. =)

allan

Hi Julan,

Having the table match for a single column is relatively straight forward and you can do something like this: http://datatables.net/examples/api/multi_filter.html (the default server-side processing script supports individual column sorting). However, given that the script is doing a match like this "%search%" it will only match a substring. For example if you have the string "apples pears oranges" then search for "apples oranges" it will not match (since it's not a substring). "apples pears" on the other hand would match.

So there are a couple of ways to get around this - the first and easiest is to simply break the input search string into words (split on white space), then simply do the search on each column with this broken string. It might result in a fairly large WHERE part of the query if there is many words being searched for, but you could say that it is most likely only two or three words will be searched on in the table. While not a great solution, it's a lot more attractive than option two...

So option two is to replicate the kind of regex searching that DataTables uses on the client-side. The way this works is to construct a string of all the columns in a single row (in MySQL use the concat function to get the full string), and then runs a rexgex expressions which uses lookahead - which as far as I can tell from a quick scan of the docs MySQL doesn't support. I suspect that it will be possible in some manner, but I'm not an expert in MySQL usage, so I'm not sure what it would be :-( A MySQL list might be able to suggest an option.

As such, option 1 is what I personally would go with :-). Implementing something like the filter that is in the demo script with a explode()ed string looping over to create the required WHERE will do the trick.

Regards,
Allan

Julan

Hi Allan, can you show me an example of option 1? I did look into option 2 but as you've correctly pointed out, MySQL will not support it.

I'm using a custom WHERE string at the moment; I basically have a "?$id" string in the < head> of my main page that calls the script, and a "$_GET['id']" in the server-processing.php, this way I am able to just click on a link within my page (eg. "index.php?id=2") and then all the categories from category 2 are showing up in the table... there's other ways to make this work but I think this is best for me.

So I wonder, if I use option 1 to explode the input string from the filter box, will this affect the additional WHERE that I have on my server_processing.php?

I would really like to see a server-side DataTables in action with multiple search words... or even multiple column searching... this would make things a lot easier and more understandable, not just for me, but for anyone looking to do the same thing in the future.

I'd be happy to make another donation if you can get this working for us.

Julan

Example on what I was saying before...

from server_processing.php:
[code]
if ($_GET['id']) {
$catid = $_GET['id'];
}
else {
$catid = 1;
}
/* etc etc */
$sWhere = "WHERE catid='$catid'";
if ( $_GET['sSearch'] != "" )
{
$sWhere = "WHERE (";
for ( $i=0 ; $i:
[code]
"sAjaxSource": "server_processing.php?id=<?php echo $catid; ?>",
[/code]

allan

Just working on an example for what I was on about - will get back to you hopefully shortly :-)

Allan

allan

Here is how multiple word matching could be implemented in the PHP script. Replace:

[code]
$sWhere = "";
if ( $_GET['sSearch'] != "" )
{
$sWhere = "WHERE (";
for ( $i=0 ; $i

fnigl

Hi Allan
Thanks for your extreme excellent work. Your recent script (above) works amazing - and my server-side datatable works amazing now.
Keep up the good work :-)

/fnigl

Julan

Hi Allan, sorry it took so long for me to reply. I actually did a backflip after I tried this out and found that it worked. So I'm not very good at backflips and I actually landed on my head... spent the night in hospital.

- Lol. Just kidding, of course. =)

But I could be doing backflips; this is absolutely brilliant. I'll talk to my boss to make another donation; if he doesn't want to, I will make another one myself at the end of the month.

I really appreciate your efforts; thank you so much. Take care.

allan

lol. Excellent to hear that this does the job for you. I'll look at integrating it into the standard server-side processing script as I have no doubt others will benefit from this as well. I'll profile it to see what the performance impact is, but it's a nice addition!

Regards,
Allan

jimbob72

This is an excellent addition. Is there any way to apply this to the same individual columns - so for example, I can search Column 1 for word A OR word B?

allan

Where it says "$sWhere .= ") AND ";" - I think if you just change that to an OR that will do it.

Allan

jimbob72

Many thanks - that's brilliant for the general search box, but I'm trying to apply that to one individual column. If, for example, I want to search just Column 1 for word A OR word B, how can I use multiple string values e.g. sSearch_0=wordA&sSearch_0=wordB and include them both in the results? I could use the general search, but then wordA might appear in a different column and would be incorrectly included in the results.

I've tried exploding into an array and using IN instead of LIKE but it doesn't seem to work. Appreciate any help/guidance you can give.

jimbob72

Hi Allan

Can you give me any help on this? I donated the other day by way of thanks.

Thanks!

allan

Hi jimbob72,

Thanks for the donation - if you want an answer to a specific question it's best to use the 'support' option since it allows me to keep a track of what I should be answering! :-)

I presume you are using fnFilter to do the column specific filtering? What needs to be done is something a bit like above (which is done for the global filter) for each of the individual columns that you want to do the multiple option filtering on - i.e. pass in a space separated string into fnFilter, then explode it in PHP and build up the filter that you want.

I think the above code looks almost perfect for that, but rather than "$aWords = preg_split('/\s+/', $_GET['sSearch']);" you'd want sSearch_0 etc for whatever columns you have (I suppose it might be an idea to rip it out into a function to make life easier and less repetitive code, although you could give it a go for just the first column to get it working.

Regards,
Allan

jimbob72

I'm not using fnFilter - though I probably should be. The filtering bit of my code is:

[code]"sAjaxSource": "../resources/php/dbtable_parse2.php",
"fnServerData": function ( sSource, aoData, fnCallback ) {
$.getJSON( sSource, aoData.concat( $("#filterform").serializeArray() ), function (json) {
fnCallback(json)
} );
}
[/code]

This is pretty messy and I'm sure that fnFilter would be better. I'll adjust and see if it works. I'll try the sSearch[i] version of your code and see if that functions too - i'm sure it will.

Many thanks
James

Ps.Will use support in future.

allan

What you've done should be fine actually - it's basically the same end result as fnFilter - fnFilter is really just a wrapper function to fnDraw when using server-side processing - so as long as you know what parameters to expect on the server-side, and then process them accordingly, it is absolutely fine.

Allan

jimbob72

I've got this far - which is working fine for one column, but not multiple columns. Something is clearly wrong with my PHP logic.

[code]
$sWhere = "";
$sWhere = "WHERE (";

for ( $i=0 ; $i $value)
{
$aWords .= ''.$value.',';
}
$aWords = explode(',',$aWords,10);

$sWhere .= "(";

for ( $j=0 ; $j

allan

Hi James,

That looks very close to me - nice one! You currently have an AND at line 24 and I'm wondering if you actually want an OR there? Or perhaps this is the behaviour you want? What I would suggest is echoing out the built up SQL statement and if you could post that back here that might tell us what is going on.

Thanks,
Allan

jimbob72

Good idea. This is what I get if I choose two values from three separate columns.

[code]
WHERE ((type LIKE '%OEIC%' OR type LIKE '%ETF%' ) AND (theme LIKE '%ArrayWater%' OR theme LIKE '%Global Ethical%' ) AND (region LIKE '%ArrayUK%' OR region LIKE '%Global%' ))
[/code]

The query looks the right format, but 'Array' is creeping in to some of the values after the first column. This could be because $aWords is returning 'Array' and appending it to the actual value.

Ps. I need 'AND' as I want the results to be conditional on multiple column values.

jimbob72

Sorted. This now works for using checkboxes to filter multiple individual columns.
[code]
$sWhere = "WHERE (";

for ( $i=0 ; $i $value)
{
$array .= ''.$value.',';
}

$aWords = explode(',',$array,20);

$sWhere .= "(";

for ( $j=0 ; $j

allan

Very nice!

So with the first issue - can you just copy the original code of mine above, and insert that before your column specific filter, then concatenate the two together? Just do a check on if $where == "" or not to see if you need to add the WHERE clause.

For the second point, I'm not quite sure I follow I'm afraid, but can you add OR IS NULL to the condition?

Allan

jimbob72

Had to tinker a little to get it to work with either general or column filter or both.
[code]
/* General search */
$sWhere = "";
if ( $_GET['sSearch'] != "" )
{
$aWords = preg_split('/\s+/', $_GET['sSearch']);
$sWhere = "WHERE (";
for ( $j=0 ; $j

allan

A bit of an update to this. I wasn't very successful in getting the code above to work, but I've modified it a little to produce correct SQL for the WHERE part of the query. This might be useful to anyone else using this:

[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 = "(";
$aWords = preg_split('/\s+/', $_GET['sSearch']);
for ( $j=0 ; $j

fbas

on the original topic:

to use reg expressions with server side with MySQL, you can detect $bRegex_i and use RLIKE/REGEXP in place of LIKE

I touch on this in the blog: http://tote-magote.blogspot.com/2011/08/serverprocessingphp-for-datatables.html

mvillavicencio

Excellent!!! I was looking for this some weeks ago! I probed this and it works!!! Thank you Allan for your extraordinary support!!

matbeard

In case anyone else is looking for a solution to server-side regex filtering -- fbas' solution in his blog is by far the cleanest and easiest. No need to mess with preg_split when you can use the regular expression directly with MySQL's REGEXP.

Thanks fbas -- this is an excellent set of tips.

raymon90

Hi all,
I'm new in using datatables. I have my table to be set up using server-side processing. Then I create an input box to filter only one column which should have regex capability. Then I stumbled to the blog fbas posted. Tried to set up my server processing code using the code on the blog but the regex filtering is still not working.

Here's my Individual column filter code:
[code]
/* Individual column filtering */
for ( $i=0 ; $i

solver

How to modyfy mysqli php code (http://datatables.net/development/server-side/php_mysqli) to work as above Allan's code?

mmonsalvo

Solver I hope this example will work in MySQLi:
[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
*/

$iColumnCount = count($aColumns);

if ( isset($input['sSearch']) && $input['sSearch'] != "" ) {
$aFilteringRules = array();

$aWords = preg_split('/\s+/', $input['sSearch']);
for ( $j=0 ; $jreal_escape_string( $aWords[$j] )."%'";
}
}

}
}

if (!empty($aFilteringRules)) {
$aFilteringRules = array('('.implode(" OR ", $aFilteringRules).')');
}
}


// Individual column filtering

for ( $i=0 ; $i<$iColumnCount ; $i++ ) {
if ( $_GET['sSearch_'.$i] != "" )
{
$aWords = preg_split('/\s+/', $_GET['sSearch_'.$i]);
for ( $j=0 ; $jreal_escape_string($aWords[$j])."%'";
}
}
}
}
}

if (!empty($aFilteringRules)) {
$sWhere = "WHERE ".implode(" AND ", $aFilteringRules);
} else {
$sWhere = "";
}
[/code]

anilmcmt

allan your search filtring code working lots of thanks ..........

anilmcmt

if($idd == '')
{
$sWhere = " id_trunk !='-1' ";
}
else
{
$sWhere = " id_trunk ='".$idd."' ";
}



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