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 2, 2013

Hi Greg,

Good to hear from you.

The tab separated string works in CMSB but doesn't run in MySQL (as a direct SQL query) but don't worry about that, I am not hand-writing code.

Can I ask one more question?

I want to build a category list page - quite similar to the viewer List Page. So for example, I will run country tags at the end of each story (see code) and hyperlink each to a list page. I would have to numerous individual pages with the getRecord string you provided in order to achieve this. Is there a way that I can create a single template to do this in the same way that the viewer List Page and Detail Page are built?

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

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