1 of 2/
MY QUESTION
How do I create sub-totals within sub-totals in an html table for a mysql query ?
As you see in the pic above my table shows a simple case where a numeric field, viz. dividend_payable is sub-totalled by company. I’d like to take this a step further adding (a) sub-totals for each year within each company as well as (b) sub-totals for each year for all companies and (c) a grand-total by year for all companies.
And to improve the appearance of the table by inserting the words ‘sub-total’ and ‘grand-total’ as needed followed each time by a line break.
I’ve tried lots of ways but my brain isn’t tuned in to data scientist mode yet so to show my numerous failed efforts here would be distracting, so the code I show below is my last effort that worked correctly.
I’ve had no luck googling for procedural solutions, it’s all about oop and bootstrap and jquery and I’m not interested in those kinds of solution. It’s like a desert outthere on this topic with no youtube videos and no gurus.
My level is newbie with a short term goal to learn procedural mysqli in xampp to create php desktop applications. All solutions, clues and links to websites gratefully accepted.
2 of 2/
HERE IS MY CODE …
<html style = "background-color:khaki;">
<head>
<title>Display a Table of Companies and Dividend's Payable</title>
<style>
table {margin: 0 auto;}
table {background-image: url('http://127.0.0.1:8080/images/spring.jpg');}
</style>
</head>
<body>
<!-- Table element in html, which also contains php & sql -->
<table border=1 cellpadding=1 cellspacing=1>
<caption style = "margin:8px auto; font-size: 38px; font-family: Buxton Sketch; Color: Green;"><b>Dividends Payable sub-totalled by Company</b></caption>
<tr>
<th>Company Name</th>
<th>Payment Date</th>
<th>Dividend Paid</th>
<tr>
<?php
// (a) Log onto MySQL User Account + Connect to Database
$conn = mysqli_connect('localhost','root','','sharediary');
// (b) Place query into a 'literal' variable
$sql = "SELECT company_name, date_of_payment, SUM(dividend_payable)
FROM shareholding
GROUP BY company_name, date_of_payment
WITH ROLLUP";
// Execute SQL Query using (a) & (b) thereby placing results into Array Variable called 'records'
$records = mysqli_query($conn,$sql);
// Loop to print out all rows in the array variable
while($row = mysqli_fetch_array($records))
{
// tr has same effect as carriage return on a typewriter
echo "<tr>";
echo "<td>".$row['company_name']."</td>";
echo "<td>".$row['date_of_payment']."</td>";
echo "<td>".$row['SUM(dividend_payable)']."</td>";
echo "</tr>";
}
?>
<table>
</body>
</html>
