strugling with multi option search.

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

By willydoit - November 19, 2018

Hi all,

I am trying to create a search filter against certain values in a table field.

I can get the following code to work but it is only allowing search against a single value. 

<form method="POST" action="<?php echo $_SERVER['PHP_SELF'] ?>">
<select name = "serviced_accommodation_facilities" > 
<option value="">Please Choose a Category</option>
<?php foreach (getListOptions('advertisers', 'serviced_accommodation_facilities') as $value => $label): ?> 
     ') as $value => $label): ?> 
     
<option value = "<?php echo $value;?>" <?php selectedIf($value, @$_REQUEST['serviced_accommodation_facilities']);?>>
    
<?php echo $label; ?></option> 
     
<?php endforeach ?> 
 
</select> 

<input type="submit" name="submit" value="Search" >
</form>

However I want to use a multi select option using bootstrap multiselect, I can get the list to display and select multiple values however when I do the search It doesnt filter anything out, it just displays all the records even when just one element is selected.

I know it must be something simple and probably immediately obvious to someone with php or mysql knowledge, neither of which I have so my efforts tend to originate in copying code from somewhere and then trying to get it to work via trial and error, only this time I am pulling my hair out and getting nowhere but seem so close.

My multi list code is shown below, if someone can advise where the issue is and why the above option works and the below one doesn't I would be extremely grateful. I am assuming it is nothing to do with the headers etc given that the above filter does work.

As always, thanks in advance for any help provided

I have noticed that the working filter uses 

<select name = "serviced_accommodation_facilities" > 

and the multi list version uses

<select  id = "serviced_accommodation_facilities" multiple="multiple"  > 

is this relevant?

		
			<!-- Multi Selection Form -->
			
			<!-- Initialize the plugin: -->
<script type="text/javascript">
    $(document).ready(function() {
        $('#serviced_accommodation_facilities').multiselect();
    });
</script>	

<form method="POST" action="<?php echo $_SERVER['PHP_SELF'] ?>">
				
				
<select  id = "serviced_accommodation_facilities" multiple="multiple"  > 	
			
<option value="" disabled selected>Required Facilities</option>

<?php foreach (getListOptions('advertisers', 'serviced_accommodation_facilities') as $value => $label): ?> 
     
<option value = "<?php echo $value;?>" <?php selectedIf($value, @$_REQUEST['serviced_accommodation_facilities']);?>>
    
<?php echo $label; ?></option> 
     
<?php endforeach ?> 
 
</select> 




<input type="submit" name="submit" value="Search" >
</form>

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