Filtering a list an "or" facility

Hi Claire,

thanks for that, however I have copied the code into my page but when loaded it doesn't load any records at all, I don't get any coding errors and having looked up what the "IN" parameter does the code you provided seems to be exactly what I need, the only thing I can think may be causing the issue is that in my table, "business_type" is a multi value checkbox list field so a business may have multiple values associated with business_type such as Restaurant and Public House or Restaurant and Take-Away. That said there are plenty businesses which do only have a single value such as Cafe and they are not being picked up either.

I have double checked the variable "business_type" for spelling errors and that is correct, I have double checked that it is the variable which holds the Cafe, Restaurant etc values and it is. I have double checked the spelling of the values and they are correct. My understanding is that the code supplied will add to each array any record where business_type has a value which contains either Cafe, Restaurant, Public House or Take Away Food so cannot see any issue there.

The only question which comes to mind is whether the value of business_type has to be an exact match for the filter criteria, ie only contain one value however I have run a test using the same code but choosing a different variable ie

list($business_directoryRecordscomp, $business_directoryMetaData) = getRecords(array(
'tableName' => 'business_directory',
'orderBy' => 'RAND()', 'where' => 'comp_listing = 1 and street IN ("Promenade","Allotment Walk","Wansford","Church Green")'
));

I assumed that the above would list all businesses which were on any of the above streets but that didn't produce any results either so the issue must be the code you provided or I have it located in the wrong place/order or something is conflicting with it..

If I remove the "and business_type IN ("Cafe","Restaurant","Public House","Take Away Food"" element my page loads correctly albeit it displays every business type so I have to assume the issue is being caused by something in that query but I cannot see what the issue is.

As you will see, I haven't just come back and said "Oh that doesn't work" I have spent some time trying to work out why it doesnt work and I cannot find any logical reason.

If I remove the newly added code and load the page as /listings/places_to_eat.php?business_type=Cafe it will display just the cafe's but if I try the same thing with the code in it produces nothing so it would seem that the arrays which are supposed to be created are all empty when using the added code.

I doubt that it has anything to do with the display element of the page as that works fine without the above code added, but just in case I have copied below the simplest display code I am using which is for the complimentary listings array.

<?php endforeach ?>

<?php foreach ($business_directoryRecordscomp as $record): ?>

<li class="span4">
<div class="listbox-comp">
<h4><?php echo htmlencode($record['establishment_name']) ?></h4>

<div class="caption">

<i class="fa fa-home"></i> <?php echo htmlencode($record['street']) ?>.<br>
<i class="fa fa-phone"> Telephone:</i> <?php echo htmlencode($record['telephone']) ?><br>
</p><hr>

<p><strong><em><i class="fa fa-info-circle"> <a href="../advertising/ad-purchase.html" target="_blank">Click here to upgrade this listing to a full page listing for only £1.00 per week</a>.</i></em></strong></p>

</div>
</div>

</li>

<?php endforeach ?>

If anyone can see any obvious reason why the query filter isn't working I would appreciate an explanation and a possible resolution. I have also uploaded a copy of the page in case it helps.

Thanks in advance

Hey willdoit,

Thanks for attaching your code. You're on the right track thinking the issue is that the business type is multi checkbox list.

I think you need to change the getRecord function to this:

  list($business_directoryRecordscomp, $business_directoryMetaData) = getRecords(array(
    'tableName'   => 'business_directory',
    'orderBy'     => 'RAND()',
    'where' => 'comp_listing = 1 AND (`business_type` LIKE "%Cafe%" OR `business_type` LIKE "%Restaurant%" OR `business_type` LIKE "%Public House%" OR `business_type` LIKE "%Take Away Food%")',
  ));

So a multi list field type stores the values as a tab separated string. So you need to search that string for each value as opposed to matching it. The LIKE operator will search the business type string for the matching variables.

Thanks!

Greg

Hi Greg,

that sorted the issue, thank you very much.