Codeigniter query to data from database for each day

Question

I have a $from date and $to date with me so I want to fetch four values from the the database for days in between $from and $to and including $from and $to . If the data does not exist for a day in the database then zero must in placed as the value for the missing values.

So how would I write a query in codeigniter to make it happen and the corresponding date should be stored in the result for a particular row .

Answer 1

My previous solution is that I using PHP to check and set zero for missing date. But I have some new solutions and you can try

1. Create a table to store dates and left/right join with your table
2. Or create temporary table using stored procedure and join. Temporary will be auto deleted when a session is expired
3. Using UNION with select statement

There're many answers on StackOverflow MySQL how to fill missing dates in range? MySQL group by date and count including missing dates Running total over date range - fill in the missing dates MySQL to fill in missing dates when using GROUP BY DATE(table.timestamp) without joining on temporary table

Answer 2

i think you solution needs to be actually in PHP and not sure if you can get what you are looking for directly from MYSQL just by a query. As from what i understand you want to run a query, get all records that are in your defined date range and then have dates that has no records have an empty row (or with any other value you decide...).

I would actually run the same query you have for selecting rows between the daterange and use DatePeriod Class to generate an array of all days between the start and end dates.

$begin = new DateTime( '2012-08-01' );
$end = new DateTime( '2012-10-31' );
$end = $end->modify( '+1 day' );

$interval = new DateInterval('P1D');
$daterange = new DatePeriod($begin, $interval ,$end);

foreach($daterange as $date){
    echo $date->format("Y-m-d") . "<br>";
}

With this we will be able to run over each day from the $from_date to the $end_date.

Next we will need to go other the rows from the DB and see on which days there are records and where no according the the daterange object we have.

Here is an approach that would work i believe, it's not the cleanest sample but some additional work on it and you can make it a bit prettier, but i think that will work for what you need.

The Database section in the code is not in Codeigniter but as it is only getting a simple query you should not have any trouble changing that.

// set the start & end dates
$from_date = '2012-09-11';
$to_date     = '2012-11-11';

// create a daterange object
$begin         = new DateTime($from_date);
$end           = new DateTime($to_date );
$end            = $end->modify( '+1 day' );
$interval      = new DateInterval('P1D');
$daterange = new DatePeriod($begin, $interval ,$end);

$sth = $dbh->prepare("SELECT col1, dateCol from tb WHERE dateCol>'".$from_date."' AND dateCol<'".$to_date."' order by dateCol ");
$sth->execute(); 
$rows = $sth->fetchAll(PDO::FETCH_ASSOC);

$rowsByDay = array(); // will hold the rows with date keys

// loop on results to create thenew rowsByDay
foreach($rows as $key=>$row) {
    $rowsByDay[strtotime($row['dateCol'])][] = $row; // add the row to the new rows array with a timestamp key
}

// loop of the daterange elements and fill the rows array 
foreach($daterange as $date){
    if(!isset($rowsByDay[strtotime($date->format("Y-m-d"))])) // if element does not exists - meaning no record for a specific day
    {
        $rowsByDay[strtotime($date->format("Y-m-d"))] = array(); // add an empty arra (or anything else)
    }
}

// sort the rowsByDay array so they all are arrange by day from start day to end day
ksort($rowsByDay); 


// just for showing what we get at the end for rowsByDay array
foreach ($rowsByDay as $k=>$v) {
    echo date('Y-m-d',$k);
    var_dump($v);
    echo '<hr/>';
}

Hope this gets you on the right way...

Answer 3

Hope this helps,

$this->db->where('date_column >= ',$date_given_start);
$this->db->where('date_column <= ',$date_given_end);
$this->db->get('TABLE_NAME')->result();


// if column is datetime
$this->db->where('DATE(date_column) >= ',$date_given_start);
$this->db->where('DATE(date_column) <= ',$date_given_end);
$this->db->get('TABLE_NAME')->result();