help req with basic filter please

5 posts by 2 authors in: Forums > CMS Builder
Last Post: August 1, 2018   (RSS)

By willydoit - July 31, 2018

Hi all,

I am wanting to produce an array filtered on two different field values ("listing" and "services_provided")  but am unsure of the syntax required to produce the result.  

I have produced my lists based on a single criteria

'where' => 'listing= "Featured Listing"',

but I also want to filter the results to only include records where "services_provided" includes only certain words such as "Accommodation" or "Holiday Park".

As records within "Services_provided" include "self catering accommodation" and "serviced accommodation" "Holiday Park" or "Caravan Park" as examples I believe I need to use the LIKE as opposed to = to filter the results .

What I need to do is create the array where;

"listing" equals "Featured Listing" AND "services provided" includes any of the following words "accommodation", "caravan","park"

Any help with the actual syntax required to produce the above would be greatly appreciated.

Thanks in advance for any help provided

By daniel - July 31, 2018

Hi willydoit,

The most basic way to achieve this would be to use something like:

'where' => 'listing = "Featured Listing" AND (services_provided LIKE "%accomodation%" OR services_provided LIKE "%caravan%" OR services_provided LIKE "%park%")',

A more condensed method is:

'where' => 'listing = "Featured Listing" AND services_provided REGEXP "accomodation|caravan|park"',

Please note that the second method requires MySQL 5.6+ which is above the minimum requirements of CMSB.

Let me know if you have any further questions!

Thanks,

Daniel
Technical Lead
interactivetools.com

By daniel - July 31, 2018 - edited: July 31, 2018

Hi willydoit,

Yes, this should be possible. However, instead of passing in the service values directly through the URL, I'd suggest instead passing a "key" through the URL and using that to determine the services to display. It's a little bit more setup but is overall much more flexible and secure. Assuming a URL like list.php?services_key=food, first add this code above your array (modify to suit your needs):

$whereString = 'listing = "Featured Listing"';

if( @$_GET['services_key'] == 'food' ) {
  $whereString .= ' AND (services_provided LIKE "%catering%" OR services_provided LIKE "%restaurant%")';
} elseif( @$_GET['services_key'] == 'example_key' ) {
  $whereString .= ' AND (services_provided LIKE "%exampleService1%" OR services_provided LIKE "%exampleService2%")';
} elseif( ... ) {
  ...
}

You can continue to add additional "elseif" sections for each service key that you want to add, and you can add more LIKE conditions to include as many services as necessary for each key.

Next, update the array to use: 

'where' => $whereString,

Let me know if the above works for your needs, or if you have any further questions!

Thanks,

Daniel
Technical Lead
interactivetools.com

By willydoit - August 1, 2018

Thanks Daniel,

That is all very helpful.