Skip to main content
L
LenderDesign
Known Participant
January 8, 2017
Question

Selecting and outputting certain columns and rows from a database table

  • January 8, 2017
  • 1 reply
  • 481 views

I would like to display a monthly loan payment chart/table that shows payment amounts for various interest rates and loan amounts. The output table will have 9 columns (the first column will be the user-defined loan amounts) and 32 rows (the top row being the user-defined interest rates). I have created a master database table that is 30 columns by 992 rows and includes all of the payment amounts:

I want to allow the user to select a starting interest rate and the increments in which it increases. For example, starting rate of 4.000% and increase increments of .125% (so the top row/header will be 4.000% | 4.125% | 4.250% | 4.375% | 4.500% | 4.625% | 4.750% | 4.875%). Additionally, the user would define the starting loan amount and the increments in which it increases (so the left column might be $200,000, $210,000, $220,000 and so on). I'm trying to accomplish an output like this:

I have been using CF for a number of years, but my knowledge is limited to more simple functions. I started putting together a query, etc. and realized that this extends beyond my knowledge. I am collecting the variable data from the user in a form. I can define the 8 interest rate columns and 31 loan amount rows, but I'm not sure what to do after that. Here is what I started with but wasn't getting the results I'm looking for:

<cfset IntRate1 = #FORM.StartRate#>

<cfset IntRate2 = #IntRate1# + #FORM.RateIncr#>

<cfset IntRate3 = #IntRate2# + #FORM.RateIncr#>

<cfset IntRate4 = #IntRate3# + #FORM.RateIncr#>

<cfset IntRate5 = #IntRate4# + #FORM.RateIncr#>

<cfset IntRate6 = #IntRate5# + #FORM.RateIncr#>

<cfset IntRate7 = #IntRate6# + #FORM.RateIncr#>

<cfset IntRate8 = #IntRate7# + #FORM.RateIncr#>

<cfset LoanAmt1 = #FORM.StartLoanAmt#>

<cfset LoanAmt2 = #LoanAmt1# + #FORM.LoanAmtIncr#>

<cfset LoanAmt3 = #LoanAmt2# + #FORM.LoanAmtIncr#>

<cfset LoanAmt4 = #LoanAmt3# + #FORM.LoanAmtIncr#>

...

<cfset LoanAmt31 = #LoanAmt30# + #FORM.LoanAmtIncr#>


<cfset List = "'#LoanAmt1#','#LoanAmt2#','#LoanAmt3#','#LoanAmt4#',...'#LoanAmt31#'">

<CFQUERY NAME="tabledata" DATASOURCE="dbds" username="username" password="passw">

  SELECT LoanAmt, #IntRate1#, #IntRate2#, #IntRate3#, #IntRate4#, #IntRate5#, #IntRate6#, #IntRate7#, #IntRate8#

  FROM table

  WHERE LoanAmt IN (#PreserveSingleQuotes(List)#)

</CFQUERY>

I'm having trouble getting the loan amount to output since I am using the variables for selecting the column names. I'm hoping there is a simple way to accomplish what I'm trying to do. Any assistance will be appreciated.

    This topic has been closed for replies.

    1 reply

    WolfShade
    WolfShade
    Legend
    January 9, 2017

    I hate to sound trite, especially to someone who isn't as up-to-speed on the more advanced features of ColdFusion.  But I would be remiss if I did not mention that it is a very bad idea to use variables for column names in queries.  At the most, a variable should be used as a comparator in a query (as in a WHERE clause), or similar, and always, always, always use CFQUERYPARAM to guard against SQL injection.

    I wish I had better news, or some sort of suggestion on how to achieve what you seek.  But I had to jump in and say that what you are proposing (especially if personally identifiable information is involved) could have disasterous implications if any script-kiddie or serious hacker were to learn about the way your proposed system is set up.

    V/r,

    ^_^

    L
    LenderDesignAuthor
    Known Participant
    January 9, 2017

    Thank you for the info. I appreciated your response. The table includes calculated loan payments based on a particular interest rate and loan amounts. There is no personal information included in the table and this would be the only table using variable column names. Is it less concerning if there is no sensitive data in the table?

    To explain further, there are 29 columns of data in the table, but the output can only accommodate 8 columns, so somehow I need to allow the end user to choose which columns they want to be displayed. The user would choose the first/starting column and then the next 7 columns will be selected in order following the starting column. It seems it might be better to rethink my solution...

    WolfShade
    WolfShade
    Legend
    January 9, 2017

    Hello, LenderDesign​,

    It is always more critical to guard against SQL-injection when PII is involved; but don't think that just because there isn't PII that you should ignore prudent security standards.  With the type of setup you describe, it would be nothing for a malicious actor to delete every table of data, or other such event, especially if you don't have a generic error template with a vague message indicating that something is wrong (allowing error output to be viewed by the user can tell a great deal about your environment and architecture, opening more attack vectors.)

    There may be a more secure way of doing what you want, but I'm not sure what that would be.

    V/r,

    ^_^

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