CODEWITHSUNDEEP
phpmysqlselectdate
Joey Morani
Joey Morani

Reputation: 26611

Select data between two dates?

I'm using a database to store logs, with a column "date" which holds the date it was inserted. The format of the date is "MM/DD/YY". Please can anyone suggest how I would SELECT data in between two certain dates. For example, I tried this: 

$from_date = "01/01/12";
$to_date = "02/11/12";

$result = mysql_query("SELECT * FROM logs WHERE date >= " . $from_date . " AND date <= " . $to_date . " ORDER by id DESC");

while($row = mysql_fetch_array($result)) {
// display results here
}

But I guess this doesn't work because the dates aren't numbers. Thanks for the help! :)

Upvotes: 5

Views: 88254

Answers (5)

MB_18
MB_18

Reputation: 2321

Change date parameters into Unix timestamps and then compare them. Here is the code:

$from_date = "2019/01/12";
$to_date = "2019/01/15";

$from_date_unix = strtotime($from_date);
$to_date_unix = strtotime($to_date);    

$result = mysql_query("SELECT * FROM logs WHERE date >= " . $from_date_unix . " AND date <= " . $to_date_unix . " ORDER by id DESC");
while($row = mysql_fetch_array($result)) {
// display results here
}

Upvotes: 0

Mike Dinescu
Mike Dinescu

Reputation: 55760

The answer to your question depends on the data type that is used to store the date field in the logs table.

SQL (MySQL in your case) is fully capable of comparing dates. Usually, the BETWEEN .. AND .. operator is used but that will not work correctly if the type of date is CHAR (or VARCHAR) - in which case you will need to cast the date field to a DATETIME before comparing.

Upvotes: 3

Brian
Brian

Reputation: 2229

You can cast the fields as dates and then select between from_date and to_date

SELECT * FROM logs WHERE date STR_TO_DATE(date, '%m/%d/%Y') between STR_TO_DATE(from_date, '%m/%d/%Y') and STR_TO_DATE(to_date, '%m/%d/%Y')

Upvotes: 3

Sarfraz
Sarfraz

Reputation: 382881

Use the BETWEEN keyword:

"SELECT * FROM logs WHERE date BETWEEN '" . $from_date . "' AND  '" . $to_date . "'
ORDER by id DESC"

Upvotes: 22

Stelian Matei
Stelian Matei

Reputation: 11623

You need to add single quotes to the date values '01/01/12':

$from_date = "01/01/12";
$to_date = "02/11/12";

$result = mysql_query("SELECT * FROM logs WHERE date >= '" . $from_date . "' AND date <= '" . $to_date . "' ORDER by id DESC");

Upvotes: 0

Related Questions