Searching for multiple keywords in multiple fields

14 posts by 4 authors in: Forums > CMS Builder
Last Post: December 1, 2009   (RSS)

By keith_s - November 9, 2009 - edited: November 10, 2009

I have a list viewer that shows a list of member categories for a Chamber of Commerce (http://www.bainbridgegachamber.com/members.php). Each category can then have multiple member listings, each of which contains 5 or 6 different fields. The member category page also has a search function.

I want the search function to show results if ALL of the keywords typed into the search field exist regardless of the order. I want 2 fields searched - name & notes_keyword.

Currently, unless there is an exact match of words in the search field (in the exact order), it won't return anything. For instance, I can search for "accounting services, businesses" and it returns results, but if I search for " businesses, accounting services", it returns no results.

I'm assuming I need a custom mysql query in the list viewer. How would I do this?

Re: [webgfx] Searching for multiple keywords in multiple fields

By Dave - November 11, 2009 - edited: November 11, 2009

Hi webgfx,

Try "query" instead of "keyword". Query works like google returning results that match all the keywords individually.

Here's an example link: members.php?name,notes_query=dry 5

Let me know if that works for you.
Dave Edis - Senior Developer

interactivetools.com

Re: [webgfx] Searching for multiple keywords in multiple fields

By Chris - November 11, 2009

Hi webgfx,

You can build up a custom mysql query like this:

// list of fields to search
$fieldsToSearch = array('name', 'notes_keyword');

// split form input into a list of keywords
$keywords = preg_split('/\s+/', trim( @$_REQUEST['keywords'] ));

// loop over keywords and fields, building up a where clause to find each keyword in at least one field
$where = '';
foreach ($keywords as $keyword) {
$keywordWhere = '';
foreach ($fieldsToSearch as $field) {
if ($keywordWhere) { $keywordWhere .= ' OR '; }
$keywordWhere .= "$field LIKE '%" . mysql_escape($keyword) . "%'";
}

if ($where) { $where .= ' AND '; }
$where .= "($keywordWhere)";
}

// if there's no where condition, supply a valid query which returns all results
if (!$where) { $where = '1'; }

// query database
list($records, $meta) = getRecords(array(
'tableName' => 'TABLENAME',
'where' => $where,
));


Note that the code above expects you'll rename your textfield from NAME="name,notes_keyword" to NAME="keywords". You'll also need to replace 'TABLENAME' with the name of the table you're searching.

I hope this helps! Please let me know if you have any trouble.
All the best,
Chris

Re: [chris] Searching for multiple keywords in multiple fields

By keith_s - November 13, 2009

Chris, the link method that Dave posted works (ie, http://www.bainbridgegachamber.com/membersSearch.php?name,notes_query=bruce mann, but not the method using the form submission that you posted.

For instance, one listing has the following in its "notes" field:
- Offers accounting services, businesses and individuals.

The "name" field for this listing contains the following:
Bruce D. Mann, CPA

If I search for any multiple word term in which the words are not in the exact order, then the query returns a blank result. How do I get Dave's results, but using a search form like I am using?

Again, the URL is:
http://www.bainbridgegachamber.com/membershipListingsNEW.php

The "accounting" and "Air Conditioning and Heating" categories are the best to use for testing purposes since they contain notes and have multiple listings in each category.

We're close to what I need. What do I need to do in order to get it exactly right using the form to allow for the search rather than a direct link?

Thanks,
Keith D Sellars
http://www.webgraffix.com

Re: [webgfx] Searching for multiple keywords in multiple fields

By Dave - November 16, 2009

Hi Keith,

Try replacing this:
<input type="text" name="name_keyword, notes_keyword" value="">

With this:
<input type="text" name="name,notes_query" value="">

Let me know if that works for you.

Also, have a look through the search docs if you haven't already for more tips:
http://www.interactivetools.com/docs/cmsbuilder/viewer_search.html

Hope that helps!
Dave Edis - Senior Developer

interactivetools.com

Re: [Dave] Searching for multiple keywords in multiple fields

By keith_s - November 19, 2009

THANK YOU GUYS!!! The latest suggestion worked great although I had another issue to deal with that it caused. When I inserted the new "input" name values for the form submission (as suggested), I ran into a problem with the fact that I am passing category names to the page via the URL. I corrected that issue and everything works perfectly now with the direct links as well as the search function.

[:)]
Keith D Sellars
WebGraffix Media Solutions
www.webgraffix.com

Re: [gkornbluth] Searching for multiple keywords in multiple fields

By Dave - December 1, 2009

Hi Jerry,

_query should work. What keywords are you searching for?

Note that date searches probably won't work as intended. Unless the search terms match the internal date format mysql uses.
Dave Edis - Senior Developer

interactivetools.com

Re: [Dave] Searching for multiple keywords in multiple fields

By gkornbluth - December 1, 2009

Thanks for looking at this.

Here's an example...

Try a search for neubert, you'll retrieve 3 records.

Try a search for morgan and you'll retrieve 1 record.

Try a search for neubert morgan and you get no records returned.

Jerry
The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

Re: [gkornbluth] Searching for multiple keywords in multiple fields

By Dave - December 1, 2009

Ahh, I see. That's because the search is an "AND" search. Meaning it shows results that match all the search terms (Records matching Neubert AND Morgan).

This is the same as how google works and usually intuitive for end users. CMS Builder doesn't automatically support OR searches but you could do those with some custom PHP and MySQL (in the Where). The problem with multi-word OR searches is the more words you add the more results you'll get. Where-as most users are accustom to adding more words to reduce their result count.

Hope that helps!
Dave Edis - Senior Developer

interactivetools.com