Selecting Records Using Where => LIKE
4 posts by 2 authors in: Forums > CMS Builder
Last Post: August 23, 2010 (RSS)
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:
Debug is showing this:
SELECT SQL_CALC_FOUND_ROWS `staff`.* FROM `cms_staff` as `staff` WHERE (num LIKE '% 3 7 %')
Any tips? Many Thanks!
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.
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/
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
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
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
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.
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/
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/