Refer back to selected data

h4x0rmx

I have a simple table with dates a (stock) prices. I have to do a bunch of operations with this data and I was wondering how much I can do in the database before php handles everything.

This some sample data:

DATES		DAYS	SUMS
3/1/2011	35	
2/28/2011	34	
2/25/2011	31	
2/24/2011	30	
2/23/2011	29	159
2/22/2011	28	152
2/18/2011	24	142
2/17/2011	23	134
2/16/2011	22	126
2/15/2011	21	118
2/14/2011	20	110
2/11/2011	17	103
2/10/2011	16	96
2/9/2011	15	89
2/8/2011	14	82
2/7/2011	13	75
2/4/2011	10	68
2/3/2011	9	61
2/2/2011	8	54
2/1/2011	7	47
1/31/2011	6	40 ...
1/28/2011	3	33 (3+6+7+8+9)
1/27/2011	2	26 (2+3+6+7+8)
1/26/2011	1	19 (1+2+3+6+7)

Dates are in the database, I want the days column to be created with my select statement. It represents the number of days + 1 since the first selected date. The sums column is the sum of the current DAY value plus the next five days.

Is it possible to do something like this with a select statement in MySQL?

I'll really appreciate your help!

bradgrafelman

Here's probably what I would do:

Perform the SELECT query (minus the DAYS and SUMS columns from above) and LIMIT it to the first match.
Grab that row and store it in PHP (e.g. in an array) after adding the two missing columns from above; DAYS can just be set to 1, while SUM can be initialized to 0 or left empty (it'll be calculated later).
Perform the same SELECT query, with two changes:
1. Also SELECT the date difference between the DATE column and the value from the first row you stored in PHP.
2. Add a LIMIT 2, N (where N is the max # of records you want in total) since we already have the first row.
Store all results in the array created above.
Loop through the array in PHP to populate the SUMS column.

h4x0rmx

I just want to make sure I'm understanding your suggestion. For 3.1 I'd have something like:

SELECT date_, (DATEDIFF('2011-01-25 23:56:56', date_) *-1) AS dif
FROM historical_price
WHERE symbol_id = 2
AND date_ >= '2011-01-26'
ORDER BY date_ DESC;

The sums will have to be done with php, right? There's no way of doing something like this in the database?

bradgrafelman;10975728 wrote:
Here's probably what I would do:

Perform the SELECT query (minus the DAYS and SUMS columns from above) and LIMIT it to the first match.

Grab that row and store it in PHP (e.g. in an array) after adding the two missing columns from above; DAYS can just be set to 1, while SUM can be initialized to 0 or left empty (it'll be calculated later).

Perform the same SELECT query, with two changes:

Also SELECT the date difference between the DATE column and the value from the first row you stored in PHP.

Add a LIMIT 2, N (where N is the max # of records you want in total) since we already have the first row.

Store all results in the array created above.

Loop through the array in PHP to populate the SUMS column.