I'm building a scheduling module for a large project. I am storing a START time and STOP time in UNIX timestamp format in MySQL INT fields. I didn't use the formal timestamp field type, as I didn't want to deal with the automatic update feature associated with it. Furthremore, (I thought - not so sure now) that storing them in UNIX timestamp format would save me some steps in terms of converting back and forth.
So my table looks like this:
CREATE TABLE sched_entries (
entry_id mediumint(8) NOT NULL auto_increment,
emp_id tinyint(8) default NULL,
start int(12) default NULL,
stop int(12) default NULL,
PRIMARY KEY (entry_id)
) TYPE=MyISAM;
Just to be crystal clear, I store UNIX timestamps in the START and STOP fields. I simply need a method to test these for matches against specific days. So if the end user has asked to look at March 2003, I need to form a query that is run once for each day of March - looking for matches for that particular day. I guess the only other catch may be that I do need to order them by time so I can output them in chronological order on the calendar, but I assume I can order the results with ease - it's the select that's got me pulling my hair out all night!
If this is super simple, I apologize. I am coming from a PERL and ColdFusion background, and this is my first encounter with a calendar/schedule type application in PHP/MySQL. I did look in the PHP Function reference and the MySQL date functions, and while I found bits an pieces of stuff tha I thought I could use - I just couldn't put it all together. Thanks in advance!
So my table looks like this:
CREATE TABLE sched_entries (
entry_id mediumint(8) NOT NULL auto_increment,
emp_id tinyint(8) default NULL,
start int(12) default NULL,
stop int(12) default NULL,
PRIMARY KEY (entry_id)
) TYPE=MyISAM;
Just to be crystal clear, I store UNIX timestamps in the START and STOP fields. I simply need a method to test these for matches against specific days. So if the end user has asked to look at March 2003, I need to form a query that is run once for each day of March - looking for matches for that particular day. I guess the only other catch may be that I do need to order them by time so I can output them in chronological order on the calendar, but I assume I can order the results with ease - it's the select that's got me pulling my hair out all night!
If this is super simple, I apologize. I am coming from a PERL and ColdFusion background, and this is my first encounter with a calendar/schedule type application in PHP/MySQL. I did look in the PHP Function reference and the MySQL date functions, and while I found bits an pieces of stuff tha I thought I could use - I just couldn't put it all together. Thanks in advance!