?
Solved

Create a coldfusion variable, named using a variable from a looped list

Posted on 2003-03-14
7
Medium Priority
?
144 Views
Last Modified: 2013-12-24
OK, here is what I am trying to do..

I do a query to my iSeries and get a result set.

Now, the information I need right now is the count of number of records grouped by state, but the problem I am running into is that I need to create named variables for each one.  Here is the code I am trying to make work (and failing)

<cfparam name="StateList" default="'AZ', 'VA', 'MI'">

<cfloop list="Statelist" index="State">
          <cfquery name="GetCountsToday" dbtype="query">
          SELECT
               COUNT(*) as NUM
          FROM
               DRIVERSTODAY
          WHERE
               UNDST = '#State#'
          </cfquery>
         
          <!--- Create variable according to state --->
          <cfparam name="1#State#DRS" default="#GetcountsToday.NUM#">
</cfloop>

So, as an end result, I need a variable called 1AZDRS that is = to the count for AZ.   Any idea's????
0
Comment
Question by:JUSTICE
[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
  • 3
  • 2
  • 2
7 Comments
 
LVL 5

Accepted Solution

by:
JimV_ATL earned 160 total points
ID: 8136921
Your problem is caused by the starting character of your variable.  

"A variable name must begin with a letter, underscore, or Unicode currency symbol."

http://livedocs.macromedia.com/cfmxdocs/Developing_ColdFusion_MX_Applications_with_CFML/Variables2.jsp#1135885
0
 
LVL 1

Author Comment

by:JUSTICE
ID: 8137051
DOH, I got it, simple mis-key's on my part, and missing that ever-important trim function!  

<cfloop list="#Statelist#" index="State">

          <cfquery name="GetCountsToday" dbtype="query">
          SELECT
               COUNT(*) as NUM
          FROM
               DRIVERSTODAY
          WHERE
               UNDST = '#State#'
          GROUP BY
               UNDST
          ORDER BY
               UNDST
          </cfquery>
         
          <!--- Create variable according to state --->
          <cfparam name="Today#Trim(State)#DRS" default="#GetcountsToday.NUM#">
0
 
LVL 1

Author Comment

by:JUSTICE
ID: 8137052
Pointed me to another problem I had, thx man! :)
0
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
LVL 5

Expert Comment

by:JimV_ATL
ID: 8137072
You're welcome!
0
 
LVL 8

Expert Comment

by:TallerMike
ID: 8137094
JimV_ATL, is correct, this is your fundamental problem. But I still think that this is a bad method for getting the counts. Why run 50 SQL statements? Try the following instead:

<cfquery name="GetCountsToday" dbtype="query">
  SELECT state, COUNT(*) as NUM
  FROM DRIVERSTODAY
  WHERE UNDST = IN (#Statelist#)
</cfquery>
         
Now you can loop through and create your variables here:
<cfset stateCountStruct = structNew()>
<cfloop query="GetCountsToday">
  <cfscript>StructInsert(stateCountStruct,state,num)</cfscript>
</cfloop>

Then you can reference the variables like this:

<cfoutput>#stateCountStruct.AZ#</cfoutput>

You may need to play with it to get it right. But only run 1 SQL statment, not 1 for each!
0
 
LVL 8

Expert Comment

by:TallerMike
ID: 8137107
Or you could keep with your same naming method like so:

<cfloop query="GetCountsToday">
 <cfset "Today#Trim(State)#DRS" = NUM>
</cfloop>

Or

<cfloop query="GetCountsToday">
 <cfscript>SetVariable("Today#Trim(State)#DRS",NUM)</cfscript>
</cfloop>
0
 
LVL 1

Author Comment

by:JUSTICE
ID: 8137161
Excellent, I will try it like that TallerMike.  :)

Thanks again!
0

Featured Post

Simple, centralized multimedia control

Watch and learn to see how ATEN provided an easy and effective way for three jointly-owned pubs to control the 60 televisions located across their three venues utilizing the ATEN Control System, Modular Matrix Switch and HDBaseT extenders.

Question has a verified solution.

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

Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
Dramatic changes are revolutionizing how we build and use technology. Every company is automating, digitizing, and modernizing operations. We need a better, more connected way to work together as teams so we can harness the insights from our system…
The purpose of this video is to demonstrate how to Test the speed of a WordPress Website. Site Speed is an important metric of a site’s health. Slow site speed can result in viewers leaving your site quickly and not seeing your content. This…
The purpose of this video is to demonstrate how to set up an RSS Feed on a WordPress Website. This will be demonstrated using a Windows 8 PC. Feedburner will be used for this demonstration. Go to your WordPress login page. This will look like the…
Suggested Courses

777 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