Began learning PHP and MySQL yesterday and also have handled to produce two tables, place rows after which display that data on the web site using many different types. Now I have to perform a calculation according to data within the two tables and write the end result to among the tables.

I am trying to puzzle out how you can perform a formula for any row having a date in table A using a variety of values connected with a variety of dates in table B. The 2 dates have been in the format YYYY-MM-DD, however the days mostly don't match, and so i need match around the month.

Here's the 2 tables I've:

Table A (user)

+----+----------+------------+-------------+
| id | username | start-date | bench-value |
+----+----------+------------+-------------+
|  1 | tim      | 2010-03-04 |             |
+----+----------+------------+-------------+
|  2 | jim      | 2010-05-30 |             |
+----+----------+------------+-------------+
|  3 | fred     | 2010-06-12 |             |
+----+----------+------------+-------------+
|  4 | sam      | 2010-08-16 |             |
+----+----------+------------+-------------+
|  5 | jane     | 2010-10-21 |             |
+----+----------+------------+-------------+
|  6 | ella     | 2010-10-21 |             |
+----+----------+------------+-------------+
|  7 | bob      | 2011-01-24 |             |
+----+----------+------------+-------------+

Table B (benchmark)

+----+------------+---------+
| id | start-date |  value  |
+----+------------+---------+
|  1 | 2010-01-31 | 1173.19 |
+----+------------+---------+
|  2 | 2010-02-28 | 1199.85 |
+----+------------+---------+
|  3 | 2010-03-31 | 1264.91 |
+----+------------+---------+
|  4 | 2010-04-30 | 1263.43 |
+----+------------+---------+
|  5 | 2010-05-31 | 1211.36 |
+----+------------+---------+
|  6 | 2010-06-30 | 1187.32 |
+----+------------+---------+
|  7 | 2010-07-31 | 1218.30 |
+----+------------+---------+
|  8 | 2010-08-31 | 1207.96 |
+----+------------+---------+
|  9 | 2010-09-30 | 1272.12 |
+----+------------+---------+
| 10 | 2010-10-31 | 1280.27 |
+----+------------+---------+
| 11 | 2010-11-30 | 1275.60 |
+----+------------+---------+
| 12 | 2010-12-31 | 1346.45 |
+----+------------+---------+
| 13 | 2011-01-31 | 1337.07 |
+----+------------+---------+
| 14 | 2011-02-28 | 1338.37 |
+----+------------+---------+
| 15 | 2011-03-31 | 1349.14 |
+----+------------+---------+

And here's a good example of what I am attempting to achieve:

tim's current bench value today = the sum of the: (first(benchmark.value)/latest(benchmark.value))for each month from the first one to the most recent month inclusive First date = 2010-03 that is id 3 = 1264.91 Latest date = 2011-03 that is id 15 = 1349.14 (this really is always the final row like me attempting to calculate on "today" and absolutely nothing later on)

1/(first/latest)   =1/(1264.91/1349.14) = 1.0666    [this is bench.id=3]
...now iterate...
1/(next/latest)    =1/(1263.43/1349.14) = 1.0678    [bench.id=4]
1/(next/latest)    =1/(1211.36/1349.14) = 1.1137    [bench.id=5]
1/(next/latest)    =1/(1187.32/1349.14) = 1.1363    [bench.id=6]
1/(next/latest)    =1/(1218.30/1349.14) = 1.1074    [bench.id=7]
1/(next/latest)    =1/(1207.96/1349.14) = 1.1169    [bench.id=8]
1/(next/latest)    =1/(1272.12/1349.14) = 1.0605    [bench.id=9]
1/(next/latest)    =1/(1280.27/1349.14) = 1.0538    [bench.id=10]
1/(next/latest)    =1/(1275.60/1349.14) = 1.0577    [bench.id=11]
1/(next/latest)    =1/(1346.45/1349.14) = 1.0020    [bench.id=12]
1/(next/latest)    =1/(1337.07/1349.14) = 1.0090    [bench.id=13]
1/(next/latest)    =1/(1338.37/1349.14) = 1.0080    [bench.id=14]
...and finish up...
1/(current/latest) =1/(1349.14/1349.14) = 1.0000    [bench.id=15]

Total = 13.7997 = 1.0666 + 1.0678 + 1.1137 + 1.1363 + 1.1074 + 1.1169 + 1.0605 + 1.0538 + 1.0577 + 1.002 + 1.009 + 1.008 + 1

And So I would then wish to write that result to Table A, giving me:

Table A (user)

+----+----------+------------+-------------+
| id | username | start-date | bench-value |
+----+----------+------------+-------------+
|  1 | tim      | 2010-03-04 |     13.7997 |
+----+----------+------------+-------------+

Because this is an iterative process it might be a significantly shorter calculation for any user like 'bob' who began this year-01.

I'd like to have the ability to do that every 4 several weeks to create termly stats to ensure that someone like user tim could be calculated such as this (the first search to obtain the first date will have to consider on the 4 month period):

1/(first/latest)   = 2010-03 = 1/(1264.91/1349.14) = 1.0666
1/(next/latest)    = 2010-07 = 1/(1218.30/1349.14) = 1.1074
1/(next/latest)    = 2010-11 = 1/(1275.60/1349.14) = 1.0577
1/(current/latest) = 2011-03 = 1/(1349.14/1349.14) = 1.0000

Total = 1.0666 + 1.1074 + 1.0577 + 1 = 4.2317

That major issues I am getting are two parts: 1. ways to use the user.start-date value for every user to choose the very first(benchmark.value) based ont he year and also the month (day is trivial). 2. how you can iteratively calculate the formula up to the most recent value within the bench table - in the finish of april, a brand new row with id=16 could be added and when this were run then your April value would end up being the last value utilized in the calculation.

As I am learning SQL And PHP at this time I am unsure which areas of this method ought to be completed in SQL and which in PHP.

Every help could be greatly appreciated as I am going to figure this out.

That major issues I am getting are two parts:

  1. ways to use the user.start-date value for every user to choose the first(benchmark.value) in line with the year and also the month (day is trivial).
  2. how you can iteratively calculate the formula up to the latest value within the bench table - at the finish of april, a brand new row with id=16 could be added and when this were run then your April value would end up being the last value utilized in the calculation.

As I am learning SQL And PHP at this time I am unsure which areas of this method ought to be completed in SQL and which in PHP.

Every help could be greatly appreciated as I am going to figure this out.

Only for reference, I have been reading through:

There's almost an excessive amount of info available, so some led advice could be realy appreciated. Many thanks.