homepage Welcome to WebmasterWorld Guest from 54.167.75.155
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
How To Return MySQL results with left as all dates?
AthlonInside




msg:1301724
 7:04 am on Mar 15, 2005 (gmt 0)

Say I track a bunch of sales everyday by individual transaction, i.e. Each transaction take a row in my sales table.

I use count(*) and group by date to generate a results such as

1 Jan - 3
2 Jan - 2
3 Jan - 8
...

But the problem is, if there is 0 sales in a day, the date won't appear and my result will 'skip' that day

1 Jan - 3
2 Jan - 2
3 Jan - 8
5 Jan - 2

** 4 Jan missing if no individual sale tracked on that day.

How to I make MySQL return this instead?

1 Jan - 3
2 Jan - 2
3 Jan - 8
4 Jan - 0
5 Jan - 2

Any idea? Thank You.

 

jatar_k




msg:1301725
 7:33 am on Mar 15, 2005 (gmt 0)

how are you outputting your dates/counts?

You should be able to compare the dates that are there and if a date is missing output that date and 0

AthlonInside




msg:1301726
 11:11 pm on Mar 30, 2005 (gmt 0)

Sorry, webmasterworld is always returning 403 to me for 2 weeks and I can't get in!

Do you mean using PHP to manually check for it? That will be quite troublesome because you will need to code some calender functions so you know which month has 30 days and which 31 and which february has 28 days and which have 29!

I am looking for a more easy solution, for exmaple with MySQL's date function, etc.

Most reports that show daily breakdown statistic will need to achieve this so I believe MySQL should be able to provide more.

killroy




msg:1301727
 11:38 pm on Mar 30, 2005 (gmt 0)

Unfortunately u cannot return data for a row that doesn't exist. You can do two things, PHP-wise u can post process. It has date functions that will easily take care of finding hte number of days for the relevant month, or you can use a temp MySQL table into which you fill the dates.

I do seem to remember how I once did a left outer join on a dummy numbers table and managed to fill the gaps, but I can't recall how it went.

hmm I just looked it up and found a table by the name of "pivot" with these specs:

+-------+---------+------+-----+---------+----------------+
¦ Field ¦ Type ¦ Null ¦ Key ¦ Default ¦ Extra ¦
+-------+---------+------+-----+---------+----------------+
¦ i ¦ int(11) ¦ ¦ PRI ¦ NULL ¦ auto_increment ¦
+-------+---------+------+-----+---------+----------------+

and filled with numbers from 1 to 10000. It's been a while since I used it though, but I'm sure if u google on mysql and pivot tables you'll find a solution.

SN

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved