If I had a table (“courses”):
course_id title date
1 One 2020-07-01
2 Two 2020-07-02
3 Three 2020-08-01
4 Four 2021-01-01
How could I output this grouped like so: Year Asc > Month Asc AND with headings? Please assume the dates are mixed, not in order like I’ve done here. I only want years and months that “exist” in the data.
So I would get:
2020
July
One
Two
August
Three
2021
January
Four
So, I need the selecting/grouping SQL logic and how to display that in HTML/PHP.
I am using the framework in PHP and MySQL: Novice to Ninja but, generally, that doesn’t support complex SQL logic (as far as I can see). I do have Entity classes which I use to “generally” emulate JOINS in OOP but this is a single-table issue. Long story short: advice in the form of SQL or PHP OOP would be great. I’d rather use the PHP OOP but I don’t think the framework can handle it by default (no grouping in the DatabaseTable class) so happy to go down the custom SQL query in a controller and skip the entity class.
S’gonna be a bit ugly to do via SQL. I would probably pull the records as-is and use PHP to do the logic. That said, if I had to roll up the titles into a single row…
SELECT YEAR(`date`) AS year,
MONTHNAME(`date`) as month,
MONTH(`date`) as monthno,
GROUP_CONCAT(title SEPARATOR ',') as titles
FROM courses
GROUP BY year,month
ORDER BY year,monthno
and then walk the rows for month/year, and explode the titles column as needed in PHP.
Alternatively, in PHP, pull your records as-is, ordered by date, and then have PHP walk them and interpret as necessary.
If the records are pulled in the correct order, then there’s a few ways you can go about achieving your result.
Simplest way that I can think of is:
Foreach the array of results;
If the Year of the current result is different than the year before it:
print the year and
save the new year for comparison next time.
If the Month of the current result is different than the month before it, or if the year changed,
print the month and
save the new month for comparison next time.
Print the title.
EndForeach.
Alternatively, you could have your code create a multidimensional array as it reads each record from the result, then you just Foreach thrice:
Foreach RecordsByYearAndMonth as YearRecord:
Print Key.
Foreach YearRecord as MonthRecord:
Print Key.
Foeach MonthRecord as Title:
Print Title
EndTitleForeach
End MonthRecordForeach
End YearRecordForeach.
I am trying to work through the second option here, the multi-dimensional array.
This is working to a degree and I get your logic. I have ensured results are ordered properly.
In the outer array (courseYear) I get the first course as expected from which I can work with date (such as extracting and formatting a year. From there, I have issues.
Could you please elaborate on (what I think may well be my issue) what you mean by “Print Key .”
I presume the first time you’ve used this in your example you expect that to be the year and the second time you expect it to be the month but maybe I’m getting these values incorrectly.
Here’s where I am (I’ve whipped out all of the CSS classes for readability):
The error I’m getting is: all I have in courseMonth (when debugging) is the value of the first field of the course (course_date_id). I don’t have any other fields from that record to work with. Here’s the debug:
Yes, I can see the logic there. I’m struggling with that however. Essentially, that’s what I thought I was doing in my code (but echoing the data that would have made up the array) and, for the same reason I’ve given above (my debug) on how I couldn’t do that, I can’t understand how to create that array.
Could you dumb it down a bit further please. I DO have a reasonable/growing understanding of multidimensional arrays and associative arrays but my problem is how to dynamically create one from a foreach on table rows.
How would I get to that array from my database table? It’s the grouping that’s getting me more than anything.
I’ll dig in to option one tomorrow. May I kindly ask, if you are not going to be around, to jot down any “gotchas” to look out for in your first approach so I can try and get this done.
okay, please allow me to throw a spanner into the mix
this is definitely a “hack” (of the good kind) but it works fine
i threw the x'09' tab characters in there, so that you can see the indentation of the year-month-title hierarchy – you don’t have to use them, Mike, but at least they give you something to hang your code on if you need to distinguish the different rows on output (e.g. a new year gets an html H1 tag, an new month H2…)
anyhow, to use this, you would run the query and then in php simply echo the output column (not the sort columns), one print line per query result row
note the ORDER BY relies on NULLs sorting first
SELECT CONCAT(x'0909',title) AS output
, YEAR(date) AS sort1
, MONTH(date) AS sort2
, course_id AS sort3
FROM courses
UNION ALL
SELECT DISTINCT
CONCAT(x'09',MONTHNAME(date))
, YEAR(date)
, MONTH(date)
, NULL
FROM courses
UNION ALL
SELECT DISTINCT
YEAR(date)
, YEAR(date)
, NULL
, NULL
FROM courses
ORDER
BY sort1
, sort2
, sort3
output sort1 sort2 sort3
--------- ----- ----- -----
2020 2020 null null
July 2020 7 null
One 2020 7 1
Two 2020 7 2
August 2020 8 null
Three 2020 8 3
2021 2021 null null
January 2021 1 null
Four 2021 1 4
The first option (above)…
I have a db table full of courses going way back and I have added code to only echo courses in the future. Looking at your logic, do I have to just put a “dummy” year in the code to get the FIRST future course (as there will be no “different than the year before it” as it’s the first one. I understand why you’re doing this (for when the year changes) but what about the FIRST valid course?
The same goes for the FIRST month for the same reason.
Take a look at my “findAll()” and “query()” functions posted above… possible to use these or altered versions to create that array I need?
WOW. Thanks for the detail there. That’s tempting for me (as it works) but, with the utmost gratitude, I’d like to leave that on the back burner for a couple of days as my code is heavily based on using PHP OOP to handle all SQL, especially relationships/joins so your code would work but be out of place in my approach.
For now, I’d prefer to stick with just grabbing the whole table then “foreach-ing” through the rows IF there’s a way (I’d like a couple of days to see if there is a way although I’m increasingly seeing the limitations of my approach in avoiding the capabilities of SQL to structure my output for me).
If I haven’t cracked it by early next week I’ll definitely come back to you.
Bit of a pain in the **** those functions are, IMO.
if pdo is a public member of the class, i would throw the idea of retrieving objects in the trash and just put the data you’re actually interested in with your query. $courses = $this->pdo->prepare("SELECT date,title FROM courses ORDER BY date")->fetchAll(PDO::FETCH_ASSOC);
But, if you really want to stick to this framework’s idea of ‘pull everything always’ (really, really not a good idea, IMO. Horribly wasteful of resources.), you’ll have to work with what you’ve got.
Thanks again my friend for the excellent guidance. I will work this through today and get back to you.
But, as an initial response, the more I work with this framework, the more I feel it is limiting and limited and just plain awkward to use. I doubt I’ll use it on my next website. It’s becoming increasingly like forcing a square peg into a round hole and I’m using way more of the “saved time” a framework is meant to provide in trying to overcome its limitations than if I hadn’t bothered with it.
Or, that’s what it feels like to me. As you are aware, I am no PHP guru and, perhaps, if I were, what I am perceiving as awkward limitations may not be so.
But, for myself, back to an approach which does not force OOP on relational SQL and does not use a framework for my next site.
also, your writing, even in as mundane a place as a discussion forum, is exquisite – do you have a web site? twitter account? (mine are rudy.ca and @rudydotca)
