Jump to content

Problems with an appointment script


spyke01

Recommended Posts

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
Link to comment
Share on other sites

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)
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.