Searching by Multiple Keywords

[lukechanning]

Howdy! I'm finishing up the last touches on a very long, large OSC project. I mention the size, because there are a lot of products and the client wants to be able to search inventory via multiple keywords. 

 

On the admin categories.php, how would one setup the $search value to accept multiple keywords? For instance, searching for "aero stabi" would search for everything including "-aero-" AND everything including "-stabi-". In this instance, "aerospace stabilizer" would be an acceptable return. 

 

I've been playing with it, but no dice! I'm trying to model off the advanced search results code, but they seem to operate differently. This is just on the admin side. 

 

Any help would be great! Thanks! 

[Jack_mcs]

There's form code in the admins categories.php file that searches for whatever is entered as a whole string. So if you enter "aero stabi", only products with both words in that order would be returned. So do what you want, you have to change the code so the input is broken into single words and then change the search code to search for "Word 1" or "Word 2" and so on. Be sure to use LIKE instead of = and may and instead of or, depending upon how you want the results to be found.

[lukechanning]

Great stuff! Glad to know I'm on the right path. Working with a reference as well as the advanced search results page, I'm working with the following code (I've got some custom fields for lots, location, etc. that work independently, so don't panic if you see references):

   //change jmp050525b - add lot, model, location columns to query.
$search_keywords = '';
          if (isset($search_keywords) && (sizeof($search_keywords) > 0)) {
            $where_str .= " and (";
            for ($i=0, $n=sizeof($search_keywords); $i<$n; $i++ ) {
              switch ($search_keywords[$i]) {
                case '(':
                case ')':
                case 'and':
                case 'or':
                  $where_str .= " " . $search_keywords[$i] . " ";
                  break;
                default:
                  $keyword = tep_db_prepare_input($search_keywords[$i]);
                  $where_str .= "(pd.products_name like '%" . tep_db_input($keyword) . "%' or p.products_model like '%" . tep_db_input($keyword) . "%')";
              }
            }
            $where_str .= ")";
          }
          
          $products_count = 0;


      //BOF Add Variables
       if ( (isset($HTTP_GET_VARS['search'])) || (isset($HTTP_GET_VARS['search_lot'])) || (isset($HTTP_GET_VARS['search_part'])) || (isset($HTTP_GET_VARS['search_location'])) ) {
        $search_part = $HTTP_GET_VARS['search_part'];
        $search_location = $HTTP_GET_VARS['search_location'];
        $search_lot = $HTTP_GET_VARS['search_lot'];
        if (tep_db_input($search_lot) != '') {
          $sql = "pd.products_lot = '" . tep_db_input($search_lot) . "' "; 
        } else {
          $sql = "1=1";
          }
          //END Add Variables


      $products_query = tep_db_query("select p.products_id, pd.products_name, p.products_model, p.products_quantity, p.products_image, p.products_msrp, p.products_mmap, p.products_price, p.vendors_product_price, p.vendors_prod_comments, p.products_date_added, p.products_last_modified, p.products_date_available, p.products_status, p2c.categories_id, p.products_model, pd.products_lot, pd.products_location from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = p2c.products_id $where_str and $sql and p.products_model like '%" . tep_db_input($search_part) . "%' and pd.products_location like '%" . tep_db_input($search_location) . "%' and pd.products_name like '%" . tep_db_input($search) . "%' order by pd.products_name");
    
    } else {
      $products_query = tep_db_query("select p.products_id, pd.products_name, p.products_quantity, p.products_image, p.products_price, p.products_date_added, p.products_last_modified, p.products_date_available, p.products_status from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = p2c.products_id and p2c.categories_id = '" . (int)$current_category_id . "' order by pd.products_name");
    }
    while ($products = tep_db_fetch_array($products_query)) {
      $products_count++;
      $rows++;

Only problem is, it doesn't seem to filter at all, now! Removing my search_keywords code gets it back to working as normal, and I feel like I'm close here. Any nudges? Obviously not looking for someone to do the dirty work for me :)

[lukechanning]

Actually, I'm not sure that that's really related or not . . the more I look at it. It's repeated in advanced search results and in the version I have of an old categories.php from another developer that behaves similarly to what I want. If I'm chasing rabbits, my mistake! 

[lukechanning]

Alright, forget all of the above! I'm very happy to say that I have finally baked this one. Using some of the input from above, as well as some raw thinkin' power, here's the solution: 

 

First, find in admin/categories.php 

 

 if (isset($HTTP_GET_VARS['search'])) {
    $products_query = tep_db_query("select p.products_id, pd.products_name, p.products_quantity, p.products_image, p.products_price, p.products_date_added, p.products_last_modified, p.products_date_available, p.products_status, p2c.categories_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = p2c.products_id and pd.products_name like '%" . tep_db_input($search) . "%' order by pd.products_name");
  } else {

And replace with: 

 

if (isset($HTTP_GET_VARS['search'])) {


  //Adds Unique Search Functions for Multiple Keywords
  if (tep_db_input($search) != '') {
    $search = explode(" ",$HTTP_GET_VARS['search']);
    $bits = array();
    foreach ($search as $search) {
         $bits[] = "pd.products_name like '%" . $search . "%'";
    }
  }


    $products_query = tep_db_query("select p.products_id, pd.products_name, p.products_quantity, p.products_image, p.products_price, p.products_date_added, p.products_last_modified, p.products_date_available, p.products_status, p2c.categories_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = p2c.products_id and pd.products_name and " . implode(' AND ', $bits) . " order by pd.products_name");
  } else {
  //END Multiple Keywords

And that'll do it! Hope that helps some folks out. 

[lukechanning]

One last word: 

 

Changes are documented and added here as an add-on for further development if needed. 

 

http://addons.oscommerce.com/info/9310