January 18th, 2013, 01:00 PM
How to use PHP to display group by quanties
I've been going back and forth on how to do this, and would very much appreciate advise. This question has a lot of MySQL in it, but it is more of an application/PHP question.
The user will specify a given date period (day, week, month, quarter, or year), and I would like to display the quantity of records within the period.
For instance, quantity date range and per day.
and per week:
11 - 12/15/2012
19 - 12/16/2012
18 - 12/17/2012
One option is to display just an representative date within the period, and use PHP's DATE functionality to extract the appropriate information. Pretty easy, but I am not positive that myDateTime will always fall within the given period. Also I am returning a given myDateTime which supposedly covers the entire myDateTime period, but in reality is just the first DATETIME within the period.
85 - Week 50 2012
32 - Week 51 2012
75 - Week 52 2012
A second option is to use MySQL to more granularly format the data. And if I go this way, I still would want to use PHP to format the date instead of MySQL. More complicated and I have similar functionality in multiple locations, but doesn't have the potential deficiencies of the above solution.
SELECT COUNT(*) AS count, myDateTime, "d" AS type FROM t1 GROUP ON DATE(myDateTime);
SELECT COUNT(*) AS count, myDateTime, "w" AS type FROM t1 GROUP ON YEAR(myDateTime),WEEK(myDateTime);
SELECT COUNT(*) AS count, myDateTime, "m" AS type FROM t1 GROUP ON YEAR(myDateTime),MONTH(myDateTime);
SELECT COUNT(*) AS count, myDateTime, "q" AS type FROM t1 GROUP ON YEAR(myDateTime),QUARTER(myDateTime);
SELECT COUNT(*) AS count, myDateTime, "y" AS type FROM t1 GROUP ON YEAR(myDateTime);
Please provide any advise on which path I should take. Thank you
SELECT COUNT(*) AS count, DATE(myDateTime) AS myDateTime_day, "d" AS type FROM t1 GROUP ON DATE(myDateTime);
SELECT COUNT(*) AS count, YEAR(myDateTime) AS myDateTime_year, WEEK(myDateTime) AS myDateTime_week, "w" AS type FROM t1 GROUP ON YEAR(myDateTime),WEEK(myDateTime);
SELECT COUNT(*) AS count, YEAR(myDateTime) AS myDateTime_year, MONTH (myDateTime) AS myDateTime_month, "m" AS type FROM t1 GROUP ON YEAR(myDateTime),MONTH(myDateTime);
SELECT COUNT(*) AS count, YEAR(myDateTime) AS myDateTime_year, QUARTER (myDateTime) AS myDateTime_quarter, "q" AS type FROM t1 GROUP ON YEAR(myDateTime),QUARTER(myDateTime);
SELECT COUNT(*) AS count, YEAR(myDateTime) AS myDateTime_year, "y" AS type FROM t1 GROUP ON YEAR(myDateTime);
January 20th, 2013, 11:14 AM
For values representing a day, I would just show the date.
For values representing a week, I would show either "Week of <date of first day of week>" or "<date of first day of week> - <date of last day of week>". I would not show "Week <week number>" because that is meaningless to 99% of people.
For month, I would just show the month name.
For quarter, I would show Q1, Q2, Q3, Q4; unlike week numbers, quarter numbers actually do have meaning to most business people.
For year, I would just show the year.
This is more of a business logic decision than a programming decision though.
As far as retrieving the data, it will probably be faster to group it using MySQL, like the first option.
One very important thing to note: PHP and MySQL handle weeks differently. One of them starts the week on Monday and the other starts on Sunday, and there is no way to change either as far as I know. I can't remember for sure, but they might also handle partial weeks at the beginning and end of years differently too. This means that the week number MySQL gives you might be different from the week number PHP gives you for the same date, and it means that the two will group and label the data slightly differently.
January 20th, 2013, 01:14 PM
Thank you E-Oreo for your reply.
I was not asking advice regarding business logic, however, I am glad you took it that way as I had not thought about your suggestion regarding weeks and fully agree with you.
My programming question regards the data being sent from the model to the view which is hidden from the user.
Under my option 1, since I am not grouping on myDateTime nor applying an aggregate function to myDateTime, I believe it is non-standardard SQL. While it in fact works and makes my PHP application much simpler, I am now (for the 3rd time going back and forth) leaning on not doing so.
Maybe if I better describe what I am doing, I can get help coming up with a simple and correct SQL solution.
- View tells controller how the data should be grouped. Based on the user's selection, options are being grouped by day date, month/year date, or some other attribute such as color, price, etc.
- Controller gets grouped data from the Model and sends it to the View.
- View displays a list which includes grouped on variable, the count for each grouping, as well as a hyperlink which includes data in the URL which identifies the specific grouping.
- Controller $_GETs the grouping specific identifier, initiates a query to provide all records within the given grouping, and sends the data to the view to be displayed.
When grouping on color or price, there is only one piece of information being sent from the controller to the view as well as sent back via the hyperlink from the view to the controller (i.e. the color ID) to identify the grouping. Under my option 1, there is only one piece of data being sent back and forth for dates as well (2013-04-03). But if I go away from my option 1, now I need sometimes multiple pieces if information to identify the grouping (i.e. month and year). With one piece of information to identify the grouping, I could just call it "grouping_variable" or something. Now with multiple pieces of information, I feel obligated to give each their proper name such as "color_id" under one scenario and "month" and "year" under another scenario.
Well, I am sure I totally confused everyone including myself. If any of this makes sense and you have a suggestion, please reply; otherwise do not feel obligated.
I believe you can do so using PHP's datetime class using something like "last Sunday"