Re: PHP and mySQL dates

On Wed, September 13, 2006 5:56 am, Dave Goodchild wrote: > Hi all. I am building an online events registry and have mapped out > all the > dates between Oct 1 2006 and Dec 31 2030, stored in the database as > timestamps incremented by 86400 to give daily slots. I have output the > values using the php date function and all is well. Users can enter > either > one-off or regular events, and I am using a mapping table to tie > events to > dates as they comprise a many-to-many relationship. If I'm reading this correctly, you've created a table of every single date, just to provide a JOIN table of massive proportions? That's... Not Good (tm) almost for sure... > I am struggling with some date conversions, however. When a user > enters a > single event for example, the data is entered into the events table, > the > inserted id captured, and then the system will look for the relevant > record > in the dates table, and eventually enter the event id and date id into > the > mapping table for later joins during the search process. Now it sounds like you are inserting even MORE entries into another table to make an even more confusing JOIN... > To do this, I call: > > $date_string = > mktime(0,0,0,$_SESSION['month],$_SESSION['day'],$_SESSION['year']) > > to assemble a timestamp from the supplied user data, and now I need to > look > for the matching date in the dates table. My problem is in converting > between UNIX and mySQL timestamp values. My first attempt to match > used this > (query extract): > > "SELECT id FROM dates WHERE FROM_UNIXTIME($date_string) = date" "SELECT id FROM dates WHERE date = '$month/$day/$year'" has always worked for me... If date is a timestamp or datetime, you have to convert THAT to a date, so that the hours:minutes:seconds don't mess you up. You're making all of this way too hard ... :-) Re-think the concept of having a row for every possible day. Just make sure all your tests for date equality are using the same type of data -- DATE, not DATETIME, TIMESTAMP, etc. -- Like Music? http://l-i-e.com/artists.htm