3

I have a pretty good idea of how to do this, but I'm not exactly sure... how to do it , if that makes sense. This is still my first day (going on second without sleep) of learning PHP and I'm trying to complete this project before I call it quits. This is actually all that's left before I can call it quits and be happy with myself. So here's the thing.

I know I've asked quite a few questions today, hopefully this is the last one..

Currently my code pulls information from my database and displays it into a table, like so:

enter image description here

Now, this is great for the feature where I want to list the last 15 transactions, which is what my following code does, please excuse anything that's not done efficiently as it's my first day.

<html>
    <table border="1">
        <tr>
            <th>Transaction Date</th>
            <th>Transaction Amount</th>
            <th>Item Name</th>
            <th>Quantity</th>
        </tr>

        <?php
            require_once 'Config.php';
            require_once 'Connection.php';

            $totalTransactions = 0;
            $totalProfit = 0;

            $testquery = "SELECT * FROM $tbl_name WHERE DATE($tbl_name.Date) 
            BETWEEN DATE_SUB(CURDATE(), INTERVAL 15 DAY) AND CURDATE()";

            $results = mysql_query($testquery) or die (mysql_error());

            while($row = mysql_fetch_array($results)) 
            {
                $totalTransactions += 1;
                $totalProfit += $row[$ROW_AMOUNT];

                echo "<tr>";
                echo "<td align='center'>".$row[$ROW_DATE] . "</td>";
                echo "<td align='center'>$". number_format($row[$ROW_AMOUNT], 2) . "</td>";
                echo "<td align='center'>null</td>";
                echo "<td align='center'>null</td>";
                echo "<tr>";
            }

            echo "<tr>";
            echo "<td align='center'><strong>SUM:</strong></td>";
            echo "<td align='center'><strong>$".number_format($totalProfit, 2)."</strong></td>";
            echo "<td align='center'><strong> </strong></td>";
            echo "<td align='center'><strong> </strong></td>";
            echo "<tr>";
        ?>
    </table>
</html>

Now, I'm trying to figure out how I can group it like such in a table [Day] - [Sum]

I understand how to get the sum for the data, obviously because that's what the script above does for the last 15 transactions, but how about grouping them together?

an example of the output I'm looking for is like this (This was done in pure HTML and is just an example of what I'm trying to achieve)

enter image description here

To re-word my question more efficiently, I'm trying to create another table that shows the sum for each date that there is "Transactions" for.

Christian Tucker
  • 627
  • 8
  • 20
  • Great question, very well articulated. Keep it up and good luck on your project. – David Brossard May 26 '14 at 08:14
  • Please note that you appear wide open to [SQL Injection](http://security.stackexchange.com/questions/25684) depending on where you get `$tbl_name`. You should be using an explicit whitelist of allowed tables if you actually need dynamic SQL, and [parameterized queries](http://stackoverflow.com/q/60174), period. I also recommend using an explicit upper-bound (`<`) for continuous range types, such as date/time/timestamp types. Up through "current" works, but[other ranges have issues](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common) – Clockwork-Muse May 26 '14 at 08:47
  • @Clockwork-Muse the **$tbl_name** is taken from Config.php. It's static and never changed. – Christian Tucker May 26 '14 at 08:51

1 Answers1

2

You have to group your columns using the GROUP BY clause and then aggregate the sum of Transaction Amount:

SELECT Date, SUM([Transaction Amount])
FROM $tbl_name
WHERE DATE($tbl_name.Date) 
BETWEEN DATE_SUB(CURDATE(), INTERVAL 15 DAY) AND CURDATE()
GROUP BY Date

Please note that you may have to put quotes or something else around the column name Transaction Amount, this is TSQL syntax, I'm not sure how it's done in MySQL.

Merlin Denker
  • 1,380
  • 10
  • 15