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

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!

So I've been messing around with 'where' trying to get this to work. It works just as I need it to if I hard code %Visitors% into the ('where'   => " gallery_category LIKE '%Visitors%' ",) as seen in bold black.

But I need to make this dynamic, so that I can choose one category from a list various category options and have the the were statement update according to category selected. So if in the "Home Page" section of my site, I change my category from "Visitors" to "Guest" then the latest photo associated with "Guest" uploaded to the photo gallery will be displayed on the Home Page of my website. Something like what can bee seen in bold red.

Any suggestions to make this dynamic in the WHERE clause?

  // load record from 'gallery'
  list($galleryFeaturedRecords, $galleryFeaturedMetaData) = getRecords(array(
    'tableName'   => 'gallery',
    //'where'       => '', // load first record
    'where'       => " gallery_category LIKE '%Visitors%' ",
    //'where'       => 'gallery_category LIKE '.$home_pageRecord['feature_gallery_category:label'],
    'loadUploads' => true,
    //'allowSearch' => true,
    'limit'       => '1',
  ));
  $galleryFeaturedRecord = @$galleryFeaturedRecords[0]; // get first record

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