How To Return MySQL results with left as all dates? PHP Server Side Scripting forum at WebmasterWorld

Welcome to WebmasterWorld Guest from 54.242.233.11
register, login, search, subscribe, help, library, PubCon, announcements, recent posts, open posts,

Pubcon Website

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

Forum Library : Charter : Moderators: coopster & eelixduppy & 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.

Global Options:

top

home

open messages

active posts

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

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
WebmasterWorld ® and PubCon ® are a Registered Trademarks of Pubcon Inc.
© Pubcon Inc. 1996-2012 all rights reserved

PHP Server Side Scripting Forum