Selecting Records from Tabbed data

3 posts by 2 authors in: Forums > CMS Builder
Last Post: June 30, 2010   (RSS)

By theclicklab - June 29, 2010

Hi there,

I have an issue where I'm only getting back some of the records that I need to display. I'm selecting from a field which has tabbed values in it like this:

" 1 11 10 "

and my where clause is only bringing back anything which starts with the right number:

$destNum = $destinationsRecord['num'];
if (!$destNum) { $destNum = 1; } // default num
list($yachtsRecords, $yachtsMetaData) = getRecords(array(
'tableName' => 'yachts',
'allowSearch' => '0',
'where' => "destinations IN ($destNum)" . ' AND active ="1"' . ' AND hide ="0"',
'useSeoUrls' => true,
'debugSql' =>'true',
));

What do I need to do with my where clause for this to work?

Many thanks!

Re: [aquaman] Selecting Records from Tabbed data

By Jason - June 30, 2010

Hi,

"IN" is used to search a list, but CMS Builder stores the multi selects as a string. We can get around this by using "LIKE".

Try this code:

$destNum = $destinationsRecord['num'];

if (!$destNum) { $destNum = 1; } // default num

$destNum = "%\t".$destNum."\t%";

list($yachtsRecords, $yachtsMetaData) = getRecords(array(
'tableName' => 'yachts',
'allowSearch' => '0',
'where' => "destinations LIKE '$destNum'" . ' AND active ="1"' . ' AND hide ="0"',
'useSeoUrls' => true,
'debugSql' =>'true',
));

Hope this helps.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/