[PHP] php and mysql date mapping question
Hi all, I am in the process of creating a national events directory where people can enter their events (car boot sales, evening classes etc) and specify whether those events are one-ff events or repeating (daily, weekly etc) affairs and people can search for those events by postcode, date range, category etc. I have a table containing all dates between Oct 1 2006 and 2030, including leap years etc. I am pretty new to relational design so here's my question: I have an events table and a dates table. As an event can happen on many dates and a date can hold many events, I created an intermediary table called dates_events to express that many-to-many relationship. The data entry works like a dream as does the search. However, the dates_events table is growing quite large (200,000 mapped relationships for 300+ test events), but the logic I used seemed clear. When the system goes live, allowing for a sweeper script that removes outdated mappings from all three tables, I estimate that this mapping table may grow to 2-3 million records at least. Does this sound flawed, and will mysql handle this kind of data volume? Anyone have any experience building a similar system. I am happy to use this method for the initial test run but may re-engineer it before we go national if suggestions lead me that way. I am unable to release the URL as yet. -- http://www.web-buddha.co.uk
Re: [PHP] php and mysql date mapping question
Dave Goodchild wrote: Hi all, I am in the process of creating a national events directory where people can enter their events (car boot sales, evening classes etc) and specify whether those events are one-ff events or repeating (daily, weekly etc) affairs and people can search for those events by postcode, date range, category etc. I have a table containing all dates between Oct 1 2006 and 2030, including Dave, if you are starting a calendar/event DB with a table full of dates something is probably wrong. you only need to store dates for actual events (in theory). leap years etc. I am pretty new to relational design so here's my question: I have an events table and a dates table. As an event can happen on many dates and a date can hold many events, I created an intermediary table called dates_events to express that many-to-many relationship. The data entry works like a dream as does the search. However, the dates_events table is growing quite large (200,000 mapped relationships for 300+ test events), but the logic I used seemed clear. When the system goes live, allowing for a sweeper script that removes outdated mappings from all three tables, I estimate that this mapping table may grow to 2-3 million records at least. asuming you tables are correctly indexes and the relations are correctly defined record count is not the limiting factor at all. it does sound like you have one table too many. a one to many relationship between an events table and a dates table should suffice. then again your denormalized design may allow for much faster data retrieval, in which case stick with it :-) only one way to find out though. Does this sound flawed, and will mysql handle this kind of data volume? yup :-) use InnoDB as the storage format and even the 4Gig limit is history. Anyone have any experience building a similar system. I am happy to use this method for the initial test run but may re-engineer it before we go national if suggestions lead me that way. I am unable to release the URL as yet. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] PHP and MySQL date
Hi all, I'm making a site and need some tables from wich I can extract date, time and/or date/time later with PHP and I neved had very clear the way the possible formats work with each other so my question is what is the best (or recommended) method to store dates and/or times in a MySQL DB for PHP to work later? Thanks in advanced, ___ Cesar L. Aracena Commercial Manager / Developer ICAAM Web Solutions 2K GROUP Neuquen, Argentina Tel: +54.299.4774532 Cel: +54.299.6356688 E-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP and MySQL date
If you need to store both date and time, there are several ways to accomplish this. MySQL has built-in date/timestamp options, but those long strings would need to be converted for be human readable. The option I use most is telling PHP what format I want to use and entering it into the database as a variable character (VARCHAR) field. $Today = date((Y-m-d H:i),mktime()); Returns: 2003-12-15 18:20 See the PHP help file for mktime() and date() Jim www.websitemanagers.net - Original Message - From: Cesar Aracena [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, December 15, 2003 5:23 PM Subject: [PHP] PHP and MySQL date | Hi all, | | I'm making a site and need some tables from wich I can extract date, time | and/or date/time later with PHP and I neved had very clear the way the | possible formats work with each other so my question is what is the best (or | recommended) method to store dates and/or times in a MySQL DB for PHP to | work later? | | Thanks in advanced, | ___ | Cesar L. Aracena | Commercial Manager / Developer | ICAAM Web Solutions | 2K GROUP | Neuquen, Argentina | Tel: +54.299.4774532 | Cel: +54.299.6356688 | E-mail: [EMAIL PROTECTED] | | -- | -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
