Select DATEDIFF() AS DAYS using date() method

Question

I've using the DATEDIFF method to get the difference b/w two dates obtained using the date(YYYY:mm:DD HH:MM:ss) function but getting the output 0 every time, even when the difference is more than 1 day.

here's my code -

if(isset($_GET['rt']))
{
$dateis=$_GET['rt'];
}
date_default_timezone_set('Asia/Kolkata');
$date = date('Y-m-d H:i:s');
if(mysqli_query($conn, "UPDATE issued_books SET Date_returned = '$date' WHERE Book_id = '$bk_id' AND Date_issued = '$dateis'"))
{
    $fine = sprintf("SELECT DATEDIFF('%s','%s') AS DAYS",
               '$date', '$dateis');
    $fine = $fine*10;
    mysqli_query($conn, "UPDATE member_db SET Fine_amt = (Fine_amt + '$fine') WHERE Member_id = '$mem'");
}

any kinda help would be really appreciated, thanks :)

@Tiger I was suggested to do that, see here - http://stackoverflow.com/a/43111907/7744098 — Parvez Khan, Apr 05 '17 at 05:46
the guy, who suggested me the sprintf() solution, used hard-coded values as you can see on that page & it works fine for those values, but not for the ones, I'm getting from the date() function — Parvez Khan, Apr 05 '17 at 06:10

cletsimon · Answer 1 · 2017-04-05T06:14:49.137

0

1.remove single quotes for '$date' and '$dateis'

$fine = sprintf("SELECT DATEDIFF('%s','%s') AS DAYS", $date, $dateis);

2.$fine = $fine*10; // this will not work since query is not yet executed in mysql, that's why you will always get undesired result

And always sanitize your parameters to avoid sql injection

edited Apr 05 '17 at 06:14

answered Apr 05 '17 at 06:07

cletsimon

61
5

here's your output - SELECT DATEDIFF('2017-03-27 12:35:04','0000-00-00 00:00:00') AS DAYS – Parvez Khan Apr 05 '17 at 06:16
@ParvezKhan what is the value of $dateis? > $dateis=$_GET['rt']; – cletsimon Apr 05 '17 at 06:20
you should convert to date the value of $dateis `$dateis = date('Y-m-d H:i:s', $_GET['rt']);` or check it if it is a validate date – cletsimon Apr 05 '17 at 06:21
you can see the value of $dateis as it is getting printed in the output in the same format that you are suggesting. – Parvez Khan Apr 05 '17 at 06:40
take a look at this - http://rextester.com/live/MTO57069 Here the calculation is fine but only for hard-coded values. – Parvez Khan Apr 05 '17 at 06:41
SELECT DATEDIFF('2017-03-30 10:26:44', '2017-03-29 17:33:18') AS DAYS When I run the above query in php myadmin, it works fine but, in php, I'm getting error for the below code; $fine = mysqli_query($conn, "SELECT DATEDIFF('2017-03-30 10:26:44', '2017-03-29 17:33:18') AS DAYS"); echo $fine; – Parvez Khan Apr 05 '17 at 06:44
@ParvezKhan what is the error shown? – cletsimon Apr 05 '17 at 06:54
it was some mysqli error, like it can't convert the $fine variable to string – Parvez Khan Apr 05 '17 at 06:56
@ParvezKhan can you paste here the mysqli error? – cletsimon Apr 05 '17 at 06:59

score 0 · Answer 2 · edited May 23 '17 at 12:09

0

Nevermind, I've found the answer here - https://stackoverflow.com/a/676828/7744098 using the strtotime() & floor methods.

Thank you all for commenting.

edited May 23 '17 at 12:09

Community

1
1

answered Apr 05 '17 at 06:54

Parvez Khan

89
9

Select DATEDIFF() AS DAYS using date() method

2 Answers2