List Records by Additional Selection

5 posts by 3 authors in: Forums > CMS Builder
Last Post: November 17, 2016   (RSS)

By mark99 - November 10, 2016

At present I've written a short PHP script that just outputs several products (records) from a specific category and then orders them by price, which starts with the following code:

<?php
  /* STEP 1: LOAD RECORDS - Copy this PHP code block near the TOP of your page */
  require_once "/*****/isp_list/system/lib/viewer_functions.php";

  list($isp_listRecords, $isp_listMetaData) = getRecords(array(
    'tableName'   => 'isp_list',
    'limit'     => '4',
    'loadCreatedBy' => false,
    'where'       => " category LIKE '%Superfast Broadband%' ",
    'orderBy'       => 'sfbb_cheap_total_price+0',
  ));

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

..all the usual field output follows.

Now at present I define which records to show by using 'where' to select from a specific 'category' (field), but I'd also like to add in an additional criteria. Essentially I have another field called 'uk_network_availability' that is a list field and includes three options:

Significant National Coverage
Patchy National Coverage
Niche / Area Specific

So what I want to do is show the records from my "Superfast Broadband" category as above, but now only display those that have the "Significant National Coverage" selection under the 'uk_network_availability' list field. I tried doing this by simply adding a second 'where' call (see below) after the first one for 'category', but it then seemed to ignore the original 'where' category call. I'm sure there's a simple way to do this?

    'where'       => " uk_network_availability LIKE '%Significant National Coverage%' ",

By mark99 - November 14, 2016

Yes that worked, brilliant! :)

Now what if I wanted to get a bit more complicated with it and display all the records for 'uk_network_availability' that matched either 'Significant National Coverage' OR 'Patchy National Coverage'?

I tried doing this:

AND uk_network_availability LIKE '%Significant National Coverage%' OR '%Patchy National Coverage%'

But it still only saw the records with 'Significant National Coverage' selected and ignored those with 'Patchy National Coverage' selected.

By gregThomas - November 15, 2016

Hey Mark, 

You  just need to tweek your MySQL statement slightly:

`category` LIKE '%Superfast Broadband%' AND (`uk_network_availability` LIKE '%Significant National Coverage%' OR `uk_network_availability` LIKE '%Patchy National Coverage%' )

There where the two issues:

  • You must declare the field you're searching each time, or the second part of the statement will be ignored. 
  • You should wrap the OR statement in brackets, or you'll end up with all records that have significant national coverage, and then all records that have a category of superfast broadband and significant national coverage. 

Cheers,

Greg

Greg Thomas







PHP Programmer - interactivetools.com

By mark99 - November 17, 2016

Perfect, that fixed it, although I had to remove the ' around uk_network_availability in order to make it work, otherwise no records were displayed. Thanks so much Greg.