Q:

Search between dates (date as VARCHAR)

I have several dates stored as varchar in the following format: 01-01-2012
I want to search between dates, but sadly it isn't working as I expected. I looked for other threads which have the same question (kinda), but the answers provided there didn't work for me.
Here is my code:
$fromday = $_POST['fromday'];
$frommonth = $_POST['frommonth'];
$fromyear = $_POST['fromyear'];

$tillday = $_POST['tillday'];
$tillmonth = $_POST['tillmonth'];
$tillyear = $_POST['tillyear'];

$vanaf = "$fromday-$frommonth-$fromyear";
$tot = "$tillday-$tillmonth-$tillyear";

// zoeken
$sel = "SELECT * FROM bestelling WHERE verzenddatum >= '$vanaf' AND verzenddatum <= '$tot'";
$dosel = mysql_query($sel) or die(mysql_error());

while($row = mysql_fetch_assoc($dosel))
{
    $datum = $row['verzenddatum'];

    echo $datum;
}

Please let me know what I'm doing wrong!

A:

It is not returning any rows because the query is not doing the comparison on valid DATE or DATETIME fields. You should be storing the dates as a DATE type, but what you could do is this:
// Switch the order of date elements:
$vanaf = mysql_real_escape_string("$fromyear-$frommonth-$fromday");
$tot = mysql_real_escape_string("$tillyear-$tillmonth-$tillday");

$sel = "SELECT * FROM bestelling WHERE STR_TO_DATE(verzenddatum, '%d-%m-%Y') BETWEEN '$vanaf' AND 'tot'";

The mysql_real_escape_string() function simply reduces the risk of SQL injection, which is what your original code was vulnerable to.
The MySQL function STR_TO_DATE() converts a string to a valid MySQLDATE type. %d-%m-%Y is the format you have in your varchar string currently, but STR_TO_DATE converts it to '%Y-%m-%d' which MySQL can then use to make range comparisons.
Also, I'm using the BETWEEN syntax in the SQL as it is the same thing as val >= val1 AND val <= val2. It's just clearer and simpler: val BETWEEN val1 AND val2.

