strugling with multi option search.

6 posts by 2 authors in: Forums > CMS Builder
Last Post: December 4, 2018   (RSS)

By Dave - November 26, 2018

Hi willydoit, 

You can do multi-value searches by adding [] after the fieldname.  See "format[]" option here: https://www.interactivetools.com/docs/cmsbuilder/viewer_search.html

So if you have viewer.php?title_keyword[]=red&title_keyword[]=blue that would return records that have a title that contains either red or blue.

I'm not sure exactly how that bootstrap component submits values but you could try adding [] after the fieldname.  

Hope that helps, let me know any other questions!

Dave Edis - Senior Developer
interactivetools.com

By willydoit - November 28, 2018

Hi Dave,

thanks for your help but the solution doesn't help unfortunately, perhaps if I explain further.

I am not looking to provide a filtered list where "serviced_accommodation_facilities" contains selection1 OR selection2 OR selection3

I need to produce a list where the values contained within "serviced_accommodation_facilities" contains selection1 AND selection2 AND selection3 etc

Unfortunately my sql and php knowledge are extremely limited so I may be making a simple mistake somewhere.

I believe the bootstrap multi-option code is simply to present the list of option in a way that allows multiple selections to be made from the list and doesn't incorporate anything to do with the interrogation of the table to provide the results.

It may be that the best way to resolve the issue is to forget my coding attempts and start from scratch.

I think the help I need is in how to code the options that have been selected so that they can be compared against the content of each records "serviced_accommodation_facilities" field and only show the records where "serviced_accommodation_facilities" contains checks against ALL the items in the list.

Any help or pointer in the right direction is appreciated

By Dave - November 28, 2018

Hi willydoit, 

Ok, sure.  Here's a few tips and pointers.

The default viewers and getRecords() function don't support multiple "AND" searches on a single field.  So you'll need to write some code PHP/MySQL code to get it to work.  If you're using both the automatic search features of getRecords and this custom code, you'll want to make sure your search field does NOT match an existing column name.  That way it won't trigger the automatic searching.

Another point, if "serviced_accommodation_facilities" is a multivalue field then the values will be stored in a list seprated by tabs (\t).  So you'll need to do a MySQL LIKE search and account for the tabs.

Next, if you add the following option to getRecords() it will show you the MYSQL code it's generating for easy debugging: 'debugSql' => 1,

Here's some sample code that might do the job.  It assumes your search field is named "facility[]": 

  // Create WHERE condition - add to getRecords with: 'where' => $where
  // Example input:  viewer.php?facility[]=aaa&facility[]=bbb
  // Example output: (serviced_accommodation_facilities LIKE '%\taaa\t%' AND serviced_accommodation_facilities LIKE '%\tbbb\t%')
  $where = '';
  if (isset($_REQUEST['facility']) && is_array($_REQUEST['facility'])) { // check for ?facility[]=value
    foreach ($_REQUEST['facility'] as $value) {
      $escapedValue = mysql_escape($value); 
      if ($where) { $where .= " AND "; }
      $where .= "serviced_accommodation_facilities LIKE '%\\t$escapedValue\\t%'";
    }
    if ($where) { $where = "($where)"; } // group AND conditions so they don't interfere with other statements
  }

We're getting into a bit of custom programming, but hopefully this helps! 

Dave Edis - Senior Developer
interactivetools.com

By willydoit - November 30, 2018

Thanks Dave, but I havent a clue what you have done :-(  as I am not a programmer I may need you to sort this for me, is it likely to be a big job? I will contact you next week with more details if that's okay.

By Dave - December 4, 2018

Sure thing, feel free to email me directly at dave@interactivetools.com.  I don't think what you need is a very big job at all.

Dave Edis - Senior Developer
interactivetools.com