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