CODEWITHSUNDEEP
phpdatetimereport
Daniel
Daniel

Reputation: 4314

PHP: report table with date gaps

I have a table in DB which contains summaries for days. Some days may not have the values. I need to display table with results where each column is a day from user selected range. I've tried to play with timestamp (end_date - start_date / 86400 - how many days in report, then use DATEDIFF(row_date, 'user_entered_start_date') and create array from this indexes), but now I've got whole bunch of workarounds for summer time :( Any examples or ideas how to make this correct?

P.S. I need to do this on PHP side, because DB is highly loaded.

Upvotes: 0

Views: 741

Answers (2)

dnagirl
dnagirl

Reputation: 20446

Try the DateTime object:

$reportdate=date_create($user_startdate);
$interval=new DateInterval('P1D');//1 day interval

$query="SELECT s.start_date, s.end_date, s.info 
   FROM summary s 
   WHERE s.start_date>='$user_startdate' 
      AND s.end_date<='$user_enddate'";

$r=mysqli_query($db,$query);
while($row=$r->fetch_assoc()){
  $rowdate=create_date($row['start_date']);
  while($reportdate < $rowdate) {//won't work if $rowdate is a timestamp!  
    //output $reportdate and blank row
    $reportdate->add($interval); //increment date
  }
  //output $rowdate and $row[info]
  $reportdate->add($interval); //increment date

}

ETA another option:

Based on your comments, it may be easier to dynamically generate the missing dates. For this you'll need an integer table, the number of dates that should appear in your report output, a start date and a date increment.

In your db create a table called numbers and insert the numbers 0 through 9:

CREATE TABLE  numbers (
  num int(10) unsigned NOT NULL,
  PRIMARY KEY (num)
);

The numbers table can be used for making sequences of integers. For instance, to get a sequence from 1 to 20:

SELECT i FROM (
  SELECT 10*n1.num + n2.num AS i 
  FROM numbers n1 CROSS JOIN numbers n2) nums
WHERE i BETWEEN 1 AND 20
ORDER BY i ASC;

If you left join a sequence query like the above to your regular query, you should be able to generate both real and blank rows. e.g.

SELECT alldates.d, mystuff.* FROM
    (SELECT date_add($start_date, interval i day) AS d FROM 
        (SELECT 10*n1.num + n2.num AS i
         FROM numbers n1 CROSS JOIN numbers n2
         ORDER BY i ASC) nums
     WHERE i <= DATEDIFF($end_date,$start_date)) alldates
LEFT JOIN mystuff
ON alldates.d = mystuff.somedate
ORDER BY $whatever;

Upvotes: 1

Surreal Dreams
Surreal Dreams

Reputation: 26380

You could "pre-load" the database with blank values. Then do UPDATE queries instead of inserts. All your days with no data will be pre-populated. You can create a simple script that creates a month's/year's/decade's worth of (blank) data and run it as often as you need. Then you never have to worry about how the days with no data get into the database - you start with your data "zeroed" out.

Upvotes: 0

Related Questions