CODEWITHSUNDEEP
phpmysqlsql
Samir Sheikh
Samir Sheikh

Reputation: 2401

Could not get record of current date

I would like to fetch data from database based on current date,In mysql it working perfect using NOW() but i have tried using following way for php but it's not work (It's display old date data also).

$date = date('Y-m-d');

$sql = "SELECT  a.created_date,a.shipment_id,o.order_id, o.customer_id,c.delivery_method
        FROM  `orders` AS o
        INNER JOIN  `table_1` AS a ON o.order_id = a.order_id
        INNER JOIN  `table_2` as c ON c.id=o.customer_id
        WHERE a.manifest_create_status =0 
            AND a.active_status=1 
            AND o.customer_id IN ($id)
            AND a.created_date > $date 
        ORDER BY a.created_date";

Is anything wrong in query ?

Upvotes: 1

Views: 53

Answers (1)

GMB
GMB

Reputation: 222462

You need to quotes around the date value, like a.created_date > '$date'.

Also please note that expression o.customer_id IN ($id) would be better written o.customer_id = $id.

Query :

$sql = "SELECT  a.created_date,a.shipment_id,o.order_id, o.customer_id,c.delivery_method
        FROM  `orders` AS o
        INNER JOIN  `table_1` AS a ON o.order_id = a.order_id
        INNER JOIN  `table_2` as c ON c.id=o.customer_id
        WHERE a.manifest_create_status =0 
            AND a.active_status=1 
            AND o.customer_id = $id
            AND a.created_date > '$date' 
        ORDER BY a.created_date";

To avoid this type of issue, while also providing protection against any type of bind injection (where does the id comes from ?), I would recommend to use bind parameters, as follows :

$date = date('Y-m-d');
$sql = "SELECT  a.created_date,a.shipment_id,o.order_id, o.customer_id,c.delivery_method
        FROM  `orders` AS o
        INNER JOIN  `table_1` AS a ON o.order_id = a.order_id
        INNER JOIN  `table_2` as c ON c.id=o.customer_id
        WHERE a.manifest_create_status =0 
            AND a.active_status=1 
            AND o.customer_id = ?
            AND a.created_date > ? 
        ORDER BY a.created_date";

$stmt = $conn->prepare($sql);
$stmt->bind_param("is", $id, $date);
$stmt->execute();

Upvotes: 1

Related Questions