Fill in missing date if no record on particular date

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

little_girl
Forum Newbie
Posts: 20
Joined: Tue Oct 13, 2009 5:11 am

Fill in missing date if no record on particular date

Post by little_girl »

I'm working on how to create a proper daily report – one that doesn’t have any gaps when data is missing. I want to show all the date for each month in the report. The problem is when there is no record on particular day, it wouldn't show it in my report. So I would like to know how to fill in the missing date into my report with 0 value.

For example:
My report is showing the total of users joined for each category in daily basis by month.

Table "Magazine"
DateJoined | UserID | CategoryID
2009-01-01 | 123 | 1
2009-01-01 | 124 | 1
2009-01-02 | 125 | 3
2009-01-05 | 126 | 2

My Query Result in table format:
DateJoined | Category 1 | Category 2 | Category 3 | Total
2009-01-01 | 2 | 0 | 0 | 2
2009-01-02 | 0 | 0 | 1 | 1
2009-01-05 | 0 | 1 | 0 | 1

Hope someone could give me some idea on how to solve this problem in php level. Thanks in advance.
litarena
Forum Newbie
Posts: 14
Joined: Tue Sep 01, 2009 3:39 am

Re: Fill in missing date if no record on particular date

Post by litarena »

Is this a php problem or a general programming problem? You've stored all your values in your database. Now you want to extract and print them. That's regular enough. But you also want to print values when you have no data. In that case you'll need to break the problem into chunks.

a. for each individual day of each month, extract the data & test whether or not any data was returned.
b. if data was returned for that day print it.
c. if no data was returned for that day print that day's date and print zeroes in the appropriate data-fields [that's done by checking the value of your database extraction query with ==0. If that's true then no data was returned.]

You'll need to have a couple of things in your program. I'll need to know the lengths of the months in days, and loop through each day of each month with the steps listed above.

(I don't know what this program you're writing is for. If it's for a real live business application you'll need to take account of leap year adjustments too.)
Mark Baker
Forum Regular
Posts: 710
Joined: Thu Oct 30, 2008 6:24 pm

Re: Fill in missing date if no record on particular date

Post by Mark Baker »

You can use a subquery something like:

Code: Select all

 
SELECT date_add('2009-1-1',CONCAT('INTERVAL ',rownumber,' DAY'))
  FROM <TABLE>
 WHERE rownumber < 365
 
which should return a series of 365 dates from the defined start data
(as long as the <table> you select contains that many rows)

You can then join your main query to this query
little_girl
Forum Newbie
Posts: 20
Joined: Tue Oct 13, 2009 5:11 am

Re: Fill in missing date if no record on particular date

Post by little_girl »

Hi Mark Baker:

For example, the data return from my query result only have 3 records on 2009-01-01, 2009-01-02, 2009-01-05. I want to show the rest of the day in that month as well with value 0. So how to print out the rest of the date eventhough in the database table don't have those record?

what should I do to show the start date If the month is based on the user selection?
Thanks.

Hi litarena:
This is php problem.

What do you mean by "take account of leap year adjustments too" and what should I do if want to implement in my application? Can you give me some idea. Thanks.
litarena
Forum Newbie
Posts: 14
Joined: Tue Sep 01, 2009 3:39 am

Re: Fill in missing date if no record on particular date

Post by litarena »

What are you writing it for? If it's just an exercise don't worry about it. But if it's for real use, February has a different length if it's a leap year.
little_girl
Forum Newbie
Posts: 20
Joined: Tue Oct 13, 2009 5:11 am

Re: Fill in missing date if no record on particular date

Post by little_girl »

Is the leap year can be checked as what in this link below?

http://de.php.net/manual/de/function.date.php
Mark Baker
Forum Regular
Posts: 710
Joined: Thu Oct 30, 2008 6:24 pm

Re: Fill in missing date if no record on particular date

Post by Mark Baker »

Sorry it took me so long to get back. I tried to test my query, and found that MySQL had died on me, so I needed to reinstall it.

Actual query should be

Code: Select all

SELECT adddate('2009-01-01',(@rownum:=@rownum+1)-1) date
  FROM (SELECT @rownum:=0) r, COLUMNS
 WHERE @rownum < 365
You must already have a query that is returning your existing report data from the magazine table. You need to join that query to the one that I've posted above, joining the two queries on the date
what should I do to show the start date If the month is based on the user selection?
You'd replace the start date in my query ('2009-01-01') with the actual start of the month selected by the user... you must be doing something similar in your existing query.
You'd probably also want to adjust the 365 days that I set in the WHERE clause.

Without knowing your existing select statement, and how you're inserting the user specified date into that, I can't do much more to help
little_girl
Forum Newbie
Posts: 20
Joined: Tue Oct 13, 2009 5:11 am

Re: Fill in missing date if no record on particular date

Post by little_girl »

Hi Mark Baker:

This is my existing select statement:
SELECT DATE(DateJoined) AS jDate,
SUM(IF(catID=1,1,0)) AS Cat1,
SUM(IF(catID=2,1,0)) AS Cat2,
SUM(IF(catID=3,1,0)) AS Cat3,
COUNT(*) AS Total
FROM Magazine
WHERE YEAR(DateJoined)=YEAR(CURDATE()) AND MONTH(DateJoined)=$mth
GROUP BY DATE(DateJoined)

So where should I join the subquery as you suggested?
Mark Baker
Forum Regular
Posts: 710
Joined: Thu Oct 30, 2008 6:24 pm

Re: Fill in missing date if no record on particular date

Post by Mark Baker »

Should look something like:

Code: Select all

 
SELECT d.date AS jDate,
       SUM(IF(m.catID=1,1,0)) AS Cat1,
       SUM(IF(m.catID=2,1,0)) AS Cat2, 
       SUM(IF(m.catID=3,1,0)) AS Cat3, 
       COUNT DISTINCT(m.UserID) AS Total
FROM ( SELECT adddate( CONCAT(YEAR( CURDATE( ) ), '-',$mth,'-01'), (@rownum := @rownum +1) -1 ) date
             FROM ( SELECT @rownum :=0 )r, information_schema.COLUMNS
           WHERE @rownum <31
       ) d
LEFT JOIN `magazine` m ON m.DateJoined = d.date
WHERE YEAR(d.date)=YEAR(CURDATE()) AND MONTH(d.date)=$mth 
GROUP BY DATE(d.date)
 
little_girl
Forum Newbie
Posts: 20
Joined: Tue Oct 13, 2009 5:11 am

Re: Fill in missing date if no record on particular date

Post by little_girl »

Code: Select all

 
SELECT d.ddate AS jDate,
       SUM(IF(m.CategoryID=1,1,0)) AS NST,
       SUM(IF(m.CategoryID=2,1,0)) AS BH, 
       SUM(IF(m.CategoryID=3,1,0)) AS HM, 
       COUNT (*) AS Total
FROM ( SELECT ADDDATE( CONCAT(YEAR( CURDATE( ) ), '-',1,'-01'), (@rownum := @rownum +1) -1 ) ddate
FROM ( SELECT @rownum :=0 )r, information_schema.COLUMNS
       WHERE @rownum <31
       ) d
LEFT JOIN `magazine` m ON m.DateJoined = d.ddate
WHERE YEAR(d.ddate)=YEAR(CURDATE()) AND MONTH(d.ddate)=1
 
In my code above,
I have replaced "COUNT (m.UserID) AS Total" with "COUNT(*) AS Total" and $mth in the second select statement I have replaced with "1". But there are an error message:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) AS Total
FROM ( SELECT adddate( CONCAT(YEAR( CURDATE( ) ), '-',1,'-01'), (@r' at line 5
Mark Baker
Forum Regular
Posts: 710
Joined: Thu Oct 30, 2008 6:24 pm

Re: Fill in missing date if no record on particular date

Post by Mark Baker »

The problem is actually with Total

Code: Select all

 
SELECT d.date AS jDate,
       SUM(IF(m.CategoryID=1,1,0)) AS Cat1,
       SUM(IF(m.CategoryID=2,1,0)) AS Cat2, 
       SUM(IF(m.CategoryID=3,1,0)) AS Cat3, 
       IF(m.CategoryID=NULL,0,COUNT(m.CategoryID)) AS Total
 FROM ( SELECT adddate( CONCAT(YEAR( CURDATE( ) ), '-',$mth,'-01'), (@rownum := @rownum +1) -1 ) date
             FROM ( SELECT @rownum :=0 )r, information_schema.COLUMNS
           WHERE @rownum <31
       ) d
LEFT JOIN `magazine` m ON m.DateJoined = d.date
WHERE YEAR(d.date)=YEAR(CURDATE()) AND MONTH(d.date)=$mth
GROUP BY DATE(d.date)
 
little_girl
Forum Newbie
Posts: 20
Joined: Tue Oct 13, 2009 5:11 am

Re: Fill in missing date if no record on particular date

Post by little_girl »

Hi,

I 4got one condition that I need to put in the WHERE clause where it need to filter the record according to the magazine choose by the user as well beside the month.

My Code:

Code: Select all

 
SELECT d.date AS jDate,
       SUM(IF(m.CategoryID=1,1,0)) AS Cat1,
       SUM(IF(m.CategoryID=2,1,0)) AS Cat2, 
       SUM(IF(m.CategoryID=3,1,0)) AS Cat3, 
       IF(m.CategoryID=NULL,0,COUNT(m.CategoryID)) AS Total
 FROM ( SELECT ADDDATE( CONCAT(YEAR( CURDATE( ) ), '-',$mth,'-01'), (@rownum := @rownum +1) -1 ) DATE
             FROM ( SELECT @rownum :=0 )r, information_schema.COLUMNS
           WHERE @rownum <31
       ) d
LEFT JOIN `magazine` m ON m.DateJoined= d.date
WHERE YEAR(d.date)=YEAR(CURDATE()) AND MONTH(d.date)=$mth AND MagzineID=$mg
GROUP BY DATE(d.date)
 
After I added the "MagazineID=$mg" into the "WHERE" clause, it returned empty recordset. Although without this new condition, it also me the wrong result as all the records returned with the value "0".
Mark Baker
Forum Regular
Posts: 710
Joined: Thu Oct 30, 2008 6:24 pm

Re: Fill in missing date if no record on particular date

Post by Mark Baker »

The primary return for this query is the date (d), and the WHERE clause is all selecting from there.
It is the secondary query (the LEFT JOIN) that is against magazine (m), and your additional criteria is against the magazine.

So your MagzineID=$mg should be against the ON clause for the LEFT JOIN, and not against the WHERE clause
little_girl
Forum Newbie
Posts: 20
Joined: Tue Oct 13, 2009 5:11 am

Re: Fill in missing date if no record on particular date

Post by little_girl »

I have modified my code as below:

Code: Select all

 
SELECT d.date AS jDate,
       SUM(IF(m.CategoryID=1,1,0)) AS Cat1,
       SUM(IF(m.CategoryID=2,1,0)) AS Cat2, 
       SUM(IF(m.CategoryID=3,1,0)) AS Cat3, 
       IF(m.CategoryID=NULL,0,COUNT(m.CategoryID)) AS Total
 FROM ( SELECT ADDDATE( CONCAT(YEAR( CURDATE( ) ), '-',$mth,'-01'), (@rownum := @rownum +1) -1 ) DATE
             FROM ( SELECT @rownum :=0 )r, information_schema.COLUMNS
           WHERE @rownum <31
       ) d
LEFT JOIN `magazine` m ON m.DateJoined= d.date AND MagzineID=$mg
WHERE YEAR(d.date)=YEAR(CURDATE()) AND MONTH(d.date)=$mth 
GROUP BY DATE(d.date)
 

But the result shows all the records return with the value "0" from the beginning until the end of month. Something is wrong in my code, but couldn't find out the problem.
Mark Baker
Forum Regular
Posts: 710
Joined: Thu Oct 30, 2008 6:24 pm

Re: Fill in missing date if no record on particular date

Post by Mark Baker »

I have no idea what is wrong with your code, but there is absolutely nothing wrong with the database query.
Here's the results when I run it myself

Have you tried running it from phpmyadmin to see the result.
Have you tried sumping out the query to see that the values for $mth and $mg are being set correctly
Post Reply