Re: Querying a comma separated list
It works perfectly. Thank you, Terry ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359216 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Querying a comma separated list
Your query should be: select * from pgallery where gtype = 1 if you only want items with a gtype of 1 or select * from pgallery where gtype IN (1,2,3,4) if you want multiple types. You can also set a variable of such as gtypelist =1,2,3,4) then do select * from pgallery where gtype IN (#gtypelist) On Tue, Aug 26, 2014 at 8:13 PM, te...@it-werks.com te...@it-werks.com te...@it-werks.com wrote: I haven't done this is ages and could use some help, please. Now here's where I screw up: cfquery name=getslides datasource=#dsn# select * from pgallery where listContains(gtype, 1) /cfquery ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359201 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Querying a comma separated list
I assume from the example you are storing the a comma delimited list in the Field gtype and need to query all records that match one or more items from the stored list. Here are some ways to accomplish what you want to do plus some other tricks. http://www.sommarskog.se/arrays-in-sql-2005.html Dennis Powers UXB Internet - A website Design and Hosting Company P.O. Box 6028, Wolcott, CT 06716 - T:203-879-2844 W: http://www.uxbinternet.com W: http://www.ctbusinesslist.com -Original Message- From: te...@it-werks.com te...@it-werks.com [mailto:te...@it-werks.com] Sent: Tuesday, August 26, 2014 8:13 PM To: cf-talk Subject: Querying a comma separated list I haven't done this is ages and could use some help, please. Here I define a list of checkboxes of picture types: input type=checkbox name=tt value=1 cfif gtype contains '1'checked/cfifSolidbr input type=checkbox name=tt value=2 cfif gtype contains '2'checked/cfifOpenbr input type=checkbox name=tt value=3 cfif gtype contains '3'checked/cfifInsulatedbr input type=checkbox name=tt value=4 cfif gtype contains '4'checked/cfifCombinationbr At form submit I update the record: cfquery name=upcontent datasource=#dsn# maxrows=1 update pgallery set gtype = '#tt#' where id = #picid# /cfquery Now here's where I screw up: cfquery name=getslides datasource=#dsn# select * from pgallery where listContains(gtype, 1) /cfquery Here;s the error: Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Undefined function 'listContains' in expression. Terry ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359203 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Querying a comma separated list
What I have seen done in these situations is to search where = the value or one of several like statements. where field = 'value' or field like 'value,%' or field like '%,value,%' or field like '%,value' David Phelan Web Developer IT Security Web Technologies Emerging Health Montefiore Information Technology 3 Odell Plaza, Yonkers, NY 10701 914-457-6465 Office dphe...@emerginghealthit.com www.emerginghealthit.com www.montefiore.org From: UXB denn...@uxbinternet.com Sent: Wednesday, August 27, 2014 1:46 PM To: cf-talk Subject: RE: Querying a comma separated list I assume from the example you are storing the a comma delimited list in the Field gtype and need to query all records that match one or more items from the stored list. Here are some ways to accomplish what you want to do plus some other tricks. http://www.sommarskog.se/arrays-in-sql-2005.html Dennis Powers UXB Internet - A website Design and Hosting Company P.O. Box 6028, Wolcott, CT 06716 - T:203-879-2844 W: http://www.uxbinternet.com W: http://www.ctbusinesslist.com -Original Message- From: te...@it-werks.com te...@it-werks.com [mailto:te...@it-werks.com] Sent: Tuesday, August 26, 2014 8:13 PM To: cf-talk Subject: Querying a comma separated list I haven't done this is ages and could use some help, please. Here I define a list of checkboxes of picture types: input type=checkbox name=tt value=1 cfif gtype contains '1'checked/cfifSolidbr input type=checkbox name=tt value=2 cfif gtype contains '2'checked/cfifOpenbr input type=checkbox name=tt value=3 cfif gtype contains '3'checked/cfifInsulatedbr input type=checkbox name=tt value=4 cfif gtype contains '4'checked/cfifCombinationbr At form submit I update the record: cfquery name=upcontent datasource=#dsn# maxrows=1 update pgallery set gtype = '#tt#' where id = #picid# /cfquery Now here's where I screw up: cfquery name=getslides datasource=#dsn# select * from pgallery where listContains(gtype, 1) /cfquery Here;s the error: Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Undefined function 'listContains' in expression. Terry ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359204 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Querying a comma separated list
listContains() isnt a function of the database but, rather, a CFML function. If youre storing the list as a string list, and are querying the database, then your query will have to use Access string functions as it doesnt know CFML: http://www.techonthenet.com/access/functions/ HTH, Jon On Aug 26, 2014, at 8:13 PM, te...@it-werks.com te...@it-werks.com te...@it-werks.com wrote: I haven't done this is ages and could use some help, please. Here I define a list of checkboxes of picture types: input type=checkbox name=tt value=1 cfif gtype contains '1'checked/cfifSolidbr input type=checkbox name=tt value=2 cfif gtype contains '2'checked/cfifOpenbr input type=checkbox name=tt value=3 cfif gtype contains '3'checked/cfifInsulatedbr input type=checkbox name=tt value=4 cfif gtype contains '4'checked/cfifCombinationbr At form submit I update the record: cfquery name=upcontent datasource=#dsn# maxrows=1 update pgallery set gtype = '#tt#' where id = #picid# /cfquery Now here's where I screw up: cfquery name=getslides datasource=#dsn# select * from pgallery where listContains(gtype, 1) /cfquery Here;s the error: Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Undefined function 'listContains' in expression. Terry ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359196 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Querying a comma separated list
I'm pretty certain you can't do what you're trying to do directly. The comma delimited list you have stored in gtype is just a string as far as the DB engine is concerned and cannot be searched as a list. If you must store it this way, I have achieved the result I think you want, in the past, with the following somewhat ugly query: cfquery name=getslides datasource=#dsn# select* from pgallery wheregtype LIKE '1' OR gtype LIKE '1,%' OR gtype LIKE '%,1' OR gtype LIKE '%,1,%' /cfquery Basically this manually searches the string for the four possible positions for your target element in a comma delimited list: 1. Element is the only thing in the list 2. Element begins the list 3. Element ends the list 4. Element is in the list between two other elements Hope that helps. Also I'm basing this on my experience with MS-SQL so apologies if MS-ACCESS actually does provide a way to do this natively. On 27 August 2014 10:32, Jon Clausen jon_clau...@silowebworks.com wrote: listContains() isnât a function of the database but, rather, a CFML function. If youâre storing the list as a string list, and are querying the database, then your query will have to use Access string functions as it doesnât know CFML: http://www.techonthenet.com/access/functions/ HTH, Jon On Aug 26, 2014, at 8:13 PM, te...@it-werks.com te...@it-werks.com te...@it-werks.com wrote: I haven't done this is ages and could use some help, please. Here I define a list of checkboxes of picture types: input type=checkbox name=tt value=1 cfif gtype contains '1'checked/cfifSolidbr input type=checkbox name=tt value=2 cfif gtype contains '2'checked/cfifOpenbr input type=checkbox name=tt value=3 cfif gtype contains '3'checked/cfifInsulatedbr input type=checkbox name=tt value=4 cfif gtype contains '4'checked/cfifCombinationbr At form submit I update the record: cfquery name=upcontent datasource=#dsn# maxrows=1 update pgallery set gtype = '#tt#' where id = #picid# /cfquery Now here's where I screw up: cfquery name=getslides datasource=#dsn# select * from pgallery where listContains(gtype, 1) /cfquery Here;s the error: Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Undefined function 'listContains' in expression. Terry ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359197 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Querying a comma separated list
Back when i used to pack fields with comma delimited strings, i used to do
something like this:
select * from pgallery where concat(',', gtype,',') like '%,1,%'
(add the , to the front and back of the string to test, so the value you
want is always ,VAL,)
this then only runs one like query, rather than multiple.
not sure if it is better, but it was simpler for me to understand
hth
Jerry Milo Johnson
On Tue, Aug 26, 2014 at 8:41 PM, Bill Moniz hydro.b...@gmail.com wrote:
I'm pretty certain you can't do what you're trying to do directly. The
comma delimited list you have stored in gtype is just a string as far as
the DB engine is concerned and cannot be searched as a list. If you must
store it this way, I have achieved the result I think you want, in the
past, with the following somewhat ugly query:
cfquery name=getslides datasource=#dsn#
select*
from pgallery
wheregtype LIKE '1' OR gtype LIKE '1,%' OR gtype LIKE '%,1' OR gtype
LIKE '%,1,%'
/cfquery
Basically this manually searches the string for the four possible positions
for your target element in a comma delimited list:
1. Element is the only thing in the list
2. Element begins the list
3. Element ends the list
4. Element is in the list between two other elements
Hope that helps. Also I'm basing this on my experience with MS-SQL so
apologies if MS-ACCESS actually does provide a way to do this natively.
On 27 August 2014 10:32, Jon Clausen jon_clau...@silowebworks.com wrote:
listContains() isnât a function of the database but, rather, a CFML
function. If youâre storing the list as a string list, and are querying
the database, then your query will have to use Access string functions as
it doesnât know CFML:
http://www.techonthenet.com/access/functions/
HTH,
Jon
On Aug 26, 2014, at 8:13 PM, te...@it-werks.com te...@it-werks.com
te...@it-werks.com wrote:
I haven't done this is ages and could use some help, please.
Here I define a list of checkboxes of picture types:
input type=checkbox name=tt value=1 cfif gtype contains
'1'checked/cfifSolidbr
input type=checkbox name=tt value=2 cfif gtype contains
'2'checked/cfifOpenbr
input type=checkbox name=tt value=3 cfif gtype contains
'3'checked/cfifInsulatedbr
input type=checkbox name=tt value=4 cfif gtype contains
'4'checked/cfifCombinationbr
At form submit I update the record:
cfquery name=upcontent datasource=#dsn# maxrows=1
update pgallery set gtype = '#tt#'
where id = #picid#
/cfquery
Now here's where I screw up:
cfquery name=getslides datasource=#dsn#
select * from pgallery where listContains(gtype, 1)
/cfquery
Here;s the error:
Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC
Microsoft Access Driver] Undefined function 'listContains' in expression.
Terry
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359198
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
