6 Replies Latest reply on Jul 20, 2006 2:34 PM by Newsgroup_User

    Getting totals in a CFC

    Level 7
      I am trying to use two SQL calls and set a couple variables to gather the
      balance of projects left for a member.

      I first find out how many projects they have total for their account.
      (available)
      Then I use the recordcount to find out how many projects they current have
      active. (used)

      Then I subtract the active projects from the total and get the
      'projectsleft'

      What did I do wrong in this component and how can I output the final number
      (projectsleft)

      I want to create a component on another page and use the 'projectsleft'.

      <cfcomponent>
      <cffunction name="Balance" access="remote" returntype="numeric">
      <cfquery name="UsedProjects" datasource="#Request.MainDSN#">
      Select title
      FROM projects
      Where dirname = '#SESSION.MM_username#'
      </cfquery>
      <cfset used = #UsedProjects.RecordSet#>
      <cfquery name="AvailProjects" datasource="#Request.MainDSN#">
      Select projects
      FROM Members
      Where username = '#SESSION.MM_username#'
      </cfquery>
      <cfset available = #AvailProjects.project#>
      <cfset projectsleft = available - used>
      <cfreturn projectsleft>
      </cffunction>
      </cfcomponent>


      Thanks!

      --
      Wally Kolcz
      Developer / Support


        • 1. Re: Getting totals in a CFC
          Dan Bracuk Level 5
          What did you do wrong? This
          <cfset used = #UsedProjects.RecordSet#>
          does not give a recordcount.

          and this
          <cfset available = #AvailProjects.project#>
          does not give you a number.

          Plus if I remember correctly, cfc's do not recognize session variables.

          That's the stuff that is definitely wrong. You are also being inefficient. You should be able to get the answer in one query.
          • 2. Re: Getting totals in a CFC
            Teddy P
            CFCs do recognize session variables, unless you are storing the CFC in the application as an object.

            • 3. Re: Getting totals in a CFC
              Level 7
              My CFCs recognize sessions.

              I noticed the error on my first one. It should be RecordCount, not
              RecordSet. I apologize, it was late.

              Why wouldn't my AvailProjects.projects give a number? It is a number in the
              'projects' column set to INT. Wouldn't that produce a number?

              I am not that great at SQL queries. What should I be looking for to get the
              result from one SQL query?

              Help me be effective.


              • 4. Re: Getting totals in a CFC
                Level 7
                I appreciate your submission to my question, but I am asking cause I don't
                know. I apologize for my lack of knowledge. If I knew how to do it
                efficiently I would not be asking.

                Also, I know the item is wrong. That is why I am asking how make it right.

                "Dan Bracuk" <webforumsuser@macromedia.com> wrote in message
                news:e9o4fe$aqf$1@forums.macromedia.com...
                > What did you do wrong? This
                > <cfset used = #UsedProjects.RecordSet#>
                > does not give a recordcount.
                >
                > and this
                > <cfset available = #AvailProjects.project#>
                > does not give you a number.
                >
                > Plus if I remember correctly, cfc's do not recognize session variables.
                >
                > That's the stuff that is definitely wrong. You are also being
                > inefficient.
                > You should be able to get the answer in one query.
                >


                • 5. Re: Getting totals in a CFC
                  Dan Bracuk Level 5
                  Without knowing your table structure, it's hard to give the exact answer. I thought your 2nd query would retun a list of projects. If you are storing the actual number instead of calculating it, you could have problems keeping it up to date.

                  In any event, the following thread shows how to do math by selecting from sub-queries. There are probably better ways for your particular situation, but it might give you some ideas.

                  http://www.adobe.com/cfusion/webforums/forum/messageview.cfm?catid=3&threadid=1174212&ente rthread=y
                  • 6. Re: Getting totals in a CFC
                    Level 7
                    Ok, the only column I need from the 'members' table is 'projects' (which is
                    a number)
                    I then need to count the projects currently in the 'project' table based on
                    the column 'dirname' which equals #Session.MM_username#
                    I need to take the number from the 'project' column from the 'members' table
                    and subtract from it the total number from the count of all projects from
                    the 'project' table and output that number to variable which can be passed
                    on to the requesting page.

                    Does that help?