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: [aquaman] Selecting Records Using Where => LIKE

By Jason - August 23, 2010

Hi Jan,

If you're trying to select from staff, you should just use = instead of like. You'll only be selecting one record using num.

If you're selecting from pages, you can use like on the staff field as you were, this will search for a particular staff number in the list. It could return multiple records.

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/