[Solved] get data from DB as range using php

by


For example…

 DROP TABLE IF EXISTS my_table;

 CREATE TABLE my_table
 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
 ,date DATE NOT NULL
 ,price INT NOT NULL
 );

 INSERT INTO my_table VALUES
 (1      ,'2014-01-01',   10),
 (2      ,'2014-01-02',   10),
 (3      ,'2014-01-03',   10),
 (4      ,'2014-01-04',   10),
 (5      ,'2014-01-05',   20),
 (6      ,'2014-01-06',   20),
 (7      ,'2014-01-07',   10),
 (8      ,'2014-01-08',   10);


        SELECT a.date start
             , MIN(c.date) end
             , a.price
          FROM my_table a
          LEFT 
          JOIN my_table b 
            ON b.price = a.price
           AND b.id = a.id - 1
          LEFT 
          JOIN my_table c 
            ON c.price = a.price
           AND c.id >= a.id 
          LEFT 
          JOIN my_table d 
            ON d.price = a.price
           AND d.id = c.id + 1
         WHERE b.id IS NULL 
           AND c.id IS NOT NULL
           AND d.id IS NULL
         GROUP 
            BY a.id;
 +------------+------------+-------+
 | start      | end        | price |
 +------------+------------+-------+
 | 2014-01-01 | 2014-01-04 |    10 |
 | 2014-01-05 | 2014-01-06 |    20 |
 | 2014-01-07 | 2014-01-08 |    10 |
 +------------+------------+-------+

Note that this particular solution assumes that ids are contiguous.

0

solved get data from DB as range using php