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
