CODEWITHSUNDEEP
phpmysql
Craftstrail
Craftstrail

Reputation: 77

Find missing dates in php

Hi I have two textboxes for date from and to. When I select dates in both textboxes, representing a time interval, I'd like to fill in missing dates, so I could query some records our in table along with date suppose i have 

2014-08-02  
2014-08-08      
2014-08-10      
2014-08-11  
2014-08-07  
2014-08-12

or may be another date please help me 

<?php
$from='2014-08-11'; 
$to='2014-09-10';

$query="SELECT * FROM stats WHERE Feeddate BETWEEN '2014-08-11' AND '2014-09-10'"; 
?>

Upvotes: 1

Views: 838

Answers (2)

Rachael
Rachael

Reputation: 424

Didn't test it, but something like this. You'll want to create a temp table of the series of dates in the range you are looking for. For example 2014-08-11 to 2014-09-10. This has a way of doing that:

Generating a series of dates

Then you'll want to query against that

SELECT *
FROM x
(
     SELECT date 
     FROM dateseries
     WHERE date BETWEEN '2014-08-11' AND '2014-09-10'
) as x
WHERE NOT IN ( 
    SELECT date 
    FROM stats
    WHERE Feeddate BETWEEN '2014-08-11' AND '2014-09-10'
);

Hope you get the idea. Just know that MySql sucks balls at subquery optimization, so you might want to move that subquery to a join.

Upvotes: 1

Havenard
Havenard

Reputation: 27934

$from   = '2014-08-11'; 
$to     = '2014-09-10';
$query  = "SELECT Feeddate FROM stats WHERE Feeddate BETWEEN '$from' AND '$to' ORDER BY Feeddate";

$cursor = DateTime::createFromFormat('Y-m-d', $from);
$res    = mysql_query($query);

while ($row = mysql_fetch_assoc($res))
{
    $date = DateTime::createFromFormat('Y-m-d', $row['Feeddate']);
    while ($cursor != $date)
    {
        echo "Day is missing: " . $cursor->format('Y-m-d') . "<br>";
        $cursor->modify('+1 day');
    }
    $cursor->modify('+1 day');

}

Upvotes: 1

Related Questions