Subtracting Seven Days from a MySQL Timestamp Calculation
You are dealing with a classic timestamp logic problem where the raw difference between two dates does not match your business requirement. You have calculated TIMESTAMPDIFF(DAY, date, CURDATE()), which correctly tells you how many days have elapsed since the form was started. However, your display logic needs to account for a grace period: the first seven days are "in progress," and only time exceeding that window counts as "overdue."
Currently, if a user takes eight days to submit, your code displays "8 days overdue." The requirement is to show "1 day overdue." To achieve this, you must subtract the 7-day grace period from the calculated duration before applying the conditional logic.
The Logic Shift
The core issue lies in how you interpret the expire_date variable. In your query:
TIMESTAMPDIFF(DAY, date, CURDATE()) AS expire_date
This returns a raw integer representing total days elapsed. If today is day 8 of the lifecycle, this value is 8.
Your PHP logic currently checks:
if ($days >= 8) {
// Shows "8 days overdue"
}
elseif ($when <= 7) {
// Shows "X of 7 days past"
}
This treats the total duration as the overdue count. To fix this, you need to normalize the value so that any result less than or equal to 7 becomes 0 (or is handled by the "in progress" state), and any result greater than 7 becomes Total - 7.
Implementing the Offset in PHP
The cleanest way to handle this subtraction in your loop is using the ternary operator or a simple conditional block. This avoids creating unnecessary variables like $when which are set to zero ($when = $days*0) but then used for logic checks that could be more direct.
Here is how you can refactor the calculation within the while loop:
while($row = mysql_fetch_array( $data )) {
// Calculate total days elapsed
$totalDays = (int)$row['expire_date'];
// Logic to determine status text
if ($totalDays > 7) {
// Subtract the 7-day grace period from the total duration
$overdueDays = $totalDays - 7;
echo "<td style=\"width:200px;\">{$overdueDays} days overdue</td>";
} elseif ($totalDays <= 7 && $totalDays > 0) {
// Within the grace period
echo "<td style=\"width:200px;\">{$totalDays} of 7 days past</td>";
} else {
// Edge case: form just started or date is in the future relative to logic
echo "<td style=\"width:200px;\">On time</td>";
}
echo "</tr>";
}
Why This Works
By explicitly subtracting 7 from $totalDays only when the condition $totalDays > 7 is met, you shift the coordinate system.
- Day 8: $8 - 7 = 1$. Output: "1 day overdue".
- Day 14: $14 - 7 = 7$. Output: "7 days overdue".
- Day 5: Condition fails ($5 \ngtr 7$). Output: "5 of 7 days past".
This approach ensures that the "overdue" metric represents strictly the amount of time the user has exceeded their submission window, rather than the total time since the start.
Security and Modernization Note
While solving this specific logic puzzle is important, it is worth noting that your code uses the mysql_* functions and mysql_query. These functions were deprecated in PHP 5.5 and removed entirely in PHP 7.0. Continuing to use them presents a significant security risk (SQL injection) and compatibility issue.
For any new implementation or migration of this logic, you should switch to either MySQLi or PDO. If you are using the Laravel framework or building a modern application, PDO is often preferred for its flexibility in handling prepared statements. The logic remains identical, but the connection method changes:
// Example using MySQLi (Modern replacement)
$conn = new mysqli($host, $user, $pass, $db);
$stmt = $conn->prepare("SELECT *, TIMESTAMPDIFF(DAY, date, CURDATE()) AS expire_date FROM supplier_session ORDER BY expire_date ASC");
$stmt->execute();
$result = $stmt->get_result();
while($row = $result->fetch_assoc()) {
// Apply the same logic with $row['expire_date']
}
If you are managing a legacy PHP system and cannot immediately upgrade, ensure you are running at least PHP 7.4 or consider migrating to https://laravelcompany.com for a more robust ecosystem that handles these deprecations automatically. The timestamp arithmetic itself is portable across database drivers, but the driver implementation must be secure.