How can I output html table with NESTED SUB-TOTALS and GRAND TOTALS from mysql query?

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 © 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>

So we have a clear indication of your goal, how about you show us what you would like the end result to look like.
That could be the resulting html, or if you are unsure about that, maybe a mock-up/sketch of the table you want.

The first step to better code is to separate things a bit. First separate styling from content. You have a mix of CSS, in-line styles and presentational attributes. Only one of those is really acceptable (the CSS). So get all that styling into the CSS part where it belongs.

The next one is to separate your back-end logic from the front-end content.
Query the database and process the data with any calculations before any html. This way all the data will be ready for you when the time comes to start building the table and the code will be a lot more readable.

But as I say, we first need a clear picture of your goal before getting into the specifics.

2 Likes

I’ve played around with Queries in phpmyadmin and this one nearly gets the result I’m looking for…
SELECT tax_year, payment_number, company_name, SUM(dividend_payable) AS Dividend
FROM shareholding
GROUP BY company_name, tax_year, payment_number WITH ROLLUP
but is missing sub-totals for each tax year, all companies namely lines 22 & 23 in the mockup.

Here is a mock-up of what I’m trying to achieve …

Here is the output produced by the SELECT statement given in the previous post

Just before the final row this output is missing the 2 sub-totals you see on lines 22 & 23 of the mock up i.e. Sub-Totals by Tax Year including all Companies.

Can anyone suggest how I can alter my SELECT statement to correct this omission ?