Kay1021 Posted June 26, 2009 Share Posted June 26, 2009 I'm wondering if someone might be able to help me with some problem solving...i'm getting myself confused. i have tables in my database for example Dogs2009-2010 Cats2009-2010 Birds2009-2010 Now I'm trying to find a way that every September I can add new tables so now i would have Dogs2009-2010 Cats2009-2010 Birds2009-2010 Dogs2010-2011 Cats2010-2011 Birds2010-2011 But i'm getting confused on how I could go about knowing the year changed I know you can use date('Y') to tell the current year but how i compare to tell the year has changed and if it has create a new table my yr goes from Sept -June so right now the tables would have 2008-2009 Hope this makes sense Thanks Because then when calling them i could say Dog.$year Hopefully someone can help me out....maybe there is a better way to do this all together. Link to comment https://forums.phpfreaks.com/topic/163789-new-table-every-yr-db-problem-solving/ Share on other sites More sharing options...
ninedoors Posted June 26, 2009 Share Posted June 26, 2009 How much data is going to be in the table? If it isn't a large amount of data each year why not create a date column so you can have them all in one table. Just a thought. Link to comment https://forums.phpfreaks.com/topic/163789-new-table-every-yr-db-problem-solving/#findComment-864206 Share on other sites More sharing options...
Kay1021 Posted June 26, 2009 Author Share Posted June 26, 2009 it's kind of a lot that's why i thought it would probably be easier just to make new tables Link to comment https://forums.phpfreaks.com/topic/163789-new-table-every-yr-db-problem-solving/#findComment-864207 Share on other sites More sharing options...
ninedoors Posted June 26, 2009 Share Posted June 26, 2009 Sorry to ask again but what is a lot? 1 million rows? Link to comment https://forums.phpfreaks.com/topic/163789-new-table-every-yr-db-problem-solving/#findComment-864210 Share on other sites More sharing options...
Kay1021 Posted June 26, 2009 Author Share Posted June 26, 2009 sorry i guess i should have specified There are at least 150 but more could be added Basically what i'm creating is like a checklist....so there are 150 different tasks...but the admin can add more. right now the set up is these different columns cat_id - task - checked - date - comment so for every task there is going to be information saved in the checked , and date and comment. And i've been using the cat_id as a kind of way to know where to save the information but then when September comes around i need to reprint those categories...but now allow for new checked,date and comment information to be added but still have the option to go back and look at the previous yr(s) Hope that makes sense Link to comment https://forums.phpfreaks.com/topic/163789-new-table-every-yr-db-problem-solving/#findComment-864214 Share on other sites More sharing options...
ninedoors Posted June 26, 2009 Share Posted June 26, 2009 Kay, I wouldn't create a new table every year, I would just add a new auto_increment column called id to your table. Then you will be able to use them over and over and you could pull out the cat_id from a specific year with the mysql date functions. Are you using mysql? What data type is cat_id? Link to comment https://forums.phpfreaks.com/topic/163789-new-table-every-yr-db-problem-solving/#findComment-864222 Share on other sites More sharing options...
PFMaBiSmAd Posted June 26, 2009 Share Posted June 26, 2009 Your date column needs to include the year (the column should be a DATE data type YYYY-MM-DD) You don't create new tables for each year of data as that makes managing and accessing the data overly complicated. To query for any range of dates, you just use the starting YYYY-MM-DD and ending YYYY-MM-DD in your query - SELECT your_columns FROM your_table WHERE date BETWEEN '2008-09-01' AND '2009-08-31' Link to comment https://forums.phpfreaks.com/topic/163789-new-table-every-yr-db-problem-solving/#findComment-864224 Share on other sites More sharing options...
ikmyer Posted June 26, 2009 Share Posted June 26, 2009 yeah, biggest custom app i have made has about 70,000 records in one table... no sweat. Link to comment https://forums.phpfreaks.com/topic/163789-new-table-every-yr-db-problem-solving/#findComment-864238 Share on other sites More sharing options...
Kay1021 Posted June 26, 2009 Author Share Posted June 26, 2009 cat_id is an int auto_incremented column it joins to another table that contains all the tasks so that it could be used in different tables so my task table is like cat_idcat_name 134Wash Dishes 135Mop Floor and then my other table say Dog looks like this (... means empty) cat_idconfirmdatecomment 134checked06/26/2009Lots of Dirty Dishes! 135......... Throughout all my coding I used the cat_id as a way to know where to save the information For example $query = "UPDATE Dog SET comment = '.$comment."' WHERE cat_id = ".$catID.""; So then i wouldn't be able to have the same tasks every year because i wouldn't be allowed to have duplicate cat_id's The date i'm currently getting from a mootools datepicker so the only thing im worried about is what if the user chooses a yr that isn't the current yr then wouldn't everything get messed up. And the date field is empty until the user chooses the date. Thanks for the help i really appreciate it Link to comment https://forums.phpfreaks.com/topic/163789-new-table-every-yr-db-problem-solving/#findComment-864242 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.