Help with arrays - dates
Moderator: General Moderators
Help with arrays - dates
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.
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.
Re: Help with arrays - dates
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?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.
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
- ReverendDexter
- Forum Contributor
- Posts: 193
- Joined: Tue May 29, 2007 1:26 pm
- Location: Chico, CA
You are familiar with the sql count() function, yes?
psuedo-code:
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.
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.
Thanks again for helping me 
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;
- ReverendDexter
- Forum Contributor
- Posts: 193
- Joined: Tue May 29, 2007 1:26 pm
- Location: Chico, CA
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!).
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.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!).
Thanks a lot for answering.