This is a short and simple questions I thought may be interesting to beginners:
I have a query "Select * From PhotoTable, Category Where PhotoTable.FileName IN ('#KeywordSearchResults#') AND CategoryID = Category.ID Order by CategoryPrefix, Filename ASC" I am using a ValueList that is dynamically generated. I need to know how I can define a custom delimiter in valuelist so that the values appear as 'a',b','c'. Right now whatever I do either i get single quote or comma. Please Help.
First off - you know the select * is bad, right? Never, ever, use select *. Always list out the columns. Also, you want to replace the simple #KeywordSearchResults# variable with a cfqueryparam tag. But that's not why you are here.
In case folks don't know, the valueList function gets you all the values from one column of a query. It's a handy little function. To use a custom delimiter, just supply a second argument to the function. So for example, this would use the @ sign:
<cfset values = valueList(myQuery, "@")>
However, the writer wants to wrap each list item with single quotes. You can't do this with valueList. Luckily ColdFusion provides us with listQualify. This function will wrap each list item with a character. So to add single quotes to the code above, you would do this:
<cfset values = valueList(myQuery, "@")> <cfset niceList = listQualify(values, "'")>
One little thing about valueList. It doesn't allow for dynamic column names. If you want to dynamically get one column, you can treat the column as an array:
<cfset col = "id"> <cfset values = arrayToList(myQuery[col])>