Adefila Sammy T-k Adefila Sammy T-k - 3 months ago 9
MySQL Question

How to determin date below todays date from db in php

I'm developing a pharmacy store project, but I have a problem of determining the total number of drugs that expired. From DB I have:

+----+----------+--------+------------+
| id | drug_nam | amount | exp |
+----+----------+--------+------------+
| 1 | M and T | 200 | 04/15/2016 |
| 2 | VIT C | 20 | 05/25/2016 |
| 3 | Pana | 10 | 01/03/2016 |
| 4 | Lonat | 1200 | 08/25/2017 |
| 5 | ProC | 100 | 05/25/2017 |
+----+----------+--------+------------+


what I need here is a line of PHP script that will count the numbers of expired drugs from DB. using
<?php $d = date('m/d/Y'); ?>
to determine it from DB.

I used the code below but it count only 2

<?php
$d = date('m/d/Y');
$result = mysqli_query($conn, "SELECT count(exp) FROM products where exp < $d ");

while($row = mysqli_fetch_array($result))
{
echo $row['count(exp)'];
}

Answer

You should convert your string date representation to date value if you want to filter by date but not by string. This query should work:

SELECT count(exp) FROM products where STR_TO_DATE(exp, '%d/%m/%Y') < $d 

The main drawback is mysql can't use index in this case. One of the solution is to convert your column from varchar(50) to DATETIME. In this case you can use your original query.