Display ALL records GROUP by Year/Month/Day with SUM

[StevenOliver]

I am needing help with the mySQL "logic" portion of this please.

My "viewLog.php" page accesses a huge table with "TIMESTAMP" and "sales Price."

| timestamp_column    |salesPrice |
+---------------------+-----------+
| 2017-01-03 19:57:07 |      5.00 |
| 2018-08-14 15:00:16 |     50.00 |
| 2017-10-09 06:15:12 |     10.00 |
| 2018-09-22 06:41:37 |     30.00 |
| 2018-09-04 06:41:37 |     20.00 |
| 2018-09-04 11:42:30 |     10.00 |

Displaying all records on one page would be a mile long :-)

How would I display everything grouped on one page? Like this (using data from the example table above):

2018
September's total: $60.00
August's total: $50.00

2017
October's total: $10.00
January's total: $5.00

Those totals would be hyperlinked so they would expand into "daily details" when clicked:

2018
September's total: $60.00
                     ↓
September 04, 2018 at 06:41:37 $20.00
September 09, 2018 at 11:42:30 $10.00
September 22, 2018 at 06:41:37 $30.00

Other than PHP requesting all dates from mySQL and grouping them into endless arrays, foreach loops, etc., is there an efficient (and elegant) one-liner that already does this sort of thing? (I know how to do basic mySQL "group by" requests, but can't get my head around the logic to do the above).

Thank you.

[Barand]

SELECT YEAR(timestamp_column) as yr
     , MONTH(timestamp_column) as mno
     , MONTHNAME(timestamp_column) as mth
     , SUM(salesPrice) as total
FROM mysales
GROUP BY yr, mno

Use yr and mth in page display

Use yr and mno in the link to drill down.

To display as required

currentYr = 0;
while fetch next row {
     if row[year] != currentYr {
         output year
         currentYr = row[year]
     }
     output month name and total
}

 

[gizmola]

To clarify Barand's response, you will need a script to handle the Drill down to Daily details.

Hopefully you understand that you need to pass the date for the calculation either using PHP's Datetime functions or MySQL's functions to derive the range.  If you need help with that, make a seperate thread.  

Depending on the MySQL server mode, there might be some small problems with his summary query.  In some modes, you can't reference computed columns in the Group By alias.  In that case, this works:

SELECT YEAR(time_stamp_column) as yr
     , MONTH(time_stamp_column) as mno
     , SUM(salesPrice) as total
FROM Sale_Price
GROUP BY YEAR(time_stamp_column), MONTH(time_stamp_column)
ORDER BY yr DESC, mno DESC

I added an ORDER BY to order by most recent activity first, just to illustrate that common requirement.

Here's the DbFiddle for you to play with.

[StevenOliver]

Barand, thank you! That is exactly what I was looking for -- code that makes sense, and code that doesn't give PHP unnecessary work. (As a novice, I often revisit code I've written and find I've given PHP unnecessary work that it didn't need to do :-)

[StevenOliver]

Gizmola, thank you for that clarification -- both codes indeed work fine on my server. I like Barand's "monthname" -- I'm always pleasantly surprised how mySQL is so "natural speaking friendly.

And, I like your "order by" clause -- so I can have the recent activity first.

Although the "drill down" php code will be a challenge, I'm confident I can that done (or at least make a dent in it) now that I have a better grasp of the logic.

As a side note..., I'm always mildly annoyed at websites that only display "10" or "20" or "50" of something, and then requiring clicking a "display more" button on the bottom of the page. Maybe this was necessary 20 years ago when we all had 19.2k telephone line modems. But nowadays (with faster browsers and faster connections and all), displaying 5000 records is just as fast as displaying 20. I'm tempted to make my PHP page show a basic summary at the top (using the "group by" code described in this thread), followed by a display of all 20,000+ records. (Maybe not, just thinking out loud :-)

(PHP is so fun... I've been waiting all day to get back to my computer to dig in to this!)

Edited November 24, 2018 by StevenOliver

[StevenOliver]

Using the aformentioned code, these statements work but I don't know how to properly nest them whereby the nested Prepared Query uses data retrieved by first Prepared Query:

// Statement One
$stmt = $db->prepare("
SELECT YEAR(timestamp_column) as yr
     , MONTH(timestamp_column) as mno
     , MONTHNAME(timestamp_column) as mth
     , DAY(timestamp_column) as da
     , SUM(salesPrice) as total
FROM mysales
GROUP BY yr, mno
ORDER BY timestamp_column desc;
");

// Statement Two
$stmt2 = $db->prepare("
SELECT DAYNAME(timestamp_column) as dana
     , usedPrice
FROM mysales
WHERE MONTH(timestamp_column) = 11 // hard-coded month
AND YEAR(timestamp_column) = 2018 // hard-coded year
ORDER BY timestamp_column desc;
");

// This works for first statement:
$stmt->execute();
$result = $stmt->get_result();
while($row = $result->fetch_assoc()) {
echo $row["mth"].'....'.$row["yr"].'...'.$row["total"].'<BR>';
}

// This works for second statement:
$result2 = $stmt2->get_result();
while($row2 = $result2->fetch_assoc()) {
$stmt2->execute();
echo $row2["dana"].'...'.$row2["salesPrice"].'<BR>';
}

Nesting them like this "somewhat" works.... but

1.) Seems inefficient, and just plain wrong to execute "$stmt2->execute();" and "$result2 = $stmt2->get_result();" inside a 50000-record "while loop."
2.) Only works for a "hard-coded" month and year (don't know how to pull data retrieved from first Statement.... e.g. hard-coded year "2018" works, but "$row['yr']" doesn't)

I don't know what to do...

[Barand]

You would only run the second query in response to the user clicking a month in the first query's output. You would then select the data for the selected year/month and display it.

Here's an example

<?php
include('db_inc.php');
$db = pdoConnect('test');            // connect to 'test' database

/******************************************
**  CHECK IF AN AJAX REQUEST WAS SENT
**  IF SO, PROCESS IT
*/
    if (isset($_GET['ajax'])) {
        exit(monthlySales($db, $_GET['year'], $_GET['month'], $_GET['mname']));
    }

$dt = new DateTime('last day of this month');
$dt->sub(new DateInterval('P1Y'));
$dt->setTime(0,0,0);

$start_date = $dt->format('Y-m-d H:i:s');

$summary = $db->prepare("SELECT YEAR(timestamp_column) as yr
                             , MONTH(timestamp_column) as mno
                             , MONTHNAME(timestamp_column) as mth
                             , SUM(salesPrice) as total
                         FROM mysales
                         WHERE timestamp_column > ?
                         GROUP BY yr, mno
                         ORDER BY yr DESC, mno DESC
                        ");
$summary->execute([$start_date]);
$tdata = '';
$currYr = 0;
foreach ($summary as $row) {
    if ($row['yr'] != $currYr) {
        $tdata .= "<tr><th colspan='2'>{$row['yr']}</th></tr>";
        $currYr = $row['yr'];
    }
    $tdata .= "<tr>
        <td><span class='drilldown' data-yr='{$row['yr']}' data-mno='{$row['mno']}' data-mname='{$row['mth']}' title='Drill down into sales detail'>{$row['mth']}</span></td>
        <td class='ra'>\${$row['total']}</td>
        </tr>" ;
}

function monthlySales(PDO $db, $year, $month, $monthname)
{
    $res = $db->prepare("SELECT DATE_FORMAT(timestamp_column, '%W %D at %l:%i%p') as date
                              , salesprice
                         FROM mysales
                         WHERE YEAR(timestamp_column) = ? AND MONTH(timestamp_column) = ?
                         ORDER BY timestamp_column
                        "); 
    $res->execute([ $year, $month ]);
    $total = 0;
    $out = "<table border='1'>\n<tr><th colspan='2'>Sales $monthname $year</th></tr>";
    foreach ($res as $row) {
        $out .= "<tr><td>{$row['date']}</td><td class='ra'>\${$row['salesprice']}</td></tr>";
        $total += $row['salesprice'];
    }
    $out .= "<tr><th>Total</th><td class='ra'> \$" . number_format($total,2) . "</td></tr></table>\n";
    return $out;
}
?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="generator" content="PhpED 18.0 (Build 18044, 64bit)">
<meta name="author" content="Barand">
<meta name="creation-date" content="11/24/2018">
<title>12 Month Sales</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script type='text/javascript'>

    $().ready( function() {
        
        $(".drilldown").click( function() {
            $("#detail-panel").html('')        // clear the panel
            
            $.get(                             // ajax call to get month's sales records
                "",                            // calls self
                { "ajax" : "1",
                  "year" : $(this).data('yr'),
                  "month": $(this).data('mno'),
                  "mname": $(this).data('mname')
                },
                function(resp) {
                    $("#detail-panel").html(resp)
                },
                "TEXT"
            )
        })
    })
</script>
<style type='text/css'>
    body {
        font-size: 11pt;
        font-family: verdana, sans-serif;
    }
    #title {
        background-color: #006EFC;
        color: white;
        font-size: 30pt;
        font-weight: 600;
        text-align: center;
        padding: 10px;
    }
    #summary-panel {
        width: 35%;
        float: left;
    }
    #detail-panel {
        width: 55%;
        float: left;
        margin-left: 50px;
    }
    .drilldown {
        cursor: pointer;
    }
    .drilldown:hover {
        background-color: #eee;
    }
    table {
        width: 60%;
        margin: 30px auto;
        border-collapse: collapse;
    }
    th {
        background-color: #006EFC;
        color: white;
        font-size: 14pt;
        padding: 5px;
        text-align: left;
    }
    #detail-panel th {
        background-color: #3C9DBA;
    }
    td {
        padding: 5px 20px;
    }
    td.ra {
        text-align: right;
    }
</style>
</head>
<body>
<div id='title'>Sales for Last 12 Months</div>
<div id='summary-panel'>
    <table border='1'>
        <?=$tdata?>
    </table>
</div>
<div id='detail-panel'>
      &nbsp;
</div>
</body>
</html>

 

[StevenOliver]

Barand, Wow! Thank you -- that is fantastic code! I'm going line by line familiarizing myself with proper PDO code. For example, new DateInterval('P1Y') sets it to a period of 1 year, setTime(0,0, 0) sets the time ("timer") to zero. I'm now working on trying to understand how and why this line of code works: "$summary->execute([$start_date])" What worries me is that I never would have thought to do that (I wouldn't have made the leap to knowing that the StartDate input parameter could go in there). I guess that's why they call programming languages "languages" ... because analogous to learning a language, one might ponder, "How would I have known to call an apple an 'apple,' or, an orange an 'orange.'" You just do it! And do it over and over again until it becomes natural and second-nature. Thank you again!!

Edited November 24, 2018 by StevenOliver

[Barand]

Oops!

That setTime(0,0,0) should be setTime(23,59,59) so we find all sales after 2017-11-30 23:59:59