how to check if there is a EXPIRING product-CodePudding

how to know if there is a expiring product? like if you set the expiry date of certain product of 11-25-2022 so by the 11-22-2022 it is considered as expiring product and

it will count the row of considered as expiring product.

I know my query is wrong because I didn't specify the column field

<?php
                        include('../connect.php');
                        $result = $db->prepare("SELECT * FROM rawprod");
                        $result->execute();
                        for($i=0; $row = $result->fetch(); $i  ){
                            // code...
                            $date = date($row['expiry_date']);

                            $datenew=date_create("$date");
                            date_sub($datenew,date_interval_create_from_date_string("3 days"));
                            $expiringdate = date_format($datenew, "Y-m-d");

                            $date = date("Y-m-d");
                            $result = $db->prepare("SELECT * FROM rawprod where '$date' >= $expiringdate");
                            $result->execute();
                            $rowcountEXP = $result->rowcount();

                        }
                    ?>

                    <div style="text-align:center;">
                        Expiring Raw Product: <font style="color:green; font:bold 22px 'Aleo';">[<?php echo $rowcountEXP; ?>]</font>
                    </div>

CodePudding user response：

You can call the date comparison in your mysql query by using DATE_SUB. Beneath there is an mysqli example with prepared statements. I assume your db connection is stored in the variable $db and one column is called product.

<?php
include('../connect.php');
$today = date('Y-m-d');
$expiring_products = array();
$stmt = $db->prepare("SELECT `product` FROM `rawprod` WHERE DATE_SUB(`expiry_date`,INTERVAL 3 DAY) <= ?");
$stmt->bind_param("s", $today);
$stmt->execute();
$stmt -> bind_result($expiring_products);
foreach ($stmt->get_result() as $row)
{
$expiring_products[] = $row['product'];
}
mysqli_close($db);
?>
<div style="text-align:center;">
Expiring Raw Product: <font style="color:green; font:bold 22px 'Aleo';">
<?php 
print count($expiring_products);
?>
</font></div>

CodePudding user response：

<?php
  include('../connect.php');
  $datetoday = date("Y-m-d");
  $res = $db->prepare("SELECT * FROM rawprod WHERE '$datetoday' >= 
  DATE_SUB(expiry_date,INTERVAL 3 DAY)");
  $res->execute();
  $rowcount123 = $res->rowcount();
?>

thanks to your idea, it is working now :>