Comparing a string-date with another string-date in MySql query

Question

I'm trying to get entries from MySql server where the dates are > date_begin and < date_end

My problem is that the table column "date" is set to VARCHAR, and the string I am comparing it is also directly from an <input type="text"/>

The date format ( as string / varchar ) in the mysql table and in the textbox will be DD.MM.YYYY

my problem is that if i do

( php code )

 $query = "SELECT * FROM table WHERE date>='" . $dateBegin . "' AND date<='" . $dateEnd . "'";

the result will be completely random.

I know why this happening so the question is: How can I compare this to get the right result ?

thank you all who can help

UPDATE :

Now I've tryed this solution, but it also doesn't work.

$query = "SELECT * FROM artikel WHERE STR_TO_DATE(datum, '%d.%m.%Y')>=STR_TO_DATE('" . $_REQUEST['dateTo'] . "', '%d.%m.%Y')" . "' AND STR_TO_DATE(datum, '%d.%m.%Y')<=STR_TO_DATE('" . $_REQUEST['dateTo'] . "', '%d.%m.%Y')";

the $_REQUEST['dateFrom'] and $_REQUEST['dateTo'] came from 2 textboxes and the input is a string like this "05.06.2005".

in the db-table 'artikel' (german for articles) in the column 'datum' (german for date) the values are VARCHAR's with the same format ( "05.06.2005" ).

Solution 1

Always compare dates with dates, integers with integers etc.

With MySQL you can use the str_to_date functions to specify how your string-date is to be processed.

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

e.g. if your date column is called "mydate":-

SELECT * from table
where STR_TO_DATE(mydate, '%m/%d/%Y') >= STR_TO_DATE('04/31/2004', '%m/%d/%Y')
...

And always ensure you're validating any string input from the user to avoid SQL injection attacks.

EDIT: as Dems points out above, this is suboptimal as a date value should be stored as date value in the database.

Solution 2

you can use between

"SELECT * FROM table WHERE date between'" . $dateBegin . " AND " . $dateEnd . "'";