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 gkornbluth - November 30, 2009 - edited: November 30, 2009

It's probably because it's late, but I can't seem to get this to work.

I want to let visitors enter more than one key word at a time into a search box and get a list of all resulting records that contain any of the search terms in any of the fields searched.

Sounds like _query idea above should work, but it doesn't. It seems to act like _keyword instead.

The search form is on a multi record list page at
http://artistsofpalmbeachcounty.org/events3.php

The code at the top of the page is<?php

require_once "/my_path/cmsAdmin/lib/viewer_functions.php";

list($current_eventsRecords, $current_eventsMetaData) = getRecords(array(
'tableName' => 'current_events',
));

?>
The search form code is <form method="get" action="<?php echo $_SERVER['PHP_SELF'] ?>">
<span class="body-text-bold">Enter as many keywords as you like to narrow your search: </span><input type="text" name="start_date,title,content,list_page_description_query" value="" size="15">
<input type="submit" name="" value="Search">
</form>

As usual, I think I'll feel really silly when I find out the answer...

But maybe not.

Thanks,

Jerry Kornbluth
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

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