mysql - How to get the next date field from the reference field of fld_Date?

Question

Here's my query...

+----+--------+---------+---+------------+------------+
| id | idname | fldname | i | fld_Date   | next_Date  |
+----+--------+---------+---+------------+------------+
|  1 |      1 | Marlon  | 1 | 2013-06-03 | 2013-06-05 |
|  2 |      1 | Marlon  | 2 | 2013-06-05 | 2013-06-07 |
|  3 |      1 | Marlon  | 3 | 2013-06-07 | 2013-06-08 |
|  4 |      1 | Marlon  | 4 | 2013-06-08 | 2013-06-11 |
|  5 |      1 | Marlon  | 5 | 2013-06-11 | 2013-07-01 |
| 19 |      1 | Marlon  | 6 | 2013-07-01 | 2013-07-07 |
| 20 |      1 | Marlon  | 7 | 2013-07-07 | 0          |
|  6 |      2 | Dawn    | 1 | 2013-06-03 | 2013-06-06 |
|  7 |      2 | Dawn    | 2 | 2013-06-06 | 2013-06-08 |
|  8 |      2 | Dawn    | 3 | 2013-06-08 | 2013-06-11 |
|  9 |      2 | Dawn    | 4 | 2013-06-11 | 2013-06-15 |
| 10 |      2 | Dawn    | 5 | 2013-06-15 | 0          |
| 13 |      3 | Jenny   | 1 | 2013-06-14 | 2013-06-15 |
| 11 |      3 | Jenny   | 2 | 2013-06-15 | 2013-06-19 |
| 12 |      3 | Jenny   | 3 | 2013-06-19 | 2013-06-21 |
| 14 |      3 | Jenny   | 4 | 2013-06-21 | 0          |
| 15 |      4 | Rhea    | 1 | 2013-06-21 | 2013-06-22 |
| 16 |      4 | Rhea    | 2 | 2013-06-22 | 2013-06-23 |
| 17 |      4 | Rhea    | 3 | 2013-06-23 | 2013-06-24 |
| 18 |      4 | Rhea    | 4 | 2013-06-24 | 0          |
| 22 |      5 | Chrisha | 1 | 2013-07-07 | 2013-09-07 | <
| 23 |      5 | Chrisha | 2 | 2013-07-08 | 2013-09-07 | <
| 24 |      5 | Chrisha | 3 | 2013-07-11 | 2013-09-07 | <
| 25 |      5 | Chrisha | 4 | 2013-07-16 | 2013-09-07 | <
| 26 |      5 | Chrisha | 5 | 2013-07-17 | 2013-09-07 | <
| 27 |      5 | Chrisha | 6 | 2013-07-22 | 2013-09-07 |
| 21 |      5 | Chrisha | 7 | 2013-09-07 | 0          |
+----+--------+---------+---+------------+------------+

Here's the output I wanted.... (differences from above marked with <)

+----+--------+---------+---+------------+------------+
| id | idname | fldname | i | fld_Date   | next_Date  |
+----+--------+---------+---+------------+------------+
|  1 |      1 | Marlon  | 1 | 2013-06-03 | 2013-06-05 |
|  2 |      1 | Marlon  | 2 | 2013-06-05 | 2013-06-07 |
|  3 |      1 | Marlon  | 3 | 2013-06-07 | 2013-06-08 |
|  4 |      1 | Marlon  | 4 | 2013-06-08 | 2013-06-11 |
|  5 |      1 | Marlon  | 5 | 2013-06-11 | 2013-07-01 |
| 19 |      1 | Marlon  | 6 | 2013-07-01 | 2013-07-07 |
| 20 |      1 | Marlon  | 7 | 2013-07-07 | 0          |
|  6 |      2 | Dawn    | 1 | 2013-06-03 | 2013-06-06 |
|  7 |      2 | Dawn    | 2 | 2013-06-06 | 2013-06-08 |
|  8 |      2 | Dawn    | 3 | 2013-06-08 | 2013-06-11 |
|  9 |      2 | Dawn    | 4 | 2013-06-11 | 2013-06-15 |
| 10 |      2 | Dawn    | 5 | 2013-06-15 | 0          |
| 13 |      3 | Jenny   | 1 | 2013-06-14 | 2013-06-15 |
| 11 |      3 | Jenny   | 2 | 2013-06-15 | 2013-06-19 |
| 12 |      3 | Jenny   | 3 | 2013-06-19 | 2013-06-21 |
| 14 |      3 | Jenny   | 4 | 2013-06-21 | 0          |
| 15 |      4 | Rhea    | 1 | 2013-06-21 | 2013-06-22 |
| 16 |      4 | Rhea    | 2 | 2013-06-22 | 2013-06-23 |
| 17 |      4 | Rhea    | 3 | 2013-06-23 | 2013-06-24 |
| 18 |      4 | Rhea    | 4 | 2013-06-24 | 0          |
| 22 |      5 | Chrisha | 1 | 2013-07-07 | 2013-07-08 | <
| 23 |      5 | Chrisha | 2 | 2013-07-08 | 2013-07-11 | <
| 24 |      5 | Chrisha | 3 | 2013-07-11 | 2013-07-16 | <
| 25 |      5 | Chrisha | 4 | 2013-07-16 | 2013-07-17 | <
| 26 |      5 | Chrisha | 5 | 2013-07-17 | 2013-07-22 | <
| 27 |      5 | Chrisha | 6 | 2013-07-22 | 2013-09-07 |

| 21 |      5 | Chrisha | 7 | 2013-09-07 | 0          |
+----+--------+---------+---+------------+------------+

My problem is every time I insert a date to fld_Date that is advanced from the next row, the next_date row are affected by copying the advanced date to the next row date... Is there any solution for this...
Here's my sql code that are trying to fix...

SELECT
id,
idname,
fldname,

IF (
@idname = (@idname := idname),
@id :=@id + 1,
@id := 1
) i,
fld_Date,
next_Date
FROM
(
    SELECT
        a.id,
        a.idName,
        a.fldName,
        a.fld_Date,
        IFNULL(b.fld_Date, 0) next_Date
    FROM
        x_table a
    LEFT JOIN x_table b ON a.idname = b.idname
    AND a.fld_Date < b.fld_Date
    GROUP BY
        a.id
) A,
(SELECT @id := 0, @idname := 0) B
 ORDER BY
idName,
a.fld_Date

Answer 1

Try this:

SELECT id, idname, fldname, IF(@idname=(@idname:=idname), @id:=@id+1, @id:=1) i, fld_Date, next_Date
FROM (SELECT a.id, a.idName, a.fldName, a.fld_Date, IFNULL(b.fld_Date, 0) next_Date
        FROM (SELECT * FROM x_table ORDER BY idName, fld_Date) a 
        LEFT JOIN (SELECT * FROM x_table ORDER BY idName, fld_Date) b ON a.idname = b.idname AND a.fld_Date < b.fld_Date
        GROUP BY a.id ORDER BY idName, fld_Date) A, (SELECT @id:=0, @idname:=0) B