bschultz Posted June 17, 2009 Share Posted June 17, 2009 I have a table that is: row_number - int 5 - primary - auto increment team_name - varchar 200 week - int 2 miles - decimal 10,2 I didn't really need the row_number column...but figured that it couldn't hurt. Now, I have teams entering miles multiple times for the same week. I should have combined the team name and week into one column and made that the primary key. Oh well...too late now. Is there anyway to limit a team to entering miles walked just once each week in this db setup? Maybe a php solution where if data exists for a week, it removes that week from the drop box? That would work, but I have no idea how to write that (figure out which weeks exists--1 through 13--) and then echo the drop box accordingly. If this doesn't make sense, let me know...I'll try to explain better. Thanks! Quote Link to comment https://forums.phpfreaks.com/topic/162575-solved-i-messed-up-in-the-design-of-my-dbnow-i-need-a-work-around/ Share on other sites More sharing options...
kickstart Posted June 17, 2009 Share Posted June 17, 2009 Hi You can easily add a unique key on the team name and week if you want. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/162575-solved-i-messed-up-in-the-design-of-my-dbnow-i-need-a-work-around/#findComment-858057 Share on other sites More sharing options...
bschultz Posted June 17, 2009 Author Share Posted June 17, 2009 team name will be the same each week, though...so it won't be unique. That's why I said that I should have combined those two columns. Quote Link to comment https://forums.phpfreaks.com/topic/162575-solved-i-messed-up-in-the-design-of-my-dbnow-i-need-a-work-around/#findComment-858078 Share on other sites More sharing options...
kickstart Posted June 17, 2009 Share Posted June 17, 2009 Hi That is what I mean. You can have a UNIQUE type index on multiple columns. Duplicates on one or the other will be fine, duplicates of both will be blocked. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/162575-solved-i-messed-up-in-the-design-of-my-dbnow-i-need-a-work-around/#findComment-858080 Share on other sites More sharing options...
bschultz Posted June 17, 2009 Author Share Posted June 17, 2009 phpmyadmin threw an error on unique for week...this is what the table could look like team_name week miles team xyz 1 112.21 team abc 1 77.6 team xyz 2 100.57 team abc 2 98 Quote Link to comment https://forums.phpfreaks.com/topic/162575-solved-i-messed-up-in-the-design-of-my-dbnow-i-need-a-work-around/#findComment-858109 Share on other sites More sharing options...
kickstart Posted June 17, 2009 Share Posted June 17, 2009 Hi Had a play and it worked fine. On the structure page for you table under indexes, put a 2 in the number of columns to add an index and click go. Type in an index name, change the drop down to "UNIQUE", and select the 2 columns from the drop down lists. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/162575-solved-i-messed-up-in-the-design-of-my-dbnow-i-need-a-work-around/#findComment-858289 Share on other sites More sharing options...
bschultz Posted June 17, 2009 Author Share Posted June 17, 2009 thanks...there was a multiple entry for one of the teams for this week that I hadn't caught...that's what threw the error. All is working now....thanks! Quote Link to comment https://forums.phpfreaks.com/topic/162575-solved-i-messed-up-in-the-design-of-my-dbnow-i-need-a-work-around/#findComment-858358 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.