php sql between date select

Question

So I have gathered the following code outputs fine and show me today's date and also the date 10 months ago:

$today = date("Y/m/d");
$months = date("Y/m/d", strtotime("-10 months"));

echo $today; // 2016/03/20
echo "</br>";
echo $months; //2015/05/20

Now I am trying to use the following code. I have a 'date_of_test' column in my DB and I only want it to show pupils tested in the last 10 months. However, I am getting no results even know there are dates in the DB within the last 10 months. If I swap the order of today and months, I only get dates between Jan and now. Any ideas?!

$query = "SELECT * FROM year9_records WHERE date_of_test BETWEEN '$today' AND '$months'";
$result = mysqli_query($conn,$query) or die("Error".mysqli_error($conn));
$row = mysqli_fetch_assoc($result);
$num_rows = mysqli_num_rows($result);

Have you tried switching $today and $months in your query? In your current version the first limit is bigger than the second. I'm not entirely sure if that's allowed. — Gentle153
– Gentle153, Commented Mar 20, 2016 at 19:20
Try $query = "SELECT * FROM year9_records WHERE date_of_test BETWEEN date_format(date('$today'),'%Y/%m/%d') AND date_format(date('$months'),'%Y/%m/%d') "; — Matt
– Matt, Commented Mar 20, 2016 at 19:21
Thanks a lot. All queries given have worked the same in terms of the output I want!! — Aoife Woodrow
– Aoife Woodrow, Commented Mar 20, 2016 at 19:32

Mawia HL · Accepted Answer · 2016-03-20 20:24:42Z

1

I would prefer an sql solution. You can use DATE_SUB:

"SELECT * FROM year9_records WHERE date_of_test >= DATE_SUB(now(), INTERVAL 10 MONTH)";

DATE_SUB() function subtracts the interval from the given date. In this case now() - 10 months.

If you prefer the PHP solution, and assuming you stored date_of_test as DATE in your table, the most appropriate way for looking back 10 months ago would be:

$tenmonthsback = date("Y-m-d", mktime(0, 0, 0, date("m")-10, date("d"),   date("Y")));
"SELECT * FROM `year9_records` WHERE `date_of_test` BETWEEN '$today' AND '$tenmonthsback'";

edited Mar 20, 2016 at 20:24

answered Mar 20, 2016 at 19:29

Mawia HL

3,6751 gold badge28 silver badges48 bronze badges

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

Comments

Adam Silenko · Accepted Answer · 2016-03-20 20:58:10Z

1

if you testing date using between condition you must have first date on the left site "And".

$query = "SELECT * FROM year9_records WHERE date_of_test BETWEEN '$months' AND '$today'"

it's equal to:

$query = "SELECT * FROM year9_records WHERE date_of_test >= '$months' AND date_of_test <= '$today'"

if the date_of_test have time you need add one day to get all today hours, not only 00:00

read this: https://docs.oracle.com/cd/B28359_01/server.111/b28286/conditions011.htm

if you don't have dates after today you don't need between:

$query = "SELECT * FROM year9_records WHERE date_of_test >= '$months'"

edited Mar 20, 2016 at 20:58

answered Mar 20, 2016 at 19:28

Adam Silenko

3,1062 gold badges19 silver badges33 bronze badges

Collectives™ on Stack Overflow

php sql between date select

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related