How to put current date in to my SQL datediff

Question

This is a query which I tried below.

$sql = "SELECT id,due, datediff(date('Y/m/d'), date)  FROM sales

I am writing a sales program for identifying the due payment. If the due payment is more than 7 error displayed payment pending what I tried so far I attached below of the full coding. when I tried the below code error ouput displayed

Notice: Undefined index: datediff(pay_date, date)

if ($_SERVER['REQUEST_METHOD'] == 'GET') {
    $today = date("Y/m/d");
    $sql = "SELECT id,due, datediff(date('Y/m/d'), date)  FROM sales where due != '0' ";
    $result = $conn->query($sql);
    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $id = $row['id'];
            $paydate = $row["datediff(pay_date, date)"] . " " .  "Days" . "</br>";
            if ($paydate >= 7) {
                $status = "Payment pending";
                $color = "#47f50b";
            } elseif ($paydate >= 3) {
                $status = "Payment pending please settle down the amount";
                $color = "yellow";
            } elseif ($paydate >= 1) {
                $status = "kindly pay the payment";
                $color = "#00a8ff";
            } elseif ($paydate == 0) {
                $status = "Disable your Order";
                $color = "red";
            }
        ?>

Nick · Accepted Answer · 2018-12-08T04:40:45.990

1

Assuming your due date column is called date, then your query should be

$sql = "SELECT id, due, DATEDIFF(date, CURDATE()) AS paydate FROM sales WHERE due != '0' ";

Note that we use CURDATE() to get the current date in MySQL, and that I have aliased the DATEDIFF result to paydate.

You also have an issue that you are trying to set

$paydate =  $row ["datediff(pay_date, date)"]

however in your query your expression does not match datediff(pay_date, date). This is why it's better to use an alias as I did above, then you can change that line to simply

$paydate = $row['paydate'];

and if you change the formula in your query this part of the code will still work.

Finally you are appending " Days" to the value of $paydate. This will make comparisons with numbers e.g. in if($paydate >= 7) problematic and it would be better if you left off the " Days" until you output the value of $paydate.

edited Dec 08 '18 at 04:40

answered Dec 08 '18 at 04:10

Nick

138,499
22
57
95

SELECT id, due, DATEDIFF(CURDATE(), date) AS paydate FROM sales WHERE due != '0' if i write like this paydate shows -5 , -4 ,-2 like this sir . can you write full code it easy to understand – Programmer Hari Dec 08 '18 at 04:18
I misinterpreted your data, just swap `CURDATE()` and `date` i.e. `DATEDIFF(date, CURDATE())` – Nick Dec 08 '18 at 04:20
can you write the full code easy forme to understand – Programmer Hari Dec 08 '18 at 04:23
SELECT id, due, DATEDIFF(CURDATE(),pay_ date) AS paydate FROM sales WHERE due != '0' it is still display nagitive numbers -5 -4 -2 like wise check paydate between current date – Programmer Hari Dec 08 '18 at 04:25

score 0 · Answer 2 · answered Dec 08 '18 at 04:10

0

Try using datediff(pay_date, date) as datedif in the SELECT and print using $row["datedif"]

answered Dec 08 '18 at 04:10

Enrico Dias

1,417
9
21

How to put current date in to my SQL datediff

2 Answers2