Very Odd WHERE Clause Issue!
7 posts by 3 authors in: Forums > CMS Builder
Last Post: April 30, 2011 (RSS)
By Perchpole - April 28, 2011
I am trying to return a list of records. Each record has a multi-select list field which allows you to assign it to various sub-groupings.
The URL of the viewer page is appeneded with &sub=n - where n is the value of the sub-group.
The idea is that when the page is displayed, it will show only those records assigned to the particular sub-group. Sounds simple...
All I need to do is use the following code strings to construct the WHERE clause:
$selectedSubGroupNum = @$_REQUEST['sub'];
$record['subGroup']I assumed the WHERE clause would look like this...
list($records, $recordsMetaData) = getRecords(array(
'tableName' => 'table',
'where' => " '%\t{subGroup}\t%' IN ($selectedSubGroupNum) ",
));...but it didn't work - and I couldn't understand why?
In the end (after almost 12 hours of head-banging!) I tried this...
list($records, $recordsMetaData) = getRecords(array(
'tableName' => 'table',
'where' => " subGroup LIKE '%\t{$selectedSubGroupNum}\t%' ",
));...and it worked - but I have no idea why?! It seems completely against everything I've learned (here)!
subGroup returns a tab-delimited list of numbers - therefore surely it should be used in this form %\t{subGroup}\t% - but that didn't work.
Only when I'd swapped the clause around did the viewer page return the correct records. Can someone please explain why?!
:o/
Perch
Re: [Perchpole] Very Odd WHERE Clause Issue!
By robin - April 28, 2011
Your original where statement didn't work for a couple reasons. Wildcards like '%' don't work with IN. Also subGroup is the entire column, so it can't be IN a single value.
http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in
Wildcards do work with with LIKE though so a single value with wildcards can match to the column.
http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like
Hope that helps,
Robin
Programmer
interactivetools.com
Re: [robin] Very Odd WHERE Clause Issue!
By Perchpole - April 28, 2011
Thanks for the feedback. My mind is a little clearer but I still can't seem to grasp the fundamental mechanics of the clause.
For me...
'where' => " subGroup LIKE '%'$selectedSubGroupNum}%' ",...seems to be the wrong way round.
Does the argument work either way?
Perhaps I would understand things a little better if there were some way of illustrating the mechanics schematically?
:o/
Perch
Re: [Perchpole] Very Odd WHERE Clause Issue!
By Jason - April 28, 2011
Using the LIKE statement with the % wildcards, you'll need to have the query like this:
'where' => " subGroup LIKE '%$selectedSubGroupNum%'",If it helps, think of it like telling the database to go through each record and check the value of "subGroup" to see if the value stored in $selectedSubGroupNum appears somewhere in the string. If it does, return that record.
Also, if the field subGroup is a multi-value field, you'll need to add \t to your query like this:
'where' => " subGroup LIKE '%\t$selectedSubGroupNum\t%'",Hope this helps clarify
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] Very Odd WHERE Clause Issue!
By Perchpole - April 28, 2011
Thanks. I do understand the basics - it's just the bit about the tab-delimited string which I'm finding tricky.
subGroup returns a tab-delimited list of numbers - and yet the \t codes are applied around $selectedSubGroupNum which is just a single number.
It seems the wrong way around!
Wibble...
Perch
Re: [Perchpole] Very Odd WHERE Clause Issue!
By Jason - April 29, 2011
You can think of it like this.
Assume subGroup is storing the numbers 100, 150, 200, and 250. In CMS Builder, that list would be stored like this:
\t100\t150\t200\t250\t
Every item in the list has a tab character both in front and behind it.
In MySQL, the % means you match anything in a string that happens either before or after the character.
If, for example, you were trying to match the number 10 to the list like this:
subGroup LIKE '%10%'What you're doing is saying return records where the digits 10 appear anywhere in the list. In our example, we would return our record since 100 contains the digits 10.
This wouldn't be what we wanted.
If we used this, however,
subGroup LIKE '%\t10\t%'We're saying return records where we find \t10\t somewhere in the string. Our record would not be returned in this case. This is why we must put the tab characters in the LIKE part of our clause.
Hope this helps to clarify.
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] Very Odd WHERE Clause Issue!
By Perchpole - April 30, 2011
Got it!
I had always thought the \t code meant "ignore" tabs - which is perhaps why I tried to construct my clause the wrong way around.
Thanks for explaining things!
:0)
Perch