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,
Technical Lead
interactivetools.com
By willydoit - July 31, 2018
thanks Daniel,
That's just what I needed, is there also a way to use the LIKE when loading a page from a link similar to \list.php?services_provided=cafe so that rather than the criteria needing to be an exact match it just needs to include the word cafe and is it also possible to also use the AND or OR elements.
I ask as I am assuming that this would allow me to create menu links to pull out certain records on the fly as opposed to having to filter the listing contents within the array in the header.
if this is possible it would allow me to use a single listings page to generate whatever listings were needed from the table rather than having to create multiple listing pages with predetermined array content. I know I can do it when matching a content with = but the field concerned can hold multiple values, for example a food establishment may have multiple services_provided values such as catering , restaurant, public house etc and I need to ensure that a places to eat listings page includes all possibilities .
Hope that makes sense 😀
Thanks again for the help.
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,
Technical Lead
interactivetools.com