CODEWITHSUNDEEP
phpmysqlloopsforeachrange
hakanerenler
hakanerenler

Reputation: 55

mysql php grouping start date and end date with price

I've a mysql table where I store daily prices for a rental system. Table has id, property_id, date and price

id 1 | propertyid 1 | date 2015-05-01 | price 300
id 2 | propertyid 1 | date 2015-05-02 | price 300
id 3 | propertyid 1 | date 2015-05-03 | price 300
id 4 | propertyid 1 | date 2015-05-04 | price 300
id 5 | propertyid 1 | date 2015-05-05 | price 500
id 6 | propertyid 1 | date 2015-05-06 | price 500
id 7 | propertyid 1 | date 2015-05-07 | price 700
id 6 | propertyid 1 | date 2015-05-08 | price 900
id 7 | propertyid 1 | date 2015-05-09 | price 900

I get the results with

( SELECT * from price WHERE property_id = 1 ORDER BY date ASC)

Question is, I need to group prices with start date and end date for same dates but I don't how where and how to start. Result should be like

startdate = 2015-05-01 enddate = 2015-05-05 price = 300
startdate = 2015-05-05 enddate = 2015-05-07 price = 500
startdate = 2015-05-07 enddate = 2015-05-08 price = 700
startdate = 2015-05-08 enddate = 2015-05-10 price = 900

With this I can get all the prices for 1 year, but if the prices are same in an array of dates I can group them. I get all the values in foreach but don't know how to group them.

Thanks.

Upvotes: 1

Views: 590

Answers (1)

Sean
Sean

Reputation: 12433

Here is a way to do it in php.

first, save all your returned db rows to an array-

while($row = fetchRow()){
    $rows[] = $row;
}

second, create an array to hold the groups, a counter var, and the last row key.

$ranges=array();
$x=0;
$last=count($rows)-1;

third, loop through each returned row doing the following - Set the range startdate/price. If it is the last row set the enddate; else if the next row price is not the same, set the enddate and increase the counter (for single date range); else if the price is not the same as the current range price, set the enddate and increase the counter.

foreach($rows as $key=>$row){
    //if range startdate not set, create the range startdate and price
    if(!isset($ranges[$x]['startdate'])){
        $ranges[$x] = array('startdate'=>$row['startdate'], 'price'=>$row['price']);
    }

    //if the last row set the enddate
    if($key==$last){
        $ranges[$x]['enddate'] = $row['startdate'];
    }
    //if the next price is not the same, set the enddate and increase the counter (single date range)
    else if($row['price']!=$rows[$key+1]['price'] ){
        $ranges[$x]['enddate'] = $row['startdate'];
        $x++;
    }
    //if the price is not the same as the current range price, set the enddate and increase the counter
    else if($row['price']!=$ranges[$x]['price'] ){
        $ranges[$x]['enddate'] = $rows[$key-1]['startdate'];
        $x++;
    }
}

last, loop over your ranges and print the values

foreach($ranges as $range){
    echo "startdate = {$range['startdate']} enddate = {$range['enddate']} price = {$range['price']}<br />";
}

Upvotes: 1

Related Questions