How can I display data between specified dates and get a summation of only those data’s?

So I am using PHP and MySQL(PDO). I have a form that is a lot like the table image below in which the user is gonna input data (almost) every day.

Data Entry Example:

Date Old Case(1) New Case(2) Total Case(1+2) Discharge Conviction Acquittal Other
12-06-2021 1000 500 1500 30 40 18 0
13-06-2021 1500 200 1700 10 14 23 9
14-06-2021 1700 100 1800 19 33 23 18
15-06-2021 1800 200 2000 32 18 07 21

And later maybe he would want to get the total number of data between two specified dates which the user would specify.Here is also another question of mine. How will the user specify the dates?. for example, he would select the ‘from’ date”01-January-2021″ and the ‘to’ date “01-February-2021” after selection he would enter submit button, and then there would be a table shown in the website like below where the total “Discharge” number, total “Conviction”, total “Acquittal” and total “Others” numbers between those dates would show.

Final Output:

Date(Between Two Dates) Last Entry Old Case in Date Range(1) Last Entry New Case in Daterange(2) Total Case(1+2) Total Discharge Total Conviction Total Acquittal Total Other
12-6-2021 to 15-6-2021 1800 200 2000 91 105 71 48

Now how can I do this function? I am not being able to figure out the function where all the data between selected dates would get total up according to their fields and then display the totaled results! Do I need to use Ajax?

DB-FIDDLE(I have never used this before so don’t really know how to use it properly.): https://www.db-fiddle.com/f/goPRNPA8Q6QRkn258sdrsd/1

Another Question: I thought I would be able to figure this issue on my own but I couldn’t after trying the whole day. So There are three other columns in my database (after updating the colums). They are “Old Case”, “New Case” and the total of these two columns “Total Cases”. This “Total Case” becomes the “Old Case” for the next entry or date. And in my Final Output, It would show the last entered “Old Case” and “New Case” between the selected “DateRange”. But I am not being able to figure out how I can implement this. Can anyone help me, please?

Answer

SQL (I assume you have an auto increment ID column in your table so that we could relate the previous record with the current record);

SELECT          CONCAT(MIN(M.Date), ' to ', MAX(M.Date)) AS DateRange, 
                SUM(M.Discharge) AS TotalDischarge, 
                SUM(M.Conviction) AS TotalConviction, 
                SUM(M.Acquittal) AS TotalAcquittal, 
                SUM(M.Other) AS TotalOther, 
                M.NewCase AS LastNewCase, 
                MP.NewCase AS LastOldCase
FROM            tmp_mytable AS M
    LEFT JOIN   tmp_mytable AS MP ON MP.ID = (SELECT MAX(M2.ID) FROM tmp_mytable AS M2)
WHERE           M.Date BETWEEN '2021-01-01' AND '2021-02-01'

I think you wanted to select the records before ‘2021-02-01’, not up to ‘2021-02-01’. In that case, change the WHERE clause as following

WHERE           M.Date >= '2021-01-01' 
    AND         M.Date < '2021-02-01'

Code example: PHP: My.PHP

print "
    <form action="./My.PHP" method="POST">
        Date: 
        From <input type="date" name="DateFrom" required>
        To <input type="date" name="DateTo" required>
        <button type="submit" name="btnPost">Post</button>
    </form>
";

$Recordset = $HTML = [];

if(isset($_POST["btnPost"])){
    $Database = new PDO("Database", "User", "Password");

    $Query = $Database->prepare("
        SELECT          CONCAT(MIN(M.Date), ' to ', MAX(M.Date)) AS DateRange, 
                        SUM(M.Discharge) AS TotalDischarge, 
                        SUM(M.Conviction) AS TotalConviction, 
                        SUM(M.Acquittal) AS TotalAcquittal, 
                        SUM(M.Other) AS TotalOther, 
                        M.NewCase AS LastNewCase, 
                        MP.NewCase AS LastOldCase
        FROM            tmp_mytable AS M
            LEFT JOIN   tmp_mytable AS MP ON MP.ID = (SELECT MAX(M2.ID) FROM tmp_mytable AS M2)
        WHERE           M.Date BETWEEN '{$_POST["DateFrom"]}' AND '{$_POST["DateTo"]}'
    ");

    $Query->execute();
    $Recordset = $Query->fetchAll();
}

foreach($Recordset as $Record){
    $HTML[] = "
        <tr>
            <td>{$Record["DateRange"]}</td>
            <td>{$Record["TotalDischarge"]}</td>
            <td>{$Record["TotalConviction"]}</td>
            <td>{$Record["TotalAcquittal"]}</td>
            <td>{$Record["TotalOther"]}</td>
            <td>{$Record["LastNewCase"]}</td>
            <td>{$Record["LastOldCase"]}</td>
        </tr>
    ";
}

print "
    <table>
        <thead>
            <tr>
                <th>Date</th>
                <th>Discharge</th>
                <th>Conviction</th>
                <th>Acquittal</th>
                <th>Other</th>
                <th>New</th>
                <th>Old</th>
            </tr>
        </thead>
        <tbody>" . implode(null, $HTML) . "</tbody>
    </table>
";