OR...

8 posts by 3 authors in: Forums > CMS Builder
Last Post: February 29, 2012   (RSS)

By Toledoh - February 28, 2012

Hi Guys,

What am I doing wrong here;
// load records
list($sponsorsRecords, $sponsorsMetaData) = getRecords(array(
'tableName' => 'sponsors',
'where' => 'category !="Energex"',
'orWhere' => 'category !="Fitzpatrick"',
));

I want to show a list of sponsors, but not Energex or Fitzpatrick".
Cheers,

Tim (toledoh.com.au)

Re: [brownleather] OR...

By Toledoh - February 29, 2012

Thanks - but doesn't work. I get both Energex and Fitzpatrick showing up...

'where' => "category != 'Energex'", removes Energex,

'where' => "category != 'Fitzpatrick' ", removes Fitzpatrick, but

'where' => "category != 'Energex' OR category != 'Fitzpatrick' ", shows all...
Cheers,

Tim (toledoh.com.au)

Re: [Toledoh] OR...

By Dave - February 29, 2012

You're close!

Try:
'where' => "category != 'Energex' AND category != 'Fitzpatrick' ",

or:
'where' => "category NOT IN ('Energex', 'Fitzpatrick') ",

Let me know if that works for you.
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] OR...

By Toledoh - February 29, 2012

Both work!

But strange... just out of interest, if you had a multi-select, so sponsors could take up any of Options A, B, C, D, E... how would you display any sponsor that doesn't take up BOTH options "A" AND "B"?

Know what I mean? (and just out of interest)
Cheers,

Tim (toledoh.com.au)

Re: [Toledoh] OR...

By Dave - February 29, 2012 - edited: February 29, 2012

Multi-selects are store as tab separated values with a leading and trailing tab. For example, A, B, C would be stored as: (tab)A(tab)B(tab)C(tab)

In Mysql and PHP you can refer to a tab with \t

So to see if a multi-value list contains an option you could use:
category LIKE "%\tA\t%"

To return all records but those with BOTH A and B selected:
'where' => 'category NOT LIKE "%\tA\t%" AND category NOT LIKE "%\tB\t%" ',

And sometimes it's easier to group things with parentheses and to put the NOT in front.

So instead of: "NOT A and NOT B" you can say: NOT (A AND B) or using the above example:
'where' => ' NOT (category LIKE "%\tA\t%" AND category LIKE "%\tB\t%") ',

Hope that helps!
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] OR...

By Toledoh - February 29, 2012

Thanks Dave.

It can get pretty complex pretty quickly huh!
Cheers,

Tim (toledoh.com.au)

Re: [Toledoh] OR...

By Dave - February 29, 2012

Yea, more so with those multi-value fields. Definitely a more advanced feature.
Dave Edis - Senior Developer
interactivetools.com