4

I want to use strtotimefor two date columns in the database in the CASE statement.

This is the entirety of the php code.

<?php
$conn=mysqli_connect("localhost","root","nature526","thebase");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

 
$result = mysqli_query($conn,  "SELECT * FROM thetable ORDER BY curdate, case 

when mainissue = 'No Water Flow/Low Pressure' AND ((Previous - Recent) / Recent) * 100 >= 120 then 1
when mainissue = 'Leakage' AND address = 'Torko St. San Jose, Antique' AND ((Previous - Recent) / Recent) * 100 > 110 then 2
when mainissue = 'High Meter Readings' then 3
else 4
end asc");

 
echo " <center> <table border='1'>
 
<tr>
 
<th>Full Name</th>
<th>Address</th>
<th>Account Number</th>
<th>Box Number</th>
<th>Designated date for Repairs</th>
<th>Issue</th>
<th>Additional Information</th>
<th>Date Difference</th>


</tr>";


while ($row = mysqli_fetch_array($result))
{
 
    
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['address'] . "</td>";
echo "<td>" . $row['actnum'] . "</td>";
echo "<td>" . $row['boxnum'] . "</td>";
echo "<td>" . $row['reqdate'] . "</td>";
echo "<td>" . $row['mainissue'] . "</td>";
echo "<td>" . $row['textarea'] . "</td>";
echo "<td>" . (strtotime($row['curdate']) - (strtotime($row['recentdate']))) / 86400 . "</td>";

 
echo "</tr>";
}
echo "</table>";

mysqli_close($conn);

So, the code above works but I want to include the two date columns recentdate and curdate. But its bugging me on where I should use the strtotime in the query. It works when I use it to echo the row. echo "<td>" . (strtotime($row['curdate']) - (strtotime($row['recentdate']))) / 86400 . "</td>";. This formula works and outputs the difference between the date columns. December 10, 2018 and December 12, 2018 would output in the table 2. Now, how would the strtotime work in the CASE statement so I could prioritize the highest difference?

It should theoretically look like these:

$result = mysqli_query($conn,  "SELECT * FROM thetable ORDER BY curdate, case 
when mainissue = 'No Water Flow/Low Pressure' AND  ((Previous - Recent) / Recent) * 100 >= 130  AND strotime(curdate - recentdate) / 86400 >= 1 then 1
when mainissue = 'No Water Flow/Low Pressure' AND ((Previous - Recent) / Recent) * 100 >= 120 then 2
when mainissue = 'Leakage' AND address = 'Torko St. San Jose, Antique' AND ((Previous - Recent) / Recent) * 100 > 110 then 3
when mainissue = 'High Meter Readings' then 4
else 5
end asc");
Dharman
  • 30,962
  • 25
  • 85
  • 135
Raven Pete
  • 41
  • 3
  • use `DATEDIFF()` for that:- https://stackoverflow.com/a/2490177/4248328 – Alive to die - Anant Dec 10 '18 at 07:41
  • @Alive to Die i see, where do I place it in the code exactly? Just to avoid confusion. – Raven Pete Dec 10 '18 at 08:28
  • `AND (curdate - recentdate) / 86400 >= 3`. here – Alive to die - Anant Dec 10 '18 at 08:29
  • For completeness: try to find a working query using phpMyAdmin or another tool alone. That will help you to see that something like `strtotime` is a PHP method that does not work when you want to execute SQL calculations – Nico Haase Dec 10 '18 at 08:49
  • @Alive to Die when mainissue = 'No Water Flow/Low Pressure' AND ((Previous - Recent) / Recent) * 100 >= 130 AND DATEDIFF(reqdate - recentdate) / 86400 >= 1 then 1 is this how I place the DATEDIFF()? If so, it doesn't work because it gives me a mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given error. – Raven Pete Dec 10 '18 at 17:30

0 Answers0