Lists in the Where clause
5 posts by 2 authors in: Forums > CMS Builder
Last Post: August 21, 2009 (RSS)
By InHouse - August 19, 2009 - edited: August 20, 2009
A list field in CMSB is set up as radio option for mutually exclusive selections.
Values in manual field set to:
left|Left Col (kids only)
mid|Middle Col (stuff)
right|Right Col (any people)This is sending what looks like un-tabbed text to the database. Database showing entries such as "left", "right" (no quotes), etc. All good.
My where clause has looked like:
'where' => 'group = "left" ',
and ...
'where' => 'group = "%left%" ',
and...
'where' => 'group LIKE "%left%" ',
and...
'where' => 'group LIKE "%/tleft%/t" ',
and ... other variations on the theme.debugSql is on.
In all cases I'm getting something like:
SELECT SQL_CALC_FOUND_ROWS `im_graphics`.* FROM `cms_im_graphics` as `im_graphics` WHERE (group = "%left%" ) AND im_graphics.hidden = 0 ORDER BY RAND() LIMIT 1
Notice: Undefined variable: VIEWER_NAME in /home/health17/public_html/cmsAdmin/lib/viewer_functions.php on line 233 MySQL
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
'group = "%left%" ) AND im_graphics.hidden = 0 ORDER BY RAND() LIMIT 1' at line 3 Have even tried to change the data field to a select list (single entry) where I've had luck before. No joy there either. Hiding the where clause allows the script to function well.
Any someone spot what my tired eyes are failing to see here?
Cheers,
J.
Re: [InHouse] Lists in the Where clause
By InHouse - August 20, 2009
'where' => "`group` = 'right' ",
Note the PHPMyAdmin style quotes `.
The interesting part was that the escaped tab characters (/t) were not being honoured.
Solved otherwise.
Re: [InHouse] Lists in the Where clause
By Chris - August 21, 2009
Sorry to hear you ran into trouble and I'm glad you've got it figured out.
One thing that may have been causing problems is that GROUP is a reserved word in MySQL. Putting it in quotes would definitely avoid that problem.
As for your tab characters, you'll need to use a backslash instead of a forward slash, e.g.:
'where' => ' `group` LIKE "%\tleft\t%" ',Hope this helps!
Chris
Re: [chris] Lists in the Where clause
By InHouse - August 21, 2009
You're quite right about the escape character of course. As I get older late nights become hazy for different reasons than in my youth. ;-) <sigh>
While I have your attention though, do you know any syntax for doing and OR in the WHERE statement. My normal attempts have not worked out.
J.
Re: [InHouse] Lists in the Where clause
By Chris - August 21, 2009
The only gotcha I can think of is that you have to specify the field name again, e.g.:
'where' => ' `group` LIKE "%\tleft\t%" OR `group` LIKE "%\tright\t%" ',Hope this helps! If not, please post the details of what you're trying to accomplish.
Chris