Problems with an appointment script

[spyke01]

hi guys once again im stuck with timestamps. im making an appointment tracking script, itll have 4 sections: todays appointments, future appointments, past appointments, and add appointments, theyll all be on 1 page, and ive gotten the basics working ive just got a few problems.

im using unix timestamps because i have several functions for them and i need to track date and time, i use 1 database field for both and run it through a function to get them seperately

the problems im having is i need to find only the appointments for the current date in 1 query, then in the next on only the ones in the future, and then the past ones. im also needing to organize them so the one that occurs the soonest will be at the top of the list

heres my database export:
[code]
CREATE TABLE `appts` (
  `appts_id` mediumint(8) NOT NULL auto_increment,
  `appts_user_id` mediumint(8) NOT NULL default '0',
  `appts_status` smallint(1) NOT NULL default '0',
  `appts_name` varchar(250) NOT NULL default '',
  `appts_date` int(11) NOT NULL default '0',
  `appts_where` varchar(100) NOT NULL default '',
  `appts_phone` varchar(15) NOT NULL default '',
  `appts_about` varchar(250) NOT NULL default '',
  PRIMARY KEY  (`appts_id`)
) TYPE=MyISAM AUTO_INCREMENT=3;

--
-- Dumping data for table `appts`
--

INSERT INTO `appts` VALUES (1, 1, 1, 'Dean Cook', 1137864600, 'high school', '979-979-9797', 'some appt');

[/code]

this project can still handle a database change, so if i need to change to something else i can, i just need to make sure i have date, time and is organized like i explained.

if anyone could help id greatly appreciate it

[fenway]

I'm not sure I understand a) why you're storing this a UNIX TIMESTAMP and not a proper mysql DATETIME column, and b) why you can't use =, >, and order by to solve your query concerns. Please clarify.

[spyke01]

i dont know how to use a datetime column, and for the querys if youre using a datetimestamp i also dont know how to do those comparisons, basiclly i just need to store the date and time i give it, and also print out only todays, only those in the past, and only those in the future(seperately of course)

[fenway]

Ok -- first, make a DATETIME column called `appts_date`, not an INT. Second, when you INSERT records into this table, you need to convert them from your unix_timestamp value that you have in your app -- use the FROM_UNIXTIME() function. Third, when you want to do you queries, simply do something ilke the following (e.g. for today's appointments):

[code]SELECT * FROM appts WHERE appts_date = CURDATE()[/code]

You can easily change this to past/future using standard comparison operators as discussed.

Hope that helps.