1

I need to create a tiles table that has a structure like this for http://www.myownmealplanner.com:

id user_id sub_tile_id
1   1   1
2   1   2
3   1   3
4   2   1
5   2   2
6   2   3
7   3   1
8   3   2
9   3   3

etc. I can't just create new tables for new users because I'm using cakephp and that would require new models, views, and controllers for every new table. How do I get the sub_tile_id to auto_increment starting at every new user id?

Maureen Moore
  • 1,049
  • 2
  • 9
  • 21
  • After dropping the id field, I tried both of these and got syntax errors ALTER TABLE `tiles` MODIFY COLUMN `sub_id` INT NOT NULL AUTO_INCREMENT WHERE `user_id` = 2; UPDATE TABLE `tiles` MODIFY COLUMN `sub_id` INT NOT NULL AUTO_INCREMENT WHERE `user_id` =2; – Maureen Moore Jan 10 '13 at 15:06
  • For other stunts I've tried I've gotten the following error: there can be only one auto column and it must be defined as a key – Maureen Moore Jan 10 '13 at 15:30
  • ALTER TABLE t2 AUTO_INCREMENT = value; You cannot reset the counter to a value less than or equal to any that have already been used – Maureen Moore Jan 10 '13 at 16:17
  • This didn't work CREATE TABLE `tiles` ( `sub_tile` ENUM('1','2','3') NOT NULL, `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `user_id` INT(22) ) – Maureen Moore Jan 11 '13 at 13:50

0 Answers0