CODEWITHSUNDEEP
phpmysqldatetimeselect
Wilfred Bolten
Wilfred Bolten

Reputation: 9

Select query with DateTime not working

It is my goal to query my table for records that exist between two dates and count the number of times a name is returned. If the value returned is more then three then I will print it to the screen.

For some reason it does not work for me.

Running PHP 5.3 with MySQL.

This is part of the code

<?php
$Today = @date('Y-m-d H:i:s');
echo "Current Date: " . $Today . "<br>";

$FirstDate = strtotime(date('Y-m-d') . ' -3 day');
echo "First date: " . (date('Y-m-d',$FirstDate)) . "<br>";

$result = mysqli_query($con,"SELECT * FROM mytablename WHERE DateTime > '" . $FirstDate . "'");
$num_rows = mysqli_num_rows($result);
echo $num_rows . "<br>";

The code continues from here to writing it to the screen. That part is working so I left it out.

The result with the above query simply returns all the records in the database and not only the records that are greater then the current date -3 days. Replacing the variable $FirstDate with '2014-12-12' does give the desired result.

Where is it going wrong? ORDER BY DateTime DESC seems to be sorting correctly so it does see it as a date.

Upvotes: 1

Views: 836

Answers (3)

Alex Szabo
Alex Szabo

Reputation: 3276

You can define this in the SQL Command as well

SELECT * FROM mytablename WHERE DateTime > CURDATE() - INTERVAL 3 DAY

Upvotes: 3

fire
fire

Reputation: 21531

strtotime returns a unix timestamp not a Y-m-d string date which is probably what your looking for...

$FirstDate = date('Y-m-d', strtotime('-3 days'));

Upvotes: 0

Jason Roman
Jason Roman

Reputation: 8276

When you say $FirstDate = strtotime(date('Y-m-d') . ' -3 day'); it's converting it to a unix timestamp, not a date string. Try this:

$FirstDate = date('Y-m-d', strtotime('-3 days'));

Upvotes: 0

Related Questions