Getting an MySQL first day and last day statement

Hi,

On a search form, we are going to have someone pick a month fo the year
for which they'd like to search for trips. Here is an example option
from the menu

March 2006

How can I write a MySQL statement that asks "give me all trips between
(and including) the first day of March, 2006 and the last day of March,
2006?"

I'm using MySQL 4, PHP 4.

Thanks, - Dave

Re: Getting an MySQL first day and last day statement

> How can I write a MySQL statement that asks "give me all trips between
> (and including) the first day of March, 2006 and the last day of March,
> 2006?"

It would help to see the definitions for the tables involved. However,
this is roughly what you need, adapt as required...

SELECT *
FROM trips
WHERE startdate BETWEEN '20060301' AND '20060331'

---
Steve

Re: Getting an MySQL first day and last day statement

Thanks for this answer. I guess my question is a little more general.
Let's say the month isn't hard-coded, but I am guaranteed to have it in
the form

$monthVar = "YYYY-MM-DD HH:MI:SS.0";

How would I write a MySQL query that searches between the first and
last days of the month represented by the above?

Re: Getting an MySQL first day and last day statement

> Thanks for this answer. I guess my question is a little more general.
> Let's say the month isn't hard-coded, but I am guaranteed to have it in
> the form
>
> $monthVar = "YYYY-MM-DD HH:MI:SS.0";
>
> How would I write a MySQL query that searches between the first and
> last days of the month represented by the above?

Ah, a different question with a different answer. Using PHP & MySQL:

$sql = "SELECT * FROM trips WHERE MONTH( startdate ) = MONTH(
\"$monthVar\" ) AND YEAR( startdate ) = YEAR( \"$monthVar\" )";

---
Steve

Re: Getting an MySQL first day and last day statement

Thanks. I really appreciate the quick responses. This answers the
question perfectly. I must ask another, however. If "startdate" has
an index on it, I've read that MySQL would not utilize that index if
the column is located within a function -- e.g. MONTH(startdate). Is
there any way to isolate "startdate" while still asking the same thing?

- Dave

Re: Getting an MySQL first day and last day statement

> Thanks. I really appreciate the quick responses. This answers the
> question perfectly. I must ask another, however. If "startdate" has
> an index on it, I've read that MySQL would not utilize that index if
> the column is located within a function -- e.g. MONTH(startdate). Is
> there any way to isolate "startdate" while still asking the same thing?

You could create 2 new columns for startmonth and startyear, computed
from startdate, and index those instead - and obviously modify your
select query accordingly.

---
Steve