Re: [PHP] 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP and mySQL dates
Good luck with that. > -- http://www.web-buddha.co.uk http://www.projectkarma.co.uk -- http://www.web-buddha.co.uk http://www.projectkarma.co.uk
RE: [PHP] PHP and mySQL dates
No problem, now I will go and make my girlfriend happy :) -Original Message- From: Dave Goodchild [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 13, 2006 6:27 PM To: Peter Lauri Cc: PHP General Subject: Re: [PHP] PHP and mySQL dates Thanks. I have been so up close and personal with this that I can't see the wood for the trees. Of course, so obvious. Thank you - you have made me very happy. > > -- http://www.web-buddha.co.uk http://www.projectkarma.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP and mySQL dates
Thanks. I have been so up close and personal with this that I can't see the wood for the trees. Of course, so obvious. Thank you - you have made me very happy. -- http://www.web-buddha.co.uk http://www.projectkarma.co.uk
RE: [PHP] PHP and mySQL dates
[snip] 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. [/snip] I do not really understand the purpose of mapping all dates between Oct 1 2006 and Dec 31 2030 and store them into a database as a timestamp. First of all, a date is a date, not a timestamp. A timestamp is date and time together. Why don't you just save the events with the date as DATE format and then compare them with CURDATE() or similar. Or just with "$_SESSION[year]- "$_SESSION[month]- "$_SESSION[day]" Just some thoughts. /Peter -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] PHP and mySQL dates
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. 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. 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" then "SELECT id FROM dates WHERE UNIX_TIMESTAMP(date) = $date_string" neither is working. Am I making some fundamental error here or missing something? Any help appreciated! -- http://www.web-buddha.co.uk http://www.projectkarma.co.uk
