Solved

Coldfusion- count list items in cfquery and cfloop

Posted on 2012-12-29
8
1,187 Views
Last Modified: 2013-01-01
Hello experts
I need help to count the list items for each language and compare them using the following code.
Any help?
<cfoutput query="getlanguages">
  <ul class="bdesc">
   <cfif len(trim(form['Ingredients_' & languages_id]))>
    <cfset text = replace(trim(form['Ingredients_' & languages_id]),Chr(13),'^', 'all')>
    <cfset 'list_#languages_id#' = 0 >
    <cfloop list="#text#" index="i" delimiters="^">
     <cfset 'list_#languages_id#' =  ['list_' &  languages_id] + 1>
     <li>#trim(i)#</li>
    </cfloop>
   </cfif>
  </ul>
  <cfdump var="">
</cfoutput>
0
Comment
Question by:Panos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 29

Expert Comment

by:Pravin Asar
ID: 38730960
Looping over the form fields (name matching = 'Ingrednents' & language_id), you can build a
query.

Here is a outline of the code,

1. Use QueryNew() to define the query structure.
     <cfset lqry=
2. Loop over the form field names and add to query. Use QueryAddRow() and QuerySetCell()
3. One you have your new query structure populated, you can count.

Here is code, (not tested )

<!--- Define Query --->
<cfset myQuery = QueryNew("fieldname, fieldvalue", "VarChar, varchar")>
<!--- Start Populating --->
<cfloop list="#form.fieldnames#" index="fld">
    <cfset tt= QueryAddRow(MyQuery, 1)/>
    <cfset tt= QuerySetCell(myQuery, "fieldname", #fld#)/>
    <cfset tt= QuerySetCell(myQuery, "fieldvalue", form[#fld#])/>
</cfloop>


<!--- get the field count --->
<cfloop query="getlanguages">
<cfquery name="group_by" dbtype="query">
    SELECT fieldname AS x, count(fieldname) as fieldCnt  
    FROM myquery  
    WHERE fieldname like 'Ingredients_' & #getLanguages.language_id
    GROUP BY fieldname
</cfquery>
<cfoutput><br/>#group_by.fieldcnt#: #group_by.fieldcnt#</cfoutput>
</cfloop>
0
 
LVL 2

Author Comment

by:Panos
ID: 38731001
Hmmm.
Hi  pravinasar.
I think you did not understand what i'm trying to do or i did not understand your code.
I have a formfield "Ingredients_' & languages_id"
The number of the Ingredients_ fields depends on how many languages i have activated.
A user can write inside that field the ingredients of a recipe spliting them using the enter key. I 'm using hidden - show tabs for each language.
To tranform the entered values to a list i'm using:
<cfset text = replace(trim(form['Ingredients_' & languages_id]),Chr(13),'^', 'all')>
I will add to each step one image upload field but not for each language separate.
the problem now is that the user can make any mistake with the number of Ingredients.
That what i was looking for was to find a way to count and compare the lists of every language.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38731222
I'm not completely sure what you're after - and unfortunately I can't stick around today. But maybe this example might help (?)

<!--- sample data --->
<cfset getLanguages = queryNew("")>
<cfset queryAddColumn(getLanguages, "languages_id", listToArray("1,2,3,4"))>

<cfset FORM.Ingredients_1 = "">
<cfset FORM.Ingredients_2 = "foo"& chr(13) & "bar">
<cfset FORM.Ingredients_3 = "">
<cfset FORM.Ingredients_4 = "foo"& chr(13) & "bar"& chr(13) & "qax">

<cfset languageTotals = {}>
<cfoutput query="getLanguages">
  <ul class="bdesc">
   	<cfif len(trim(form['Ingredients_' & languages_id]))>
	    <cfset text = replace(trim(form['Ingredients_' & languages_id]),Chr(13),'^', 'all')>
	    <cfset total = 0>
		<cfloop list="#text#" index="i" delimiters="^">
	    	<cfset total = total+ 1>
	    	<li>#trim(i)#</li>
		</cfloop>
		<cfset languageTotals[languages_id] = total>
		<li><strong>items:</strong> #languageTotals[languages_id]#</li>
	</cfif>
  </ul>
</cfoutput> 

<!--- compare all --->
<cfloop collection="#languageTotals#" item="id">
	<cfoutput>
 	total for languages_id[#id#] = #languageTotals[id]#<br>
	</cfoutput>
</cfloop>

Open in new window

0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 2

Author Comment

by:Panos
ID: 38731600
Hi agx.
It is almost what i need.
I need a cfif tag that is comparing all languagetotals. If they are all the same.....
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 38732955
To just verify the counts are the same, an array would be better:

<!--- sample data --->
<cfset getLanguages = queryNew("")>
<cfset queryAddColumn(getLanguages, "languages_id", listToArray("1,2,3,4"))>

<cfset FORM.Ingredients_1 = "">
<cfset FORM.Ingredients_2 = "foo"& chr(13) & "bar">
<cfset FORM.Ingredients_3 = "">
<cfset FORM.Ingredients_4 = "foo"& chr(13) & "bar"& chr(13) & "qax">

<cfset allTotals = []>
<cfoutput query="getLanguages">
  <ul class="bdesc">
   	<cfif len(trim(form['Ingredients_' & languages_id]))>
	    <cfset text = replace(trim(form['Ingredients_' & languages_id]),Chr(13),'^', 'all')>
	    <cfset total = 0>
		<cfloop list="#text#" index="i" delimiters="^">
	    	<cfset total = total + 1>
	    	<li>#trim(i)#</li>
		</cfloop>
		<cfset arrayAppend(allTotals, total)>
	</cfif>
  </ul>
</cfoutput> 

<cfif arrayMin(allTotals) neq arrayMax(allTotals)>
	amounts are not the same. do something here ...
</cfif>

Open in new window

0
 
LVL 2

Author Comment

by:Panos
ID: 38734320
Thank you agx.
It is working.

Happy new year
0
 
LVL 2

Author Closing Comment

by:Panos
ID: 38734321
Thank you
regards
panos
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38735139
Welcome. Happy new year :)
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question