Mysql Query? How to search between 2 dates?

[nfear24]

Im using PHP to access MySql. Im new to MySQL so having some slight problems. Here is what I have to just search by the date.

$query = "select * from record where ".$searchtype." like '%".$searchterm."%'";

Now I want to modify that so i can search between 2 dates. So if they enter 3/19/2003 in a box and 3/23/2003 in another it will return all the results between and including those dates. How do I query this with mysql. Any examples of queries between 2 dates would be great.

Thanks

 

[gogirl]

SELECT * FROM my_table WHERE date BETWEEN '$begin_date' AND'$end_date';

BETWEEN is inclusive - it includes both the beginning and end. This
is clearer but the same as:

... WHERE '$begin_date' <= date AND '$end_date' <= date;

 

[sleipnir214]

One gotcha, though...

MySQL will only accept dates in the form of YYYY-MM-DD. If your users are entering them in a different format, in PHP you can use something like:

$thedate = date ('Y-m-d', strtotime($user_entered_date));

or string manipulation to get the date in the right format for MySQL. Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[JustKIDn]

Give a man a fish, he eats for a day. Teach him to fish and he eats for a lifetime. (or until the pond runs out of fish!)

Go here for a lot of your date time questions.
Look towards the bottom of the page in user comments. Where people have entered helps for questions like this one.

http://www.mysql.com/doc/en/Date_and_time_functions.html

HTH
tgus

____________________________
Families can be together forever...

 

[nfear24]

MySQL will only accept dates in the form of YYYY-MM-DD. If your users are entering them in a different format, in PHP you can use something like:

$thedate = date ('Y-m-d', strtotime($user_entered_date));


Hey I like this idea, One question though. If the user enters 3-4-2003 when it goes to convert to y-m-d how does it know to make the 3 the month and not the 4 and vice versa. I would like to use this somehow so user dont have to enter 2003-3-4. Does it support 3/4/2003 with slashes and not dashes. I got the search between 2 dates part working but I cant get it to convert the users date.

thanks

 

[nfear24]

I guess I got it to work with that date conversion.