Formatting of 'where' clause

4 posts by 3 authors in: Forums > CMS Builder
Last Post: October 4, 2017   (RSS)

By dnevels - September 8, 2017 - edited: September 8, 2017

I am trying to get the formatting right for my where clause. Here is what I am doing:

I have a dropdown menu populated by the database. I make a selection from the dropdown and "GET" that value to another file.

The second file is the experimental multi record viewer. I am trying to add a where clause to show only records with the same Bus_categories:

$Bus_categories = $_GET['Bus_categories'];
  list($categoriesRecords, $categoriesMetaData) = getRecordsAPI(array(
    'tableName'   => 'categories',
    'perPage'     => '10',
    'loadUploads' => false,
    'allowSearch' => true,
  'debugSql' => true,
'where' => htmlencode($record['Bus_categories']),

Does this make sense?

UPDATE: I tried using the current list page code rather than the Experimental code and it works. Either I needed to do a different way or there is a bug in the experimental code.

By Dave - September 11, 2017

Hi dnevels, 

For the new viewer code, try this: 

list($categoriesRecords, $categoriesMetaData) = getRecordsAPI(array(
  'tableName'   => 'categories',
  'perPage'     => '10',
  'loadUploads' => false,
  'allowSearch' => true,
  'debugSql'    => true,
  'where'       => "category = :bus_category",
  'params'      => [                              // params can be used in these fields: where, orderBy
    ':bus_category' => $_GET['Bus_categories'],   // specify any values you want to use here
  ],
));

You specify any input parameters you want to use in "params" and refer to them in the where.  It does all the escaping for you.

Also, for your where, you probably want to compare it to the value of a field.  I added "category = " but you'll want to make sure that matches your fieldname.

And... If it's working with the current list page code, feel free to stick with that if you want to save time.

Hope that helps!

Dave Edis - Senior Developer
interactivetools.com

By Dave - October 4, 2017

Hi Brownleather, 

I'm loving the new getRecordAPI, any eta on LeftJoin support?

That's great.  I'd be interested in any feedback you have (feel free to create a new post if you have any or email me direct at dave@interactivetools.com).  We have leftJoin support in the queue and it should be done this month (October).

Thanks!

Dave Edis - Senior Developer
interactivetools.com