Question about Dates

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
JPlush76
Forum Regular
Posts: 819
Joined: Thu Aug 01, 2002 5:42 pm
Location: Los Angeles, CA
Contact:

Question about Dates

Post by JPlush76 »

I'm creating a display system for current events. I have a page that displays a list of months June July August, etc...

when you click on July for example I want to have all the events that fall within July appear. In my database I have a start date and an end date field.

The problem I'm having is if something falls in the middle of the month such as an event that goes July 15th - July 19th.

Whats the best way to pull these records out of the database?


The code I have right now only pulls out the record if its between July 1st and July 31st. I'm having a brain fart and can't think of the solution.

Here is the mysql code I'm using

Code: Select all

<?php
SELECT * FROM board_events as be, board_member as bm WHERE be.member_id = bm.member_id AND '$key' BETWEEN start_date AND end_date
?>
the $key would be the current month as 07-01-2003 so you can see why the Between isn't working

Here is some ugly code I can use that selects EVERYTHING in the events table then loops through for what matches the current month. It works but I think its rather inefficient.

Code: Select all

<?php
$result = query_db("SELECT first_name, last_name, page, picture, location,event_desc, DATE_FORMAT(start_date,'%m') as start, "
."DATE_FORMAT(end_date,'%m') as end, DATE_FORMAT(start_date,"%M %e, %Y") as sdate, "
."DATE_FORMAT(end_date,"%M %e, %Y") as edate FROM board_events as be, board_member as bm WHERE be.member_id = bm.member_id");


while($row = mysql_fetch_array($result))
{
	if($key >= ($row['start']) AND $key <= ($row['end']))
	
	{
		echo '<TR>';
	
		echo '<TD valign="top">';
		echo ($row['sdate']).' - <BR>'.($row['edate']);
		echo '</TD>';
	
		echo '<TD valign="top">';
		echo '<a href="'.($row['page']).'">'.($row['first_name']).' '. ($row['last_name']).'</a><BR>';
		echo ($row['event_desc']);
		echo '</TD>';
	
		echo '<TD valign="top" align="right">';
		echo ($row['location']).'<BR><img src="'.($row['picture']).'" width="65" height="65" border="1">';
		echo '</TD>';
	
	
		echo '</TR>';
	
	} // end if date check

}

?>
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

don't you want to show all events that begin (you might truncate the output if it extends the interval) in that month?
if so for the current date/month the query should like

Code: Select all

SELECT blablabla WHERE start_date BETWEEN '2003-06-01' AND '2003-06-30'
having the first day of the month (which shouldn't be too hard to calculate ;) ) you can let mysql do the work to determine the last day, e.g. try

Code: Select all

SELECT '2003-06-1' as firstDayOfJune, ('2003-06-1' + INTERVAL 1 MONTH - INTERVAL 1 DAY)  as lastDayOfJune
Post Reply