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 going to 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 they would want to get the total amount 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 they would enter submit button, and then there would be a table shown in the web site 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 columns). 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?
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>
";