16 Replies Latest reply on Oct 22, 2007 1:10 PM by cf_dev2

    dynamic query name

    fyrehed Level 1
      Hello,

      I sure home someone can help me with this.

      I am trying to run dynamic loops and am stumped with how to call a variable name.

      As you can see, calling the variable names just isnt going to work out the way I am trying to do this. Can someone steer me in the correct direction?

      thanks,

      Matt
        • 1. Re: dynamic query name
          Dan Bracuk Level 5
          There is a more efficient way to do this. Instead putting your second query into a loop, just run it once and use cold fusion's valuelist function.

          Details are in the cfml reference manual. If you don't have one, the internet does.
          • 2. Re: dynamic query name
            cf_dev2 Level 1
            quote:


            <cfquery datasource="#my.dsn#" name="get#getLeagues.id#">
            SELECT All, my, info
            Where leagueID='#Getinums.leagueid#'
            </cfquery>



            You're not going to get far without a FROM clause in the query.

            What are you trying to output and why do you think you need cfloop? I suspect a simple INNER JOIN with <cfoutput> and "group" could do the job here.
            • 3. Re: dynamic query name
              fyrehed Level 1
              Excuse me,

              I pulled out a ton of code, trying to simplify. I obviously took out to much (from clause in query). If you will note, I did use the valuelist to pull the id's out of the first query.

              Can anyone tell me if there is a way to use a variable for a query name and call that query with fields? This is actually what I am trying to find out.

              Thanks in advance.
              • 4. Re: dynamic query name
                Grizzly9279 Level 1
                I haven't tested this, but I'm fairly sure something like the following should accomplish exactly what you're trying to do.

                • 5. Re: dynamic query name
                  cf_dev2 Level 1
                  quote:

                  fyrehed wrote:
                  I pulled out a ton of code, trying to simplify
                  ....



                  That's appreciated. But it was not obvious that the omission was deliberate and truthfully your code sample is confusing because few of the variables and query names match up.

                  For example, you're storing a list of the ID's from the "getnums" query here
                  ........<cfset leaguelist=valuelist(getnums.id)>

                  Then you're looping through a 2nd query named "GetInfo". Where does that query come from?
                  ........<cfloop query="Getinfo">

                  Inside the loop the code references a 3rd query named "getLeagues"
                  ........<cfquery datasource="#my.dsn#" name="get#getLeagues.id#">

                  Then a 4th query is used in the where clause
                  ........Where leagueID='#Getinums.leagueid#'


                  So its unclear how many you have and which ones you're actually using. Honestly, the cfloops seem like an over complication. Unless you have another reason for using them, you could probably do the same thing more simply by using one cfquery with a JOIN and <cfoutput> with "group". That said, one problem that jumps out at me is here

                  quote:

                  <cfloop from"1" to="#listlen(leaguelist)#" index="i">
                  <table>
                  <cfloop query="get#i#">
                  <cfif get#i# currentrow mod 2 eq 0>



                  The queries were named using #getLeagues.id#. So you must use the id value, not the list index #i#. Also, you don't need to use the query name inside your query loop.

                  <cfloop list="#leagueList#" index="currentID">
                  <table>
                  <cfoutput query="get#currentID#">
                  <cfif currentrow mod 2 eq 0>
                  ....


                  • 6. Re: dynamic query name
                    fyrehed Level 1
                    Grizzly...


                    Thanks for sticking to topic.
                    • 7. Re: dynamic query name
                      Grizzly9279 Level 1
                      RE: fyrehed

                      No problem :) Moving forward, please keep in mind that if you're going to ask for help and post sample code, you're going to get better results from this community if you try your best to make sure your code is clear of errors and/or typos. The folks here that try to help are easily confused/side-tracked by syntactical/symantical errors, such as those cf_dev2 pointed out.

                      In general, you'll get better (more effective) help from these forums if you take the time to describe your problem as clearly as you possibly can, and with error-free code examples.

                      Anyways, best of luck! I hope you got what you needed.
                      • 8. dynamic query name
                        cf_dev2 Level 1
                        > Thanks for sticking to topic

                        fyrehed,

                        Responding to the code you posted IS relevant. The values used to name your queries will directly effect what code is necessary to retrieve those queries.

                        If you'd actually read the response you would have seen a suggestion on how to fix the problem. You seem to have ignored both that and a suggestion on how to improve the code.

                        Grizzly9279's suggestion was good, but as I mentioned before it uses the list index and not the values. Based on the psuedo-code you posted that probably will not work.

                        The comments made here are not personal. People volunteer their time trying to help others solve problems. If you don't want to take the advice given, that's your choice.

                        Good luck




                        • 9. Re: dynamic query name
                          cf_dev2 Level 1
                          Grizzly9279 wrote:
                          > you're going to get better results from this community if you
                          > try your best to make sure your code is clear of errors and/or typos.

                          Well said

                          Grizzly9279 wrote:
                          > <cfloop from="1" to="#listlen(leaguelist)#" index="i">
                          > <cfset dynamicQueryResult = evaluate( "get" & i )>

                          Looks like you might have gotten snagged by the psuedo-code too ;). If #getLeagues.id# is used to name the queries, they must use the list value to retrieve the queries, not the list index (#i#). Though technically evaluate should not be necessary here since cfloop's "query" accepts a string. (Not tested)

                          <cfloop query="get#listGetAt(leaguelist, i)#">
                          ...

                          Cheers
                          • 10. Re: dynamic query name
                            Grizzly9279 Level 1
                            RE: cf_dev2

                            Yeah, I wasn't going to bother trying to figure out the correct variable names or field names, since it was pretty clear that the code fyrehed posted was incomplete, and not at all close to a "working" set of code. fyrehed's example is enough to at least understand the general idea of what he's trying to do.

                            The example I posted wasn't really intended to be a "working" example either (as I certainly didn't test it!). My hope was that it would at least get fyrehed thinking about a minor adjustment he could make to his approach that might help get him on the right path. Sometimes...a subtle change in approach can help someone think something through more clearly.

                            In any event, I think it was enough to get fyrehed "out of the weeds" so to speak. At least I hope so!
                            • 11. Re: dynamic query name
                              fyrehed Level 1
                              Grizzly,

                              Yes, this was enough to get me out of the weeds. Granted, it would have been better for me to not chop up my code and give you guys the "whole thing" as convoluted as it was. My question obviously wasn't communicated as well as I needed it to be:

                              "I am trying to run dynamic loops and am stumped with how to call a variable name."

                              I truly appreciate the effort all have done to come to my aid and my question was answered. I am out of the weeds.

                              Thanks again!

                              Matt
                              • 12. Re: dynamic query name
                                Grizzly9279 Level 1
                                That's great! I'm glad we could be of service Matt. Take care.

                                -Grizzly
                                • 13. Re: dynamic query name
                                  cf_dev2 Level 1
                                  Grizzly9279
                                  > enough to at least understand the general idea of what he's trying to do.
                                  While I suspected what he was trying to do, it seemed like an over complication. <shrug> To each his own :)

                                  >Sometimes...a subtle change in approach can help someone think something through more clearly.
                                  True enough .. assuming they're receptive.

                                  Cheers
                                  • 14. Re: dynamic query name
                                    Grizzly9279 Level 1
                                    RE: cf_dev2

                                    I completely agree that the original approach is *probably* overly complicated (I say probably because we really don't know what the rest of Matt's code is doing; perhaps there is some other hack or limitation he's working around that caused him to take this approach?). I too felt that this could/would be better accomplished from both a performance and maintainability perspective, with a "grouped" ColdFusion query approach.

                                    However, I also considered two things:

                                    1) Matt wasn't specifically asking for us to tell him how to completely re-engineer his approach, even if we thought there were better ways to go about it.

                                    2) It looked to me like Matt was getting a little tired of people "attacking" the code he posted, and not really providing any answers to his immediate problem. (Again, I know YOU weren't attacking cf_dev2, you were only trying to help, but I think it is possible that this was Matt's perception)

                                    So with those things considered, I figured I could be perhaps help if I took a blind stab at a slightly different approach for Matt to chew on. In the end, I think this proved to be a good judgement call.

                                    I think it's easy to get hung up on the details...when sometimes, all someone really needs is a nudge in the right direction.
                                    • 15. Re: dynamic query name
                                      fyrehed Level 1
                                      grizzly and cf_dev2:

                                      I agree totally with both of you. It would have been easier if I posted all my code. Most of the time I do. There are plenty of times when I have posted code that I appreciate people, like yourselfs, helping clean the whole page with ugly code. I have even asked for it on occasion. This was not one of them, I was in such a big hurry that I short changed your ability to "tear the onion down to the bulb". To be honest, I just was in such a flipping hurry that I probably sounded a bit miffed and I am sorry for that. ( One of, my many, bad traits.) You are both great and I truly appreciate your efforts on my behalf. I recognize that more code is better and will be more careful next time.

                                      Matt
                                      • 16. Re: dynamic query name
                                        cf_dev2 Level 1
                                        Grizzly9279,

                                        Good responses. My main point in commenting was that while I did give suggestions, I also provided an answer as well. Yet it seemed to be overlooked. But that's why its good to have a variety of people responding to questions on the forums. Different approaches work for different situations. In this case it was yours :)


                                        Matt,

                                        Fair enough. Though some people won't, if I see potential problems I'm always going to mention them. Its up to the OP whether or not they wish to explore them. That said, I always try to answer the question that was asked as well. We all understand that sometimes we have to code in less than ideal conditions. So if its ever one of those cases, feel free to say "okay. thanks, but .. uhh... no" ;) We won't take offense.

                                        Btw, from my own questions, I've found a good way to stave off "helpful suggestions" is to preface my question with "I am aware there's a better way, but due to (whatever) constraints I have to work with (..this..)" In most cases it does the trick

                                        Cheers