-1

I've been trying to execute the following piece of code to calculate the difference b/w the dates obtained using the PHP $date = date('Y-m-d H:i:s'); function.

$fine = mysqli_query($conn, "SELECT DATEDIFF(".$query_exec['Date_issued'].", ".$query_exec['Date_returned'].") AS days");
$fine = $fine*10;

The value that $fine if returning is only 0 even if the difference b/w the dates is >0.

Plz help, thank you

Jens
  • 67,715
  • 15
  • 98
  • 113
Parvez Khan
  • 89
  • 9

1 Answers1

0

I suspect $query_exec['Date_issued'] and $query_exec['Date_returned'] contain something like 2017-03-01, 2017-03-30 that, when inserted in the SQL code above produces:

SELECT DATEDIFF(2017-03-30, 2017-03-01) AS DAYS

There is no DATE value in this SQL. 2017-03-01 and 2017-03-30 are numeric expressions that evaluates to 2013 and 1984.

Enclose the values of $query_exec['Date_issued'] and $query_exec['Date_returned'] in quotes in the SQL: 

$sql = sprintf("SELECT DATEDIFF('%s','%s') AS DAYS",
               $query_exec['Date_issued'], $query_exec['Date_returned']);

I used sprintf() to make it more readable. You can use strings concatenation as well if you like it more.

axiac
  • 68,258
  • 9
  • 99
  • 134
  • 1
    Surely you should use a parameterised query rather than `sprintf` for this. – ymbirtt Mar 30 '17 at 10:26
  • I totally agree with you. I tried to solve just the mentioned issue and nothing more. However, if the value of `$query_exec['Date_issued']` is generated using `date()` there is no danger of SQL injection and if it is not executed in a loop, a parametrized query doesn't bring performance improvements. – axiac Mar 30 '17 at 10:35
  • I've benn trying what he's suggesting but usign the date() function rather than hard-coded values http://stackoverflow.com/a/20419537/7744098 – Parvez Khan Mar 31 '17 at 10:09
  • Also there's nothing wrong without enclosing $Date_issued & $ Date_returned into sing quotes as I'm getting both the dates accurately. – Parvez Khan Mar 31 '17 at 10:11
  • @ParvezKhan you didn't get it. `2017-03-01` is not a date, it's a numeric expression. To be more specific, its value is exactly `2013` (as the result of the operations: `2017-3=2014`, `2014-1=2013`). The query `SELECT DATE_DIFF(2017-03-30, 2017-03-01)` is the same as `SELECT DATE_DIFF(1984, 2013)` – axiac Mar 31 '17 at 10:17
  • So how come the output is always 0 & how should I get the current time then in those 2 variables? – Parvez Khan Mar 31 '17 at 10:43
  • @ParvezKhan take a look at the results of this query: http://rextester.com/live/MTO57069. [`DATEDIFF()`](https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_datediff) cannot handle numbers – axiac Mar 31 '17 at 11:16
  • Hmm, now I understand, I'll try implementing it in my code & let you know asap :) – Parvez Khan Mar 31 '17 at 11:36
  • @axiac it's still not working man, I've tried the exact same code you've provided but the output is still 0, help plz – Parvez Khan Apr 04 '17 at 04:28