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 Code (markup): 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?
That's like a black thing that's white. If it has duplicates (which it would) it can't be autoincrement, which can't have duplicates. Autoincrement is meant as an ID field for the table, not for anything else. This is one reason to not use a framework - if you're writing your own code you wouldn't be having any problem.
You can run SQL $sql= "ALTER TABLE Table_name AUTO_INCREMENT=".$newuserid You could set it to 0 and MySQL is automatically going to take the next incremental value based on highest value in the primary key field. However, this is not efficient coding. I don't like CakePHP myself and I have evaluated that framework for my applications.
Thank you. 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;
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
Based on your previous example, you cannot do this. Your primary key and auto increment column is `id`. A table cannot have more than 1 auto increment column. You're going to have to implement this on an application level if you don't want to modify the database.
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
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) )
I was asked at another forum why I want a sub_tile_id and this is my explanation. I'd like to know if it is even really necessary after all. The jquery at http://myownmealplanner.com/mealplans/add contains the following code (see the view source): for (var i=1;i<100;i++){ $( "#draggable" + i ).draggable(); } Each draggable div uses the primary key of the tiles table to create its own name, for example draggable1, draggable2,..., and on up to draggable100. The tile id (primary key) gives the draggable div its name and I am looping through 100 of these. If I have five users who each have 20 meal tiles then I have already exhausted all of the names available at 100 (5 times 20 being 100). Of course I could just loop over 200 meal tiles instead, keep adding users and keep looping over ever more meal tiles but it seems like a bad idea. Wouldn't the jquery slow down quite a bit if I loop through for example 500 tiles? If instead each user has his own set of meal tiles then I would grab the user id and the sub_tile_id so it would never be more than maybe 10 or 20 to loop through.
But I would still have the same problem. User number 2 would get tiles 101 thru 200, user number 3 would get 201 thru 300 and so on. So I would have to loop through too many tile ids.
Not if you wrote it so that the "tile number" was actually the user number plus the tile number (from 1-100). Then user 1 would get tiles 1-1 to 1-100, user 2 would get tiles 2-1 to 2-100, etc. There's no rule that tile "numbers" have to be created by an autoincrement field in a database - you can designate the tiles in any way you choose. You just chose a bad way to do it. (It's actually easily doable with an autoincrement field - the computer doesn't care if a user has tiles 1-1 to 1-100 or tiles 99901 to 100000. That's only something that bothers you.)