Page 1 of 1

Help with arrays - dates

Posted: Mon Sep 24, 2007 12:38 pm
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.

Posted: Mon Sep 24, 2007 12:48 pm
by feyd
Proper query would likely suffice.. if I understand your question.

Posted: Mon Sep 24, 2007 12:57 pm
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

Re: Help with arrays - dates

Posted: Mon Sep 24, 2007 1:32 pm
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

Posted: Mon Sep 24, 2007 2:58 pm
by Daedalus8
That groups them by date but doesn't give me the count of items on each day.

We're getting there!! :)

Posted: Mon Sep 24, 2007 3:25 pm
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

Posted: Mon Sep 24, 2007 3:26 pm
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 :)

Posted: Mon Sep 24, 2007 3:32 pm
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!).

Posted: Mon Sep 24, 2007 3:38 pm
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.