Limiting display results using 'where'

Hi,

So this multi option checklist field? I think the problem is that CMS Builder stores multi value checklists in a tab separated sting, so you need to search that string to return center A results:

 // load records  
list($coming_up_eventsRecords, $coming_up_eventsMetaData) = getRecords(array(    
  'tableName'   => 'coming_up_events',    
  'where' => "applies_to LIKE '%\tcentre A\t%'",
));

So the where statement looks through the applies_to field string, and will return any results with center A in them. As this is a tab separated list the \t will add tabs to the string.

Let me know if you have any questions.

Thanks!

Greg

Thanks.  And if I wanted to include centre A and centre B, but not centre C or centre D, how would I do that?

Hi, something like this should work:

// load records  
list($coming_up_eventsRecords, $coming_up_eventsMetaData) = getRecords(array(    
  'tableName'   => 'coming_up_events',    
  'where' => "applies_to LIKE '%\tcentre A\t%' OR applies_to LIKE '%\tcentre B\t%'",
));

So the where statement will look for results which contain either 'centre A' or 'centre B'. It should also include results which have both ticked.

Thanks!

Greg

Perfect, thanks!

Hi zick, 

Sometimes I find it easier to write the code over a few lines for simplicity and debugging purposes.  Try this: 

// get where
$escapedValue = mysql_escape( $home_pageRecord['feature_gallery_category:label'] ); // replace ' with \', etc
$where        = "gallery_category LIKE '%$escapedValue%'";

// debug (comment or remove this part when you're done)
print "feature_gallery_category:label = '{$home_pageRecord['feature_gallery_category:label']}'<br/>\n";
print "where = '$where'<br/>\n";
print "Done";  exit;

// load record from 'gallery'
list($galleryFeaturedRecords, $galleryFeaturedMetaData) = getRecords(array(
  'tableName'   => 'gallery',
  'where'       => $where,
  'loadUploads' => true,
  //'allowSearch' => true,
  'limit'       => '1',
));
$galleryFeaturedRecord = @$galleryFeaturedRecords[0]; // get first record

The key parts are: 

• We escape the value before we pass it to mysql.  This makes it so stray ' or other chars in your keywords don't get confused as additional mysql code.
• We assign the where string to a variable and print that out to make sure it looks exactly how we want
• Once that's working, comment out the debug code and you should be good to go.

Hope that helps, let me know if it works for you.  Thanks.

Dave,

Thanks for the help... your solution did the trick.

One other question if you can help out a bit further. I'd like to add a bit to the $where so that once a date has passed the record is no longer displayed. I've tried various methods to make this work with the code below, but haven't had any luck. Can you help out?

// get where
$escapedValue = mysql_escape( $home_pageRecord['feature_gallery_category:label'] ); // replace ' with \', etc
//$where        = "gallery_category LIKE '%$escapedValue%'";

$where = "gallery_category LIKE '%$escapedValue%' AND date > NOW()";


// debug (comment or remove this part when you're done)
//print "feature_gallery_category:label = '{$home_pageRecord['feature_gallery_category:label']}'<br/>\n";
//print "where = '$where'<br/>\n";
//print "Done";  exit;

// load record from 'gallery'
list($galleryFeaturedRecords, $galleryFeaturedMetaData) = getRecords(array(
  'tableName'   => 'gallery',
  'where'       => $where,
  'loadUploads' => true,
  //'allowSearch' => true,
  'limit'       => '1',
  'orderBy'     => 'date ASC', 
));
$galleryFeaturedRecord = @$galleryFeaturedRecords[0]; // get first record

Hi Zick,

The code example you've provided looks like it should work, assuming you have a date field called 'date'. What is the name of the date field you want posts to be newer than? What errors do you get when you run the code? 

Thanks

Greg