Selecting Records Using Where => LIKE

4 posts by 2 authors in: Forums > CMS Builder
Last Post: August 23, 2010   (RSS)

By theclicklab - August 21, 2010

Hi there,

I am trying to select multiple records to show using Where => LIKE and am having trouble getting data to show. I thing my where clause is not correct. Here's the code:

<?php
$staffNum = $pagesRecord['staff'];
$staffDisable = 0;
if (!$staffNum) { $staffNum = 0; } // set default num
if ($staffNum == 0) { $staffDisable = 1; } // used to disable staff display if no num
list($staffRecords, $staffMetaData) = getRecords(array(
'tableName' => 'staff',
'allowSearch' => '0',
'where' => "num LIKE '%\t$staffNum\t%'",
'debugSql' =>'true',
));
?>

<?php if ($staffDisable == 0): ?>
<div class="box">
<h2>Meet our <?php echo $pagesRecord['name'] ?> Specialist(s)</h2>
<?php foreach ($staffRecords as $record): ?>
<p class="partner clearfix"><b><a href="<?php echo $record['page_link'] ?>">
<?php foreach ($record['image'] as $upload): ?>
<img src="<?php echo $upload['urlPath'] ?>" width="<?php echo $upload['width'] ?>" height="<?php echo $upload['height'] ?>" alt="<?php echo $record['title'] ?>" />
<?php endforeach ?>
<?php echo $record['title'] ?></a></b><br />
<?php echo $record['description'] ?></p>
<?php endforeach ?>
</div>
<?php endif ?>

Debug is showing this:

SELECT SQL_CALC_FOUND_ROWS `staff`.* FROM `cms_staff` as `staff` WHERE (num LIKE '% 3 7 %')

Any tips? Many Thanks!

Re: [aquaman] Selecting Records Using Where => LIKE

By Jason - August 23, 2010

Hi,

With the where clause you're using (%\t$staffNum\t%), what you're looking for is a number that has a tab character before and after it in the num field. num is a special field in CMS Builder that only ever holds 1 number. There will never be any tab characters in it, so you're where clause will never return any records.

Using LIKE the way you have it is usually used when selecting from fields that are multi value drop down lists. Since you're selecting from the "staff" table, if you could tell me the names of all of the fields in the staff table and what type of field they are, I can try to help you more.

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/

Re: [Jason] Selecting Records Using Where => LIKE

By theclicklab - August 23, 2010

Hi Jason,

maybe I shouldn't be using "like"?

Fields in "staff" table are:

num
createdDate
createdByUserNum
updatedDate
updatedByUserNum
dragSortOrder
title
description
image
email_address_username
page_link


In my table "pages" I have:

field name: staff
field type: list

options:

- checkboxes (multi value)
- get options from database
- section tablename: staff
- Use this field for option values: num
- Use this field for option labels: title

Many thanks,
Jan