Select data between two dates?

Question

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! :)

Answer 1

Use the BETWEEN keyword:

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

Answer 2

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')