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

Situation:

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

FWIW here's what eventually worked:

'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

Hi InHouse,

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!
All the best,
Chris

Re: [InHouse] Lists in the Where clause

By Chris - August 21, 2009

Hi InHouse,

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.
All the best,
Chris