skip to Main Content

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>

2

Answers


  1. Chosen as BEST ANSWER
    <?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 :>


  2. 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>
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search