Negative Except Regex Filter with Database?

aaronh

Hey everyone,
I'm attempting to get a functioning negative regex filter working with my table that pulls data from a mySQL database.
I've attempted to get the script from here: http://datatables.net/forums/discussion/4345/negative-exclude-except-search-example/p1 working, but out of the box I have found that regex doesn't seem to work at all with external databases. Though I've seen some regex scripts made to work with external sources, I haven't been able to find anything about getting something like this working.

Does anyone have some advice or pointers for getting me going in the right direction to get this working?

fbas

unless your database supports regexp (I don't know if any do) you have to make do without it.

your server side script can look for patterns in the search strings, like the negative operator, and put together your SQL WHERE clause appropriately.

CORRECTION: looks like MySQL has REGEXP functionality: http://www.go4expert.com/forums/showthread.php?t=2337

aaronh

Thanks fbas-
Do you maybe have some guidance as to how I could pass the command to my server side script to detect it? The SQL string should be no problem, I'm just not sure how the fnFilter function passes data to it.

Edit:
Thanks for the update clarifying regexp functionality, but it still doesn't help that I don't know how data is passed to the server side script. I would greatly appreciate any help with that!

fbas

when using server side processing, fnFilter adds parameters to the aoData array that is sent to the server during fnServerData

see http://datatables.net/ref#fnServerData or http://datatables.net/usage/server-side, and Allan's sample server side code at http://datatables.net/release-datatables/examples/server_side/server_side.html

for column-specific filter, a parameter named sSearch_{i} will be set, where {i} is the column index. if you have set reg exp in the fnFilter, bRegex_{i} should be set "true"

if you detect bRegex_{i} and sSearch_{i} are set, you can add to the WHERE clause of your SQL using the REGEXP operator in MySQL. [note: I'm not testing this code. I'll leave debugging to you]
[code]
for ( $i=0 ; $i

fbas

and the "!" operator doesn't seem supported. you still might need to get creative, parse the search string, and use " NOT REGEXP '.substring(mysql_real_escape_string($_GET['sSearch_'.$i]), _____)."' "

aaronh

After some work, I've come up with my own sort of solution for this that doesn't involve REGEX at all. I've updated the server side script to check for search strings starting with !, which will see them as an exclude. With this addition, you can also use commas to separate different strings to either include in the search, or exclude.

The code is still pretty rough at the moment, but it functions just fine. I'll update the post when I get it cleaned up more.

[code]/* Individual column filtering */
for ( $i=0 ; $i