PHP/Mysql Dates

[bigheadedd]

Hi All,

 

Bit stuck on something I think should be quite simple.

I have a table which includes events that have a state date and end date (startdate, enddate).

 

I've got a search function that pulls out with various criteria etc, however i'm now doing a search by date.

 

For example, an event may start on 1st Jan 2011 and finish on 1st May 2011.

If my search for which events are on on the 2nd February; it should pull the above event out, as it lies inbetween those dates.

 

SELECT * FROM events WHERE ((DAY(events.startdate <= '".date('d',$day)."') AND DAY(events.enddate >= '".date('d',$day)."')) AND MONTH(events.startdate <= '".date('m',$day)."') AND MONTH(events.enddate >= '".date('m',$day)."'))

 

Theres a bunch of other stuff joining tables etc, but you get the idea.

For some reason this isn't working?

 

I've got it working by month, but month AND day is eluding me. I also need to add in the year, but that isn't so important at the moment.

 

Any help would be amazing!

 

Thanks

 

E

 

 

 

[nethnet]

Are your fields for startdate and enddate the mysql DATE type?  If so, you could just do this:

 

SELECT * FROM `table` WHERE `startdate` < NOW() AND `enddate` > NOW()

[bigheadedd]

No, they are TIMESTAMP.

 

Using NOW() wouldn't work as I need to do something like this

 

WHERE startdate <= date(01 01 2011) AND enddate >= date(01 05 2011)

 

 

Hope that makes sense?

[nethnet]

I see, I read that wrong.  I thought you were searching based on today's date.

 

But regardless, TIMESTAMP is essentially the same as DATETIME, so just build a string around the search date, and use that in place of NOW() in the example I posted.

 

$searchdate = $_POST['year'] . "-" . $POST_['month'] . "-" . $_POST['day'] . " 12:00:00";

[bigheadedd]

Thanks for that; although it still isn't working?

 

$searchdate = date('d',$day)."-".date('m',$day)."-".date('Y',$day)." 00:00:00";
WHERE events.startdate < '".$searchdate."' AND events.enddate > '".$searchdate."'

 

if I add `` to my field names it comes up with invalid field names? Is this my problem?

[PFMaBiSmAd]

What type of value is in $day ?

[bigheadedd]

Just got it working!

I realized that the date was YYYY-MM-DD 00:00:00 and not DD-MM-YYYY 00:00:00

 

!

 

Thanks so much for your help!

 

E