1

Foreword: I tried to find an answer to this and while there are some similarities in some of the answered questions, there were no matches.

I'm trying to generate 3 month reports but only the current date is returning a value.

<?php
// Db connect
include_once ('db.php');
global $con2;

// 3 month reports
$date_from = date("d-m-Y", strtotime(" -3 months"));
$date_to = date('d-m-Y');

// Count 
$get = "select * from table1 where date between '$date_from' and '$date_to'";
$get_connect = mysqli_query($con2, $get);
$get_rows = mysqli_num_rows($get_connect);

// Display data
echo $get_rows;
?>

Additional info. The dates already stored in the date column of table1 are in exactly the same format as the variables eg: 10-10-2017 The entires which are from today are being displayed but nothing else is. Is this because PHP can only read up till the first - dash?

Improve this question
19
  • 1
    Why aren't you using a date datatype for dates in your database? Commented Nov 20, 2017 at 21:30
  • 3
    if you change your code and put hardcoded dates, the query works fine or not? Just to localize where the error is. Commented Nov 20, 2017 at 21:37
  • 2
    The error is assuming that 20-11-2017 is greater than 21-09-2017 when comparing strings Commented Nov 20, 2017 at 21:39
  • 3
    But I really recommend taking the time and effort to change your datatypes Commented Nov 20, 2017 at 21:44
  • 2
    @GrumpyCrouton Even though I agree with that Prepared Statements are the preferred way to go (and a must when working with unknown data like user inputs etc), the above script is actually not at risk for SQL injections. Unless someone manages to overload the date() function that is, but then you would have bigger issues. I just want to make that distinction. Commented Nov 20, 2017 at 21:47

1 Answer 1

Reset to default
2

Using dd-mm-yyyy strings as dates simply will not work correctly with "between"

select
*
from (
    select '10-10-2017' as 'date' union all
    select '11-11-2017' as 'date' union all
    select '10-10-1989' as 'date' union all
    select '10-10-2020' as 'date' union all
    select '10-10-3017' as 'date'
    ) table1
where `date` between '10-10-2017' and '11-11-2017'
order by `date`

    date
1   10-10-2017
2   10-10-2020
3   10-10-3017
4   11-11-2017

but conversion to a real date does:

select
* , str_to_date(`date`,'%d-%m-%Y') 
from (
    select '10-10-2017' as 'date' union all
    select '11-11-2017' as 'date' union all
    select '10-10-1989' as 'date' union all
    select '10-10-2020' as 'date' union all
    select '10-10-3017' as 'date'
    ) table1
where str_to_date(`date`,'%d-%m-%Y') between str_to_date('10-10-2017','%d-%m-%Y')  and str_to_date('11-11-2017','%d-%m-%Y') 
order by str_to_date(`date`,'%d-%m-%Y') 


    date        str_to_date(`date`,'%d-%m-%Y')
1   10-10-2017  10.10.2017 00:00:00
2   11-11-2017  11.11.2017 00:00:00

Demo

In addition: The way "between" is implemented in SQL is that the first value must be lower than the second value (i.e. the sequence of the value IS vital). That is because "between" is really just a syntax shortcut for somevalue >= compare_val_low and somevalue <= compare_val_high if you reverse the comparison values it will return NULL unless somevalue happens to equal one or both of the comparison values. Using strings it will be easy for what appears to be a lower date, to in fact be a "higher value" due to the nature of string ordering, e.g. this might "look" ok:

between '29-08-2017' and '11-11-2017'

but it is not OK at all because '29' is higher than '11' and between will return no rows.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I see. Thanks for example. I guess the only way of doing this with variables would be to change the format of the date to yyyy-mm-dd

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.