CMSB 2.14 not recognising values in list

5 posts by 2 authors in: Forums > CMS Builder
Last Post: September 3, 2013   (RSS)

By terryally - September 2, 2013

Hello InteractiveTools,

I have created a multi value list in order to create country categories. When I try to create category pages CMSB is not recognising the query. I also ran the query directly in MySQL and it too does not recognise it. I then used a textfield to manually type in the country, CMSB recognised this.

How do I get the multi value list to work?

Thanks

Terry

In summary:

FOLLOWING DOES NOT WORK. "country" is a multi value list

CMSB:
  // load records
  list($testRecords, $testMetaData) = getRecords(array(
    'tableName'   => 'test',
    'where' => 'country = "Kenya"',
  ));

MYSQL:
   SELECT *
   FROM `cms_test`
   WHERE `country` = 'Kenya'
   LIMIT 0 , 30


THE FOLLOWING WORKS! "country_tag" is a textfield

CMSB:
  // load records
  list($testRecords, $testMetaData) = getRecords(array(
    'tableName'   => 'test',
    'where' => 'country_tag="Kenya"',
  ));

MYSQL:
   SELECT *
   FROM `cms_test`
   WHERE `country_tag` = 'Kenya'
   LIMIT 0 , 30

By gregThomas - September 2, 2013

Hi Terry,

The values for a multi value list field are stored in a tab separated string, so to search them you'd need to use something like this:

  // load records
  list($testRecords, $testMetaData) = getRecords(array(
    'tableName'   => 'test',
    'where' => "`country` LIKE '%\tKenya\t%'",
  ));

So the getRecords where string will return records where the country field has a string which contains that contains the value Kenya with a tab value before and after it. The same select statement would look like this:

   SELECT *
   FROM `cms_test`
   WHERE `country` LIKE '%  Kenya  %'
   LIMIT 0 , 30

Let me know if you have any questions.

Thanks!

Greg

Greg Thomas







PHP Programmer - interactivetools.com

By terryally - September 3, 2013

Hi Greg,

Solution found.

From the story template page I passed the variable from the story page to the countryList page as follows:

<?php
$country = array_combine($record['country:values'], $record['country:labels']);
$countryCount = count($country);
$i = 1; 
foreach($country as $value => $label): ?>
<a href="countrylist.php?country=<?php echo strtolower($label);?>" title="Display all stories for <?php echo $label; ?>"><?php echo $label; ?></a> <?php if($i != $countryCount){ echo ','; } ?>
<?php $i++; ?>
<?php endforeach; ?>

On the countryList page, I defined the $_GET variable and inserted into search query:

  // load records
  $country = $_GET['country'];
  list($testRecords, $testMetaData) = getRecords(array(
    'tableName'   => 'test',
    'where' => "country LIKE '%\t$country\t%'",
  ));

... and alakazamkazoo! It achieved my objective.

The post http://www.interactivetools.com/forum/forum-posts.php?postNum=2216161#post2216161 also helped.

Best regards

Terry

By gregThomas - September 3, 2013

Hi Terry,

Glad you've got it working. Let me know if you have any questions.

Thanks!

Greg

Greg Thomas







PHP Programmer - interactivetools.com