Server-Side Processing and Regex Search / Filter
Server-Side Processing and Regex Search / Filter
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. =)
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. =)
This discussion has been closed.
Replies
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
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.
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
[code]
$sWhere = "";
if ( $_GET['sSearch'] != "" )
{
$sWhere = "WHERE (";
for ( $i=0 ; $i
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
- 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.
Regards,
Allan
Allan
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.
Can you give me any help on this? I donated the other day by way of thanks.
Thanks!
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
[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
[code]
$sWhere = "";
$sWhere = "WHERE (";
for ( $i=0 ; $i $value)
{
$aWords .= ''.$value.',';
}
$aWords = explode(',',$aWords,10);
$sWhere .= "(";
for ( $j=0 ; $j
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
[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.
[code]
$sWhere = "WHERE (";
for ( $i=0 ; $i $value)
{
$array .= ''.$value.',';
}
$aWords = explode(',',$array,20);
$sWhere .= "(";
for ( $j=0 ; $j
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
[code]
/* General search */
$sWhere = "";
if ( $_GET['sSearch'] != "" )
{
$aWords = preg_split('/\s+/', $_GET['sSearch']);
$sWhere = "WHERE (";
for ( $j=0 ; $j
[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
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
Thanks fbas -- this is an excellent set of tips.
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
[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]
{
$sWhere = " id_trunk !='-1' ";
}
else
{
$sWhere = " id_trunk ='".$idd."' ";
}
if ( $_GET['sSearch'] != "" )
{
$sWhere = "(";
$aWords = preg_split('/\s+/', $_GET['sSearch']);
for ( $j=0 ; $j