easy one! : comma separated list from cfoutput

ok. i am having a serious brain fart... i know there is a beter way to ooutput a list the is comma seperated then this.. <cfloop query="getProd">        <cfset columns = columns&"#specs_partnum#,"> </cfloop> <!--- remove the last comma ---> <cfset columns = left(columns,evaluate(len(columns) -1))> <cfoutput>#columns#</cfoutput> but again, i am drawing a complete blank... any help would be appreciated thanks!

<cfoutput>#valueList(getProd.specs_partnum)#</cfoutput> -Joe On Tue, May 13, 2008 at 11:26 AM, morchella <morchella.deliciosa@gmail.com> wrote: ----- Excess quoted text cut - see Original Post for more -----

ValueList(getProd.specs_partnum, ",") G On Tue, May 13, 2008 at 11:26 AM, morchella <morchella.deliciosa@gmail.com> wrote: ----- Excess quoted text cut - see Original Post for more -----

morchella wrote: ----- Excess quoted text cut - see Original Post for more ----- valueList(getProd,'specs_partnum') OR quotedValueList(getProd,'specs_partnum')

awsome... thank you! now.. =] how would i remove doubles? <!--- List of Accessory's that are assigned to this product  ---> <cfquery name="getAccessory" datasource="listit"> SELECT DISTINCT ascList FROM dbo.list WHERE (specs_partnum IN (#columns#)) AND (ascList IS NOT NULL) </cfquery> gives me... ascList 132 135 135,132 so my  list looks like aclist: 132,135,135,132 just want 132,135 thanks again!

also why is it not listed in my wack books under functions? also where did functions go in the 8 series of wack books.. i know its there in 4.5-7... On Tue, May 13, 2008 at 11:46 AM, morchella <morchella.deliciosa@gmail.com> wrote: ----- Excess quoted text cut - see Original Post for more -----

If you're doing a distinct in your query, or a group by, then you shouldn't have any dupes. awsome... thank you! now.. =] how would i remove doubles? <!--- List of Accessory's that are assigned to this product  ---> <cfquery name="getAccessory" datasource="listit"> SELECT DISTINCT ascList FROM dbo.list WHERE (specs_partnum IN (#columns#)) AND (ascList IS NOT NULL) </cfquery> gives me... ascList 132 135 135,132 so my  list looks like aclist: 132,135,135,132 just want 132,135 thanks again!

right but my result of the query could be 134 135 135, 134 134, 135, 2 so the result is distinct. so when it puts them into a list i get 134, 135, 135, 134, 134, 135, 2 i am going to to try to look at list to array? and then back to list? On Tue, May 13, 2008 at 11:57 AM, Andy Matthews <lists@commadelimited.com> wrote: ----- Excess quoted text cut - see Original Post for more -----

Try this: http://www.bennadel.com/blog/432-Using-ColdFusion-Structures-To-Remove-D uplicate-List-Values.htm m!ke right but my result of the query could be 134 135 135, 134 134, 135, 2 so the result is distinct. so when it puts them into a list i get 134, 135, 135, 134, 134, 135, 2 i am going to to try to look at list to array? and then back to list?

Order by ascList... that should get DISTINCT to work. <cfquery name="getAccessory" datasource="listit"> SELECT DISTINCT ascList FROM dbo.list WHERE (specs_partnum IN (#columns#)) AND (ascList IS NOT NULL) ORDER BY ascList </cfquery> Robert B. Harrison Director of Interactive services Austin & Williams 125 Kennedy Drive, Suite 100 Hauppauge NY 11788 T : 631.231.6600 Ext. 119 F : 631.434.7022 www.austin-williams.com Great advertising can't be either/or... It must be &.

ORDER BY ascList that still gives me ascList 132 135 135,132 On Tue, May 13, 2008 at 12:37 PM, Robert Harrison < robert@austin-williams.com> wrote: ----- Excess quoted text cut - see Original Post for more -----

Paul Ihrig wrote: > ORDER BY ascList > > that still gives me > ascList > 132 > 135 > 135,132 It looks to me like you have a de-normalized database scheme there that stores a list of values in a column.  If that case then that is the result you are going to get.  '132', '135' and '135,132' are three distinct and separate values. If this is the case then you are not going to get a SQL solution.  You will need to do something in ColdFusion after the fact.  Or better yet, if you can, normalize your database so that you are not storing lists of values in a column.

ian, that is exactly what i am doing. but i am storing a list inside a column to refer to user created groupings of items.

In that case you would probably either have to move the values to a look up table (normalize) or uses a function to tease out the dupes. G On Tue, May 13, 2008 at 1:11 PM, Paul Ihrig <pihrig@gmail.com> wrote: > ian, that is exactly what i am doing. > but i am storing a list inside a column to refer to user created groupings > of items.

If that's what you're doing then why not have a user_groupings relational table that holds the user_id and group_id? On Tue, May 13, 2008 at 12:11 PM, Paul Ihrig <pihrig@gmail.com> wrote: > ian, that is exactly what i am doing. >  but i am storing a list inside a column to refer to user created groupings >  of items.

Paul Ihrig wrote: > ian, that is exactly what i am doing. > but i am storing a list inside a column to refer to user created groupings > of items. Ok, this would be fairly easy to normalize with a join table. user_grouping -------------- userID groupID Then you would have records something like iskinner 135 jblow    132 pIhrig    135 pIhrig    132 Your query would then work exactly as you want it to.  Without the modification to your database you are not going to be able to get these results.  You are going to have to finish the processing in your CFML.   One of the easiest ways would be to convert the valueList() resutls to a structure which would consolidate all duplicates then retrieve the list again with structKeyList(). But if you have a choice, normalizing your database is much more flexible and scalable.

lets try this.... productTable pId, acList 1.............3,5,7 2.............2,3 accessoryTable aID, Products 2.............1,2,3 3.............3,4 5.............2 7.............12,15, et.. the user basically selects aa aid from the accessories table in the form of a check box. and ascociates as many id's containg producta [which are accesories] to what ever real product he wants.. this really does seem to work quite well. all my code seems fine now. thanks to that valueList thing... i was just trying to clean up the result sets... WHERE aId IN (#accList#) works... accList: <cfoutput>#ListSort(accList, "Numeric")#</cfoutput><br /> shows accList: 132,132,135,135,136,136,136,136 i was just trying to look through all the stabndard functions out there to remove duplicates. listToArray throws me a error converting simple something to something...

Paul Ihrig wrote: ----- Excess quoted text cut - see Original Post for more ----- Normalized Database Design Best Pratice ProductTable-Accessory Join Product ID AccessoryID 1 3 1 5 1 7 2 2 2 3 AccessoryTable_Product Join 2 1 2 2 2 3 3 3 3 4 5 2 7 12 7 15 Are these some type of recipical thing?  Are are these two different relationships? > this really does seem to work quite well. >   For now.  But if you have to maintain and enhance this application it will bite you in the behind someday.  There is a very good reason that 50 years of database design has been working this way and I don't see it changing any time soon. > i was just trying to look through all the stabndard functions out there to > remove duplicates. > listToArray throws me a error converting simple something to something... Don't for an array it is not going to get you the consilidation you want.  Just create a structure, loop over the valueList() result creating a node for  each value then extrat the list.  Structures can not have key duplicates they will get consilidated into one key. <cfset something = structNew()> <cfloop list="#valueList(aQuery.aColumn)#" index="value">   <cfset something[value] = true> </cfloop> <cfoutput>#structKeyList(something)#</cfoutput>

Ian, Thank You! http://63.144.103.199/products/index.cfm?n1ID=5&n2ID=46&n3ID=148 cant really even to attempt to explain the db structure... never worked with arrays or structures. i do appreciate it.. On Tue, May 13, 2008 at 2:12 PM, Ian Skinner <HOF@ilsweb.com> wrote: ----- Excess quoted text cut - see Original Post for more -----

You can do it using a QoQ and Select DISTINCT OR I wrote a couple of functions to do this. One day I will post them to CFLib..... /** * Case-sensitive function for removing duplicate entries in a list. * @param list      The list to be modified. * @return Returns a list. */     function ListDeleteDuplicates(list) {       var i = 1;       var delimiter = ',';       var returnValue = '';       if(ArrayLen(arguments) GTE 2)         delimiter = arguments[2];           list = ListToArray(list, delimiter);           for(i = 1; i LTE ArrayLen(list); i = i + 1)         if(NOT ListFind(returnValue, list[i], delimiter))         returnValue = ListAppend(returnValue, list[i], delimiter);       return returnValue; }     /**      * Case-INsensitive function for removing duplicate entries in a list.      * @param list      List to be modified.      * @return Returns a list.      */     function ListDeleteDuplicatesNoCase(list)     {       var i = 1;       var delimiter = ',';       var returnValue = '';       if(ArrayLen(arguments) GTE 2)         delimiter = arguments[2];           list = ListToArray(list, delimiter);       for(i = 1; i LTE ArrayLen(list); i = i + 1)       if(NOT ListFindNoCase(returnValue, list[i], delimiter))       returnValue = ListAppend(returnValue, list[i], delimiter);       return returnValue;     } On Tue, May 13, 2008 at 11:46 AM, morchella <morchella.deliciosa@gmail.com> wrote: ----- Excess quoted text cut - see Original Post for more -----

Wait... I cant remember if I wrote those or not. I have a library of list functions... Some I wrote, some I got offa CFlib. Don;t want to take credit for someone else's work. G On Tue, May 13, 2008 at 12:04 PM, Gerald Guido <gerald.guido@gmail.com> wrote: ----- Excess quoted text cut - see Original Post for more -----