Results 1 to 2 of 2
  1. #1
    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
    FROM actions
    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.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts