Mayeenul Islam Mayeenul Islam - 3 years ago 73
MySQL Question

How to echo specific data in loop with a condition specific to the last row

For an accounting system, I'm using PHP & MySQL. I've two tables "GROUP" and "ACHEADS".

In the GROUP table, I have:

---------------------
| id (AI) | group |
---------------------
| 1 | Group 1 |
| 2 | Group 2 |
---------------------


In the ACHEADS table, I have:

-----------------------------------------
| id (AI) | ac_head | amount | j_id |
-----------------------------------------
| 1 | Something 1 | 2000 | 1 |
| 2 | Something 2 | 1000 | 1 |
| 3 | Something 3 | 5000 | 2 |
| 4 | Something 4 | 4000 | 2 |
| 5 | Something 5 | 8000 | 2 |
-----------------------------------------


I've joined the two tables as GROUP.id <<->> ACHEADS.j_id

Now I need to preview the data like this:

----------------------------------------------
Particulars | Details | Total |
----------------------------------------------
Group 1 | | |
Something 1 | 2000 | |
Something 2 | 1000 | 3000 |
----------------------------------------------
Group 2 | | |
Something 3 | 5000 | |
Something 4 | 4000 | |
Something 5 | 8000 | 17000 |
----------------------------------------------
GRAND TOTAL | | 20000 |
------------------------------------==========


Challenges




  1. The table will be dynamic and will generate within a PHP loop (I'm
    using a WHILE loop)

  2. Remember: it's a table and if I miss echoing a td, then the table will break up



Problems




  • When I'm using the loop it's echoing the data on the Details td
    accurately. But the sum of the details row according to j_id is also
    echoing in each td



Preview here:

----------------------------------------------
Particulars | Details | Total |
----------------------------------------------
Group 1 | | |
Something 1 | 2000 | 3000 |
Something 2 | 1000 | 3000 |
----------------------------------------------
Group 2 | | |
Something 3 | 5000 | 17000 |
Something 4 | 4000 | 17000 |
Something 5 | 8000 | 17000 |
----------------------------------------------


My thoughts




  • If I can check whether it is the last data of the query, if isset,
    then echo the total amount with it's td. (But remember the
    Challenge#2)

  • Does it require a foreach loop?



I failed




  • I tried checking max(id), it works fine in SQL, but can't use it in
    condition within a loop.



(If you still can't understand me, then on the second phase, I'll post my code.)

Answer Source

I would do 2 loops:

  1. Fetch id from GROUP
  2. Fetch amount from ACHEADS based on j_id

This would look something like (non-tested code):

echo '<table><tr><td>Particulars</td><td>Details</td><td>Total</td></tr>';

$total = 0;

$q1 = "SELECT id FROM `GROUP`";
$res1 = mysqli_query($q1);
while($row1 = mysqli_fetch_assoc($res1)) {
    echo 

    $group_total = 0;
    $j_id = $row1[id];

    $q2 = "SELECT ac_head, amount FROM ACHEADS WHERE j_id = $j_id";
    $res2 = mysqli_query($q2);
    while($row2 = mysqli_fetch_assoc($res1)) {

        echo '<tr><td>' . $row2[ac_head] . '</td>';
        echo '<td>' . $row2[amount] . '</td></tr>';

        $group_total = $group_total + $row2[amount];
        $total = $total + $row[amount];
    }

   echo '<tr><td colspan="3" align="right">' . $group_total . '</td></tr>';
}

echo '<tr><td>GRAND TOTAL</td>';
echo '<td colspan="2" align="right">' . $total . '</td></tr>';
echo "</table>";
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download