First off, I would suggest making your query much simpler, just to diagnose the
problem. Then add complexity to it and see if/when it goes wrong...
Start with (for example):
SELECT roomDisplay, roomID, ageGroup, accountType
FROM chatrooms
WHERE
<cfqueryparam value="#arguments.accountType#"
cfsqltype="cf_sql_varchar" /> IN (accountType)
AND <cfqueryparam value="#arguments.ageGroup#"
cfsqltype="cf_sql_integer" /> IN (ageGroups)
See what that returns for you... Then start adding your Concat statements one
at a time and see what changes. It may not be following the same logic you are
in your head, and this will force you to see it one step at a time, so you can
figure out why it's doing what it's doing...
Best of luck,
Laurence MacNeill
Mableton, Georgia, USA
--- In [email protected], Wally Kolcz <wko...@...> wrote:
>
> I know this is out of the core topic, but I am banging my head on this
> simple issue..or probably is...
>
> I am trying to write a SQL query (in ColdFusion) for my database (MySQL
> 5) when I am looking for a passed in argument value in the column (which
> is set as a varchar).
>
> I am passing in '0' for the ageGroup and 'kids' for the accountType I
> have 'kids'
>
> My Query is:
>
> Select CONCAT(roomDisplay," (National)") as roomDisplay,
> CONCAT(<cfqueryparam value="#arguments.ageGroup#">,
> "-", roomDisplay, "-", "National") as roomID
> FROM chatrooms
> WHERE <cfqueryparam value="#arguments.accountType#"> IN
> (accountType)
> AND <cfqueryparam value="#arguments.ageGroup#"
> cfsqltype="cf_sql_integer" /> IN (ageGroups)
> ORDER BY roomDisplay ASC
>
> I have a record where 'ageGroups' is '0, 1, 2, 3, 4, 5, 6, 7, 8' and the
> 'accountType' is 'kids, siblings, parents'
>
> I keep getting 0 returns but I know both values exist in at least 1 record
>
> What am I doing wrong?
>
