sub-totals within sub-totals in html table with mysql data

Problems with the Windows version of XAMPP, questions, comments, and anything related.

sub-totals within sub-totals in html table with mysql data

Postby sugafairie » 08. May 2019 15:36

Dear Moderator,

Is this question appropriate for this forum?

How do I add images to a post ?

Kind Regards,

Gordon

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>
sugafairie
 
Posts: 14
Joined: 16. March 2019 11:51
XAMPP version: 3.2.2
Operating System: Windows 8.1

Re: sub-totals within sub-totals in html table with mysql da

Postby Nobbie » 08. May 2019 17:53

sugafairie wrote:so the code I show below is my last effort that worked correctly.


Ok, nice, so what is your problem as it works correctly?
Nobbie
 
Posts: 13170
Joined: 09. March 2008 13:04

Re: sub-totals within sub-totals in html table with mysql da

Postby sugafairie » 08. May 2019 19:53

Yes, that is bad english!
I mean to say this is what I've got at the moment and it's not good enough.
To clarify my question I will post mock up of what I would like to achieve in the next day or 2.
sugafairie
 
Posts: 14
Joined: 16. March 2019 11:51
XAMPP version: 3.2.2
Operating System: Windows 8.1

Re: sub-totals within sub-totals in html table with mysql da

Postby Nobbie » 09. May 2019 09:57

After re-reading your first posting, i think i got your problem. Your select statement is wrong, as you select and group for date_of_payment, but this leads to a grouping per DAY(!), what is not what you are looking for. You want a grouping per YEAR.

You need to extract the YEAR from date_of_payment and group for YEAR, thats all you need to change:

"SELECT company_name, YEAR(date_of_payment) AS myYear, SUM(dividend_payable)
FROM shareholding
GROUP BY company_name, myYear";

For a subtotal per YEAR without grouping for company, you of course get the total amount payed per year:

"SELECT YEAR(date_of_payment) AS myYear, SUM(dividend_payable)
FROM shareholding
GROUP BY myYear";

Instead you may also use WITH ROLLUP in your first sql statement, actually i did not know that clause, its new in MySQL 8. This also gives you the grand total per year. Last not least you may also add an WITH ROLLUP in the second sql statement i provided, that gives the total total payment for all years and all companies:

"SELECT YEAR(date_of_payment) AS myYear, SUM(dividend_payable)
FROM shareholding
GROUP BY myYear
WITH ROLLUP";
Nobbie
 
Posts: 13170
Joined: 09. March 2008 13:04

Re: sub-totals within sub-totals in html table with mysql da

Postby sugafairie » 10. May 2019 19:16

Dear nobbie,

Thanks for looking at my question in detail.
I didn't realise your alias 'myYear' could be used in the GROUP BY !

It will take me a while to grasp your answer as I'm really struggling with the idea of using sets.
My brain is programmed to think in dbIII+ and BASICA.
Also I've googled around it's been quite scary to discover that MySQL Queries can be bafflingly complex.
How on earth does a person reach that level of skill ?!

Could you tell me how to add a jpeg to this question as I have screen captured an Excel mock up and it would help with further questions.
sugafairie
 
Posts: 14
Joined: 16. March 2019 11:51
XAMPP version: 3.2.2
Operating System: Windows 8.1

Re: sub-totals within sub-totals in html table with mysql da

Postby Nobbie » 10. May 2019 20:41

sugafairie wrote:How on earth does a person reach that level of skill ?!


As always - learning by doing and taking your time. It took me years(!) to reach that level, i am not a prodigy or so, its lots of stuff to learn and of course it takes a very long time to improve your skill.

As for your question about jpg, you cannot simply show a JPG from your disk. You have to upload it to a picture provider or any similar known webspace and finally you have to apply the URL of that picture in an IMG Tag of this forum. Sorry, its quite complicated for newbees, but thats the way it is.
Nobbie
 
Posts: 13170
Joined: 09. March 2008 13:04

Re: sub-totals within sub-totals in html table with mysql da

Postby sugafairie » 10. May 2019 21:30

Rough mock up of desired output, more questions to follow

https://www.flickr.com/photos/90155868@N08/46904004075/in/dateposted/
sugafairie
 
Posts: 14
Joined: 16. March 2019 11:51
XAMPP version: 3.2.2
Operating System: Windows 8.1

Re: sub-totals within sub-totals in html table with mysql da

Postby Nobbie » 11. May 2019 09:37

Sorry, but we can stop that here. I wont give you a fully featured solution for your task. I gave you an important hint and of course its on you to get deeper into MySQL and procedural programming with PHP. Maybe it will take some time (it took lots of time for me as well), but there is no other way than learning by doing. Of course i *could* program the whole script for you, but that is definately not within the scope of this forum and it would only improve my own skills, but not yours.
Nobbie
 
Posts: 13170
Joined: 09. March 2008 13:04

Re: sub-totals within sub-totals in html table with mysql da

Postby sugafairie » 11. May 2019 10:16

Dear Nobbie,
I am working hard at devising my own solutions and I'm only looking for hints.
e.g. experimenting in phpmyadmin I've come up with this query ...

SELECT tax_year, payment_number, company_name, SUM(dividend_payable) AS Dividend
FROM shareholding
GROUP BY company_name, tax_year, payment_number WITH ROLLUP;

which almost produces the desired result see ...
https://www.flickr.com/photos/90155868@N08/47821778031/in/dateposted
but is missing the sub-totals you see on lines 22 & 23 of the mock up.

I've tried lots of variations to get these 2 sub-totals but I'm beginning to think a more complex technique
may be needed and any hints about direction of travel would be appreciated.
sugafairie
 
Posts: 14
Joined: 16. March 2019 11:51
XAMPP version: 3.2.2
Operating System: Windows 8.1

Re: sub-totals within sub-totals in html table with mysql da

Postby Nobbie » 11. May 2019 10:27

You cannot solve that with one single SQL (or - maybe one can do it), you have PHP as a powerfully procedural language and its very easy to split the task in its logical parts.

To show a pseudo syntax, what you need:

Code: Select all
Loop over all companies
     Loop over all years with that company
         Loop over all payments from that company in that year, ordered by paymen_number
         ... some HTML code to show all payments for that company in that year ...
         ... you can either build the sum of payments within PHP, or use SQL (Group by ...) ...
         Loopend
         ... some HTML code to show subtotal per year per company ....
     Loopend
     ... some HTML code to show the subtotal for that company ...
Loopend


Finally get all subtotals (we solved that a few posts above)
Total Finally get total total (also solved above)


Now simply replace the pseudo code by real PHP syntax and real SQL statements (you have to build multiple selects, store them into multiple result sets).

Its easier than you might think. Dont try to build one overkill SQL statement, you may try that in a couple of years, when you are a professional SQL programmer. But actually, i dont find any use for it, why not using simple statements and simple PHP? Its easier to read and to maintain.
Nobbie
 
Posts: 13170
Joined: 09. March 2008 13:04

Re: sub-totals within sub-totals in html table with mysql da

Postby sugafairie » 11. May 2019 11:55

Dear Nobbie,

Thank you, I'll give it a go and let you know how I get on
sugafairie
 
Posts: 14
Joined: 16. March 2019 11:51
XAMPP version: 3.2.2
Operating System: Windows 8.1


Return to XAMPP for Windows

Who is online

Users browsing this forum: No registered users and 135 guests