Where to check for matches and filter out

3 posts by 3 authors in: Forums > CMS Builder
Last Post: January 17, 2020   (RSS)

By Mikey - January 17, 2020

On my Geo Map, I need to filter out homes that are not related to a floor plan, so that only home floor plans that match the floor plan's record number are displayed.  I am trying to create this 'where' => 'floor_plan' == $floor_plansRecord['num'], to accomplish the job, but it doesn't seem to work. Any suggestions on the right configuration for this to accomplish the filtering?

  // load record from 'floor_plans'
  list($floor_plansRecords, $floor_plansMetaData) = getRecords(array(
    'tableName'   => 'floor_plans',
    'where'       => whereRecordNumberInUrl(0),
    'loadUploads' => true,
    'allowSearch' => false,
    'limit'       => '1',
  ));
  $floor_plansRecord = @$floor_plansRecords[0]; // get first record
  if (!$floor_plansRecord) { dieWith404("Record not found!"); } // show error message if no record found
  
    // load records from 'homes_for_sale'
  list($homes_for_saleRecords, $homes_for_saleMetaData) = getRecords(array(
    'tableName'   => 'homes_for_sale',
    'loadUploads' => true,
    'allowSearch' => false,
    'orderBy' => 'price ASC'
  ));

  list($myRecords, $myMetaData) = getRecords(array(
    'tableName'   => 'homes_for_sale',
    'where'       => 'floor_plan' == $floor_plansRecord['num'],
    'allowSearch' => false,
  ));

Thanks, Zicky

By daniel - January 17, 2020

Hi Zicky,

A very handy CMSB function for making properly escaped "where" statements is mysql_escapef(). In this case, you would use it like this:

'where' => mysql_escapef('floor_plan = ?', $floor_plansRecord['num']),

Try that out and let me know if it does the trick.

A note on usage: mysql_escapef() replaces the "?" in the string with the value provided. It can also handle additional values by adding more "?" characters, and each one will be replaced in sequence. For example:

mysql_escapef('one = ? AND two = ? AND three = ?', $one, $two, $three)

Cheers,

Daniel
Technical Lead
interactivetools.com