How to add days in MySQL on date column?

Question

I have a simple table in database. The table has date_start and added_days column.This is the table with normal SELECT:

id    date_start    added_days
 1    2017-10-10             2
 2    2017-10-10             4
 3    2017-10-10             6
 4    2017-10-10             6
 5    2017-10-10             3

I know how to add days to date_start column. But, how to make the date_start value is the same as the previous date_start?

My current SELECT:

SELECT DATE_ADD(date_start, INTERVAL added_days DAY) AS 'new_date',added_days FROM test

id      new_date    added_days
 1    2017-10-12             2
 2    2017-10-14             4
 3    2017-10-16             6
 4    2017-10-16             6
 5    2017-10-13             3

My expected output:

id      new_date    added_days
 1    2017-10-10             2
 2    2017-10-12             4
 3    2017-10-16             6
 4    2017-10-22             6
 5    2017-10-28             3 

fiddle

note:
I also accept PHP solution, if it's imposibble in MySQL

Answer 1

The concept can be done in the same way this answer needed. I use MySQL variables like an in-line program.

select
          @lastDate := date_add( if( @lastNumDays = 0, date_start, @lastDate ), INTERVAL @lastNumDays DAY ) as new_Date,
          @lastNumDays := added_days as added_days
       from
          YourTable ,
          ( select @lastDate := '2017-01-01',
                   @lastNumDays := 0 ) SQLVars;

The "SQLVARS" alias is used to in-line create the variables... a bogus place-holder date, and the last number of days defaulted to 0 so no days are actually added.

The select fields will use -- on the first-pass the actual first date, add zero days to it as the start, THEN, set that value into the @lastDate variable to be used on the next record. The NEXT field assigns the days into the @lastNumDays variable to be used for the next record's "Date_Add()" process.

My working solution can be found at SQL Fiddle demo

CLARIFICATION.

Two variables to preserve what the "last date" was, and one for the number of days to advance AFTER whatever the current record is. So, for processing the rows in order, think of it as...

@lastDate = '2017-01-01' -- acting as just a place-holder declared variable @lastNumDays = 0 -- another place-holder for subsequent adding to base date

First Record, the IF() is applied, @lastNumDays = 0, so it puts the "Date_Start" into the @lastDate variable but becomes the "New_Date" column. Next, takes the added_Days and stores into @lastNumDays variable and becomes the "added_Days" column.

So at the end of the first row being processed per your sample data,

@lastDate = '2017-10-10' and
@lastNumDays = 2

Now, we get to the second row of your data. the IF() has a value in @lastNumDays and uses the @lastDate instead and adds 2 days to it. So now

@lastDate = '2017-10-12'
@lastNumDays = 4

Next record... applies the adding 4 days to the 10/12 date and becomes

@lastDate = '2017-10-16'
@lastNumDays = 6

etc.. Now, the fact that you have a fixed column value for the start date could be simplified by just pre-defining that column as the starting point, but I don't know enough of your environment, temp / permanent table, etc. to make that call, just enough to get the answer you were looking for.