Help with arrays - dates

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

Post Reply
Daedalus8
Forum Newbie
Posts: 5
Joined: Mon Sep 24, 2007 9:55 am

Help with arrays - dates

Post by Daedalus8 »

So hopefully you guys can understand what I'm trying to do. Here we go!

Source
MySQL database with hundreds of records, ordered by unique id. Each one of the records have a date entry

What I need:
- Create an array from the database that will contain how many records there is per day on a monthly(weekly, quaterly) basis. I can select all the dates and put them into an array but I need an actual count of each entry per date and that is what needs to go into the array.

Can any of you guys tell me how would I go about doing this?


Thanks to all of you for even reading my question.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Proper query would likely suffice.. if I understand your question.
Daedalus8
Forum Newbie
Posts: 5
Joined: Mon Sep 24, 2007 9:55 am

Post by Daedalus8 »

Most likely... could maybe a moderator move this topic to database? Or if somebody else has more ideas they are more than welcome.

Thanks for the fast reply feyd, very much appreciated
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Help with arrays - dates

Post by califdon »

Daedalus8 wrote:So hopefully you guys can understand what I'm trying to do. Here we go!

Source
MySQL database with hundreds of records, ordered by unique id. Each one of the records have a date entry

What I need:
- Create an array from the database that will contain how many records there is per day on a monthly(weekly, quaterly) basis. I can select all the dates and put them into an array but I need an actual count of each entry per date and that is what needs to go into the array.

Can any of you guys tell me how would I go about doing this?


Thanks to all of you for even reading my question.
We could probably do a better job of helping you if you would tell us what you are trying to accomplish. What do you intend to do with the array? Do you plan to permit the user to specify whether the basis will be weekly, monthly or quarterly for each query?

In the meantime, read up on the GROUP BY clause in SQL, it sounds like that's what you want. http://www.tizag.com/mysqlTutorial/mysqlgroupby.php
Daedalus8
Forum Newbie
Posts: 5
Joined: Mon Sep 24, 2007 9:55 am

Post by Daedalus8 »

That groups them by date but doesn't give me the count of items on each day.

We're getting there!! :)
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post by ReverendDexter »

You are familiar with the sql count() function, yes?

psuedo-code:

Code: Select all

SELECT date, count(*)
FROM table_with_stuff_in_it
group by date
Last edited by ReverendDexter on Mon Sep 24, 2007 3:27 pm, edited 1 time in total.
Daedalus8
Forum Newbie
Posts: 5
Joined: Mon Sep 24, 2007 9:55 am

Post by Daedalus8 »

So I came up with this query which does work, the only problem with it is that I need the dates that have no entries to show as well, instead of just disapear.

Code: Select all

SELECT products, tm_date, COUNT(tm_date) as amount 
FROM ticket_man 
WHERE products LIKE '%productname%' AND tm_date BETWEEN CURDATE()-6 AND DATE_ADD(CURDATE(), INTERVAL 1 DAY)-0 
GROUP BY products 
ORDER BY tm_date;
Thanks again for helping me :)
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post by ReverendDexter »

Only way I know to do in SQL, is to create a lookup table of all the dates that you want, and then do a left or right join of your results against that.

Or, you could create an array in PHP with all the dates you want as keys, and then fill them with the data you get from your query.

I don't believe that it's possible to make a query list all of the dates within a range (I'd love to be shown elsewise, though!).
Daedalus8
Forum Newbie
Posts: 5
Joined: Mon Sep 24, 2007 9:55 am

Post by Daedalus8 »

ReverendDexter wrote:Only way I know to do in SQL, is to create a lookup table of all the dates that you want, and then do a left or right join of your results against that.

Or, you could create an array in PHP with all the dates you want as keys, and then fill them with the data you get from your query.

I don't believe that it's possible to make a query list all of the dates within a range (I'd love to be shown elsewise, though!).
Could you show a piece of code maybe? just to guide me a little bit.

Thanks a lot for answering.
Post Reply