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...

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.

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....

What version of mysql are you on? If you are on 5.x you can create a stored procedure for this.

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

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?

"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?

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

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....

basically, you would say 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
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
is that more along the lines of what you were asking?

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....

i'm sorry, i did not say it was not possible

we're still trying to understand you

or, at least, i am

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?