Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2005
    Posts
    6

    Unanswered: Doing Math, is it possible?

    I have a MySQL database with two tables in it, I know that I can pull the variables through php and execute the equations that I have.. but is there a way to have the equation done in the MySQL database? The output of the equation is in one of the tables but is an empty set right now, I want to be able to generate the answer to the equation through the database as opposed to through the php if at all possible? If it is, how does one do it... I am using phpAdmin to do all of the functionality... any help would be great... thank you very much...

  2. #2
    Join Date
    Mar 2004
    Posts
    480
    I would take x and multiply it by y then divide by the tangent of the hypotenuse squared. ;-)

    Seriously, what are you looking to do? Yes you can manipulate data from your mysql to your output and you are most often faster to do it in your sql query than within an application language where you loop through your sql data.

  3. #3
    Join Date
    Jan 2005
    Posts
    6
    I have to calculate an APR rate, which in itself sounds easy, but when I found out that it would require a hell of a lot more than a simple x*y/z=APR I was thinking maybe it would be easier to do within the database than it would extracting it via php code... there are a lot of processes that it needs to go through.. the calculations are as follows:

    Loan Amount * point amount = PNTAMT
    Loan Amount - PNTAMT = CLA
    [CLA(1+x)360thpower (x)] / [(1+x)360thpower - 1 ] = Payment (y)
    (IR*CLA/360)*5 = Interim Interest (II)
    II + Appraisal (350) + App Fee (795) = z
    CLA - z = LAPR


    All of these have fields in the database structured over two seperate tables... this is just some of the functionality that needs to be done....

  4. #4
    Join Date
    Mar 2004
    Posts
    480
    What version of mysql are you on? If you are on 5.x you can create a stored procedure for this.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sounds like a simple JOIN to me

    a stored procedure would be okay in that you can compile it to make it slightly more efficient and call it easily with parameter values, but otherwise it would have to do a JOIN too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2005
    Posts
    6
    I am new to using MySQL.... I understood almost nothing of what you just sated above... How would I go and do this within PhpAdmin in regards to doing the equations?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "All of these have fields in the database structured over two seperate tables"

    can we see these? and perhaps a couple of sample rows of data?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2004
    Posts
    480
    And I'll add, to save some more time, also show us sample output of what exactly you are trying to achieve with those table rows you are going to show us.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by guelphdad
    And I'll add, to save some more time, also show us sample output of what exactly you are trying to achieve with those table rows you are going to show us.
    Say! Now there's a plan!

    -PatP

  10. #10
    Join Date
    Jan 2005
    Posts
    6
    http://rates.oneagentnetwork.com/

    this page outputs the information... right now the APR rate is just manually entered through a web form.

    http://rates.oneagentnetwork.com/update.php

    The APR calculation has to be determined by a number of equations as listed above. If I can do it within the dB it seems like it would be quicker than if I extracted it out in and did it via php....

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    basically, you would say
    Code:
    select calculations
      from table1
    inner
      join table2
        on table1.pkey = table2.fkey
    where calculations and the tables that the columns are coming from are "tbd" (to be determined)

    actually, i think i might sort of understand your question

    if LoanAmount and PointAmount are two actual columns, then you could say
    Code:
    select LoanAmount * PointAmount as PNTAMT
      from ...
    but how would you then write an expression for CLA which uses PNTAMT?

    because in MySQL, you can not use the alias PNTAMT in another expression in the SELECT list

    i.e. you must say
    Code:
    select LoanAmount * PointAmount as PNTAMT
         , LoanAmount -
              ( LoanAmount * PointAmount ) as CLA
      from  ...
    is that more along the lines of what you were asking?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jan 2005
    Posts
    6
    so what you are saying is that it is not possible... the way that it is laid out.... probably easier to generate the math on the fly through php then....

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm sorry, i did not say it was not possible

    we're still trying to understand you

    or, at least, i am
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jan 2005
    Posts
    6
    Thanks....

    Let me start from the beginning....

    I have a dB with two tables in it... the first table generates the output at:

    http://rates.oneagentnetwork.com/

    the second table is an assumptions table, contains loan amount, amortization period, payment, interiminterest, Appraisal Fee, and App Fee.

    There are 6 rows, the top three have a loan amount assumption at 350k (conventional) with three different amort schedules (360, 240, 180 months), The bottom three rows have loan amounts of 500k (JUMBO) with three different amort schedules.

    The two tables need certain values to be able to calculate the APR. It needs the Rate (table 1) and Points (table 1), then based upon an assumption of 350k for loan amount, it should generate the payment amount, which in turn would have to be populated into the payment field of the dB Table.

    RIght now they are just set to 0 value and are not relational to each other. There has to be a way to get it to link up mathematically. AM I clearing things up a bit?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •