Thread: Grouping notes by date
01-14-15, 19:55 #1Registered User
- Join Date
- Feb 2014
Unanswered: Grouping notes by date
I am developing a time recording system for legal advisers in a free advice centre using PHP/MySQL.
There is a table called ‘actions’ which records what actions have been taken and how long the advice lasted. The relevant fields are:
matterid (links to another table ‘matters’)
action date (when the advice was given)
action time (how long was spent with a client)
notes (records what advice they gave or action they took)
A number of pieces of advice may be given to a client on a single day and a client will probably come back many time on the same matter.
For a matter I want to be able to view a table which lists all of the notes for each day. So the table should display 3 columns:
The date of the advice || all of the advice notes for that day || the total time spent that day
I have used:
SELECT actiontime, notes, actiondate
ORDER BY actiondate
Which is OK but lists each action on a separate row e.g. if there were 3 actions on January 10th I get 3 rows for January 10th with notes in each one. I want one row with the all of the notes in that row.
01-15-15, 10:14 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
There's somethings you can do in the SQL engine, and somethings you cant do (or cant do easily)
Sometimes you can use clunky workarounds to force a computer language to do soemthing it wasn't (neccesarily) designed for. but in this case Id suggest the best method of handling this is to do so inside your PHP script, not try and do it in SQL.
Inside that PHP script iterate through the client notes and do whatever processing your want and whatever layout you want.
you may be able to do this in SQL using concatenation, multiple self joins and so on. but why..I'd rather be riding on the Tiger 800 or the Norton