# Thread: Doing Math, is it possible?

1. Registered User
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. Registered User
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. Registered User
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. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
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

6. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
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?

8. Registered User
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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
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?

12. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
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

14. Registered User
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
•