Skip to main content
A
Anonymous
March 13, 2011
Answered

Storing variables names in a database

  • March 13, 2011
  • 3 replies
  • 1234 views

Hi;

I'm storing some calculations in a database  table and need to retrieve them and run them in a query, one of them  needs to access the value of an argument passed to the function  performing the query/calculation. Problem is I'm not sure how to  evaluate the variable when it is retrieved - it's not getting processed.

Any ideas?

here is the functon:

<cffunction
 name="fnShowliveSurcharge" access="public" output="true" 
returntype="any" hint="makes a calculation based on a formulae" >

        <cfargument name="avgCost" required="true" />

    

        <cfquery name="qryGetAlloys" datasource="#variables.dsn#" result="qryGetAlloys_results" >

            select * from nas_alloys;

        </cfquery>

        

        <cfquery name="qryGetGrades" datasource="#variables.dsn#" result="qryGetGrades_results" >

            select * from nas_grades order by id;

        </cfquery>

        

        <cfloop query="qryGetGrades">

            <cfloop query="qryGetAlloys">

                <cfquery name="qryGetSurcharge" datasource="#variables.dsn#" result="qryGetSurcharge_results">    

                    select 

                        #qryGetAlloys.formulae# as myValue

                    from nas_alloys a left join nas_triggers t on t.alloyid = a.id

                    left join nas_astm astm on astm.alloyid = a.id

                    left join nas_estimatedprice ep on ep.alloyid = a.id

                    where astm.gradeid = '#qryGetGrades.Id#'

                    and a.id = '#qryGetAlloys.Id#';

                </cfquery>

            </cfloop>

        </cfloop>

        

    </cffunction>

the second line of the query is inserting

((#arguments.avgCost# - t.value) * (astm.astm/100) * 1.2)

from the nas_alloys table, however the query ends up looking like;

select ((#arguments.avgCost# - t.value) * (astm.astm/100) * 1.2) as myValue
from nas_alloys a left join nas_triggers t on t.alloyid = a.id
left join nas_astm astm on astm.alloyid = a.id
left join nas_estimatedprice ep on ep.alloyid = a.id
where astm.gradeid = '1'
and a.id = '1';
                   

instead of:

select ((2.2289 - t.value) * (astm.astm/100) * 1.2) as myValue from nas_alloys a left join nas_triggers t on t.alloyid = a.id
left join nas_astm astm on astm.alloyid = a.id
left join nas_estimatedprice ep on ep.alloyid = a.id
where astm.gradeid = '1'
and a.id = '1';
                   

Any thoughts on how to correct this?

-thanks

-sean

This topic has been closed for replies.
Correct answer Dan_Bracuk

Conditional logic can be done in sql a number of ways.  One way that works with just about anything is a case construct.

select case when something then this

when something else then that

else whatever

end fieldname.

Then there are functions like ifnull, coalesce, decode, etc.  These are db specific.  I don't use MySql so I can't tell you what functions it has.

3 replies

D
Dan_Bracuk
Inspiring
March 14, 2011

Adam gave you good advice.  I am merely going to answer your specific question.

If you are storing variable names in a db, and you want coldfusion to process them, you have to do this.

1.  run your query

2.  create a coldfusin file containing the query results

3.  cfinclude the file.

I don't think Owain's suggestion will work.

A
Anonymous
March 17, 2011

Hi guys;

thanks, yes Adam is technically correct, though a query like that might be a little beyond me... can you even do conditional sql in mysql? [i.e. if(table.id = '1'}( (select * from anothertable))}else{(select avg(somethingelse) from yetAnotherTable))} ] ??

one of the problems with the variable is that ~sometimes~ it needs to be the average of a select from another table.

select avg(cost/2204.6) as averageCost from nas_cost 
where cost != '0' and date >= '#variables.firstOfMonth#' 
and date <= '#variables.lastOfMonth#' order by date;

- there are currently 6 or 7 different calculations [one for each alloy]

- the calculations can change from time to time dependent on the alloy composition and quality [wonderful!]

hmmm guess I have some mysql docs to run through.

-sean

D
Dan_BracukCorrect answer
Inspiring
March 17, 2011

Conditional logic can be done in sql a number of ways.  One way that works with just about anything is a case construct.

select case when something then this

when something else then that

else whatever

end fieldname.

Then there are functions like ifnull, coalesce, decode, etc.  These are db specific.  I don't use MySql so I can't tell you what functions it has.

A
Adam Cameron.
Inspiring
March 14, 2011

A rule of thumb that I have is if I find myself looping over one query to get joining info for another query embedded with the loop: I am not thinking things through properly.  And to have the inner CFQUERY within two nested query loops?  Yikes.  Other than the variation of formula here, isn't all that doable with one query?  Even if not, all you're using CF for here is manipulating data, which is the job of the DB, not CF.  CF is for taking the resultant data from the DB and using it to generate text to return to the web server (you know: making web pages).

Speaking of the formulas... how many have you got?  Rather than storing your logic as data, I'd be storing it as... well... logic.  Either have a suite of procs, or one proc with a switch based on (whatever) which calls in a different formula as is required.  All CF should be doing is calling a proc.

--

Adam

Owainnorth
Owainnorth
Inspiring
March 14, 2011
I'm not sure how to  evaluate the variable when it is retrieved

Have you tried using the evaluate() function?

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices