CODEWITHSUNDEEP
phpmysqldate
user7478641
user7478641

Reputation:

How to get the recent record and compare its date with the current date

How to display only one records of customer records who didn't order since last 14 days. I need to sub the last order date from current date and display only who didn't order since 14 days or more.

note that date format sorted on database like this: 01-25-2017 and datatype varchr(can't change it) here my code

records

<?php
    $q_customer = $conn->query("SELECT  * FROM customer_order INNER JOIN  customer on customer_order.phone= customer.phone GROUP BY customer_order.phone" ) or die(mysqli_error());
    while($f_customer = $q_customer->fetch_array()){
?>
<tr>
    <td><?php echo $f_customer['phone']?></td>
    <td><?php echo $f_customer['first_name']?></td>
    <td><?php echo $f_customer['last_name']?></td>
    <td><?php echo $f_customer['order_date']?></td>
</tr>

<?php
    }
?>

Upvotes: 1

Views: 194

Answers (2)

phatfingers
phatfingers

Reputation: 10250

SELECT c.phone, c.first_name, c.last_name, 
MAX(str_to_date(co.order_date, '%m-%d-%Y')) AS order_date
FROM customer_order co INNER JOIN customer c on co.phone=c.phone 
GROUP BY c.phone, c.first_name, c.last_name 
HAVING MAX(str_to_date(co.order_date, '%m-%d-%Y')) < DATE_SUB(curdate(), INTERVAL 2 WEEK);

Upvotes: 1

Nick Duncan
Nick Duncan

Reputation: 829

Try this:

SELECT  * FROM `customer_order`
INNER JOIN `customer` on `customer_order.phone` = `customer.phone`
WHERE `order_date` >= DATE_SUB(date_format(`order_date`, '%d/%m/%Y'), INTERVAL 14 DAY)
GROUP BY `customer_order.phone`

You may have to change

date_format(`order_date`, '%d/%m/%Y')

to suit your needs. For example, you may need to change it to

date_format(`order_date`, '%m/%d/%Y')

Upvotes: 1

Related Questions