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 ross - November 10, 2016

Hi there.

Thanks for posting.

You can combine your two "where" with an "AND" like this:

'where'       => " category LIKE '%Superfast Broadband%' AND uk_network_availability LIKE '%Significant National Coverage%' ",

Could you give that a shot and let me know how you make out.

Thanks!

-----------------------------------------------------------
Cheers,
Ross Fairbairn - Consulting
consulting@interactivetools.com

Hire me! Save time by getting our experts to help with your project.
Template changes, advanced features, full integration, whatever you
need. Whether you need one hour or fifty, get it done fast with
Priority Consulting: http://www.interactivetools.com/consulting/

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