Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2014
    Posts
    35

    Unanswered: Displaying data from a query

    I am producing a database for an charity who give free legal advice. I have various table to record this - matters, legal, outcomes.

    The relevant fields are:
    matters - mattered, legalid, outcomeid, counter
    legal - legalid, legal (legal is the legal area e.g. employment)
    outcomes - outcomeid, outcome

    I want to get a table that shows the legal area, the outcome and the number of matters for each outcome.

    I have a query:
    mysql_select_db($database_blac, $blac);
    $query_Recordset1 = "SELECT matteroutcomes.outcomeid, outcomes.outcome, legal.legal, SUM(matters.`counter`) FROM matters INNER JOIN matteroutcomes ON matteroutcomes.matterid=matters.matterid INNER JOIN outcomes ON matteroutcomes.outcomeid=outcomes.outcomeid INNER JOIN legal ON legal.legalid=matters.legalid GROUP BY matters.legalid, matteroutcomes.outcomeid";
    $Recordset1 = mysql_query($query_Recordset1, $blac) or die(mysql_error());
    $row_Recordset1 = mysql_fetch_assoc($Recordset1);
    $totalRows_Recordset1 = mysql_num_rows($Recordset1);

    which works but the table I produce has headings Legal | Total | Outcome - the code is:
    <p>&nbsp;</p>
    <table border="1">
    <tr>
    <td width="276">Legal Area</td>
    <td width="78">Totals</td>
    <td width="262">Outcome</td>
    </tr>
    <?php do { ?>
    <tr>
    <td><?php echo $row_Recordset1['legal']; ?></td>
    <td><?php echo $row_Recordset1['SUM(matters.`counter`)']; ?></td>
    <td><?php echo $row_Recordset1['outcome']; ?></td>
    </tr>
    <?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
    </table>

    I would like the legal area to only appear once rather than as it is below - so there would be Consumer on the 1st line then 3 blank lines in the legal area column then Employment etc


    Legal Area Totals Outcome
    Consumer 1 Avoid Repossession
    Consumer 1 Housed or re-housed
    Consumer 1 Prevent Homelessness
    Consumer 1 Repairs carried out
    Employment 2 Avoid Repossession
    Employment 2 Debt challenged
    Employment 1 Repairs carried out

    I want it to look like:

    Legal Area Totals Outcome
    Consumer 1 Avoid Repossession
    1 Housed or re-housed
    1 Prevent Homelessness
    1 Repairs carried out
    Employment 2 Avoid Repossession
    2 Debt challenged
    1 Repairs carried out

    Is this possible and, if so, how is it done?

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    short of a tbale redesign there is no way I can see of doing this in MySQL....
    In PHP however you could bastardise your design


    the basic problem is you should have a table for legal area with the PK of that as a FK in the main table
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2014
    Posts
    35
    Sorry - I'm not sure how the pk/fk would help. Is there any guidance/examples anywhere?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Legal types
    1 consumer
    2 emplyment
    3 family

    legal areas
    type fk in legaltypes
    id pk, fk in matters refers to this
    description
    sortseq

    join matters to legal areas, legal areas to legaltypes
    Order by legaltypes.description, legalareas.sortseq

    in you php loop when the legaltypes.description (or id) changes start a new table
    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
  •