php - Condition inside a SQL Query
Get the solution ↓↓↓I want to create a query, that will show the word "Passed" if the DATEDIFF(so_date,actual_delivery) = 3 then show "Failed" if not. Can someone help me please?
<?php
$hostname="localhost";
$username="root";
$password="";
$db = "csl_otd";
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT so_date,
actual_delivery,
case when DATEDIFF(so_date, actual_delivery) = 3
then "Passed"
else "Failed"
end as status
FROM sales_order
LEFT JOIN dar ON sales_order.dar_numberr = dar.dar_number
WHERE DATEDIFF(so_date, actual_delivery) < 10') as $row) {
echo "<tr>";
echo "<td>" . $row['so_date'] . "</td>";
echo "<td>" . $row['actual_delivery'] . "</td>";
echo "<td>" . $row['DATEDIFF(so_date,actual_delivery)'] . "</td>";
echo "<td>" . $row['status'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
Output
Date Difference | Status
2 | Passed
4 | Failed
Answer
Solution:
use case when
SELECT so_date,actual_delivery,
DATEDIFF(so_date,actual_delivery) Date_Difference,
case when DATEDIFF(so_date,actual_delivery) = 3
then "Passed"
else "Failed" end Status
FROM sales_order
LEFT JOIN dar
ON sales_order.dar_numberr=dar.dar_number
WHERE DATEDIFF(so_date,actual_delivery)<10
Or you can use if that you used (but there is little correction)
SELECT so_date,actual_delivery,DATEDIFF(so_date,actual_delivery),
if ( DATEDIFF(so_date,actual_delivery) = 3
,'Passed','Failed') status
FROM sales_order
LEFT JOIN dar
ON sales_order.dar_numberr=dar.dar_number
WHERE DATEDIFF(so_date,actual_delivery)<10
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: foreach() argument must be of type array|object, null given
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.