Problems selecting a range of information.....

[scout1idf]

Hi, I'm working on a system to save records for my wife to take to the doctor and I need to print out only the records that we haven't taken to him already. I'm storing them in a MySQL database using PHP to both store and retrive. It's working great except for retrieving date ranges. I can retrieve either a single day or all of records. I need to be able to retrieve and print selected date ranges so we don't...

1.) duplicate records the doctor already has

2.) waste my ink and paper printing what we don't need

This is what I have.. Database example.....(obviously the real information is personal and more in-depth.)

 +----+-------+----+------+-------------+| id | month | day| year | information |+----+-------+----+------+-------------+|  1 |   2   | 3  |  13  | it's Sunday |+----+-------+----+------+-------------+|  2 |   2   | 4  |  13  | it's Monday |+----+-------+----+------+-------------+ etc..... +----+-------+----+------+-------------+| 40 |   3   | 14 |  13  | it's Thurs  |+----+-------+----+------+-------------+| 41 |   3   | 15 |  13  | it's Friday |+----+-------+----+------+-------------+ etc..... 

Example form to retrieve information.... (the real one is complete and styled nicely )

 <form>	 <h2>From:</h2>	 <input type='text' name='month1' />		   //beginning month	 <input type='text' name='day1'  />			 //beginning day	 <input type='text' name='year1'  />			//beginning year   	 <h2>To:</h2>	 <input type='text' name='month2'  />		   //ending month	 <input type='text' name='day2'  />			 //ending day	 <input type='text' name='year2'  />			//ending year 	 <input class='button' type='submit' value=' GO ' /></form> 

What I have/need help with.....

 SELECT * FROM healthWHERE (this is the part I can't figure out {month1 day1 year1}  {month2 day2 year2})ORDER BY day ASC 

Thanks in advance for your help....

[justsomeguy]

Unfortunately, having different columns for month, day, and year (and a 2-digit year, at that) makes that much more difficult than it should be. Consider saving the date as a single column, as an integer timestamp. With PHP, you can use the mktime function to get the timestamp for a certain date. You can use the date function to format a timestamp to display so that you can read it, or return certain parts of it. The getdate function will also return the parts. Since the timestamps are integers, if you're looking for a date range then you can get all records where the timestamp is within the start and end integers. It will probably be less of a pain to write a script to convert the database to timestamps, and then update your code to use those then it will be to write the code to make sense of that data. Looking at MySQL's date functions, it looks like you would need a combination of STR_TO_DATE and CONCAT that probably isn't going to make a lot of sense when you look at it in another year. e.g.: WHERE STR_TO_DATE(CONCAT(month, ',', day, ',20', year), '%c,%e,%Y') BETWEEN '2012-01-01' AND '2012-12-31'