Search using a comma separated list of items?

LenWright

Hi,
great looking product! I'm having fun learning about this and I'm exploring the available options.

Question about Search: Is it possible to search on a collection of items?

for example, I'm building a report that lists thousands of employees. I'd like to allow the user to search and filter the report's results for say, 3 employees. So, if the user enters: "Jane Smith, Bob Brown, Michael Scott".

I would like the records to be filtered to show 3 rows; one for each of the names in the collection.

From a SQL query point of view, I would write this like: Where EmployeeName in ("Jane Smith, Bob Brown, Michael Scott")


Is that possible?

GregP

It's definitely possible; it may require a bit of finagling.

The existing search field will trigger a new request on each keyup. I would suggest implementing your own search box with a 'search now' button. At that point in time, you can grab the contents of the search box, and either pass it to the server (and let the server do the tokenization) or split the comma-seprated list in Javascript before sending it along.

Without sitting down and hammering out some code, I don't have anything more concrete to give you; for example, if there's a way to put this value into sSearch before the request is fired, or if you'd just pass it along as a new parameter using fnServerParams. But in general, you can do it.

Greg

LenWright

Thanks, Greg. Appreciate your comments. I'll give it a go

fbas

here's an easier way to do it

datatables already supports regular expression matching, so use the "|" instead of comma (or convert commas into "|" for your users when fnFilter is called)

http://datatables.net/examples/api/regex.html

GregP

Awesome. "Easier" is always better. I didn't know about the DT regex!

fbas

IMPORTANT TO NOTE THAT: reg ex filtering will work "out of the box" for client-side data.

for server side processing, you'll need to modify your server_processing.php to detect bRegex_i and use RLIKE/REGEXP operator instead of LIKE.

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

[code]
{
if (isset($_GET['sSortable_'.$i]) && $_GET['sSortable_'.$i] == "true") {
if (isset($_GET['bRegex_'.$i]) && $_GET['bRegex_'.$i] == "true")
$sWhere .= $aColumns[$i]." REGEXP '".mysql_real_escape_string( $_GET['sSearch'] )."' OR ";
else
$sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
}
}
[/code]

LenWright

Thanks fbas, I was able to get this work, exactly how I wanted it. Here is my code, if others are interested:

HTML for the input text box and a button:
[code]
Filter:


[/code]

Javascript:

[code]$('#BtnComplexSearch').click(function() {
$('#dt_example').dataTable().fnFilter(
$("#ComplexSearch").val() //get the value from the text input box
, 0 //search the first column
, true //enable Regex
, false //disable Smart
, false //disable Global search box update
);
})
[/code]