Auto Increment Problem

[makamo66]

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?

[makamo66]

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;

[makamo66]

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;

[makamo66]

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;

[makamo66]

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

[makamo66]

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

[rh_lloydnorthov]

I don't believe you can have two auto-increment fields in a table, and it looks like the 'id' field is set to auto-increment.

I think your'e going to have to
a) query the table to see if the user_id for your new entry already exists

SELECT user_id,sub_tile_id FROM table WHERE user_id = [user id] ORDER BY sub_tile_id DESC LIMIT 1

(read the entry from the table with the same user_id as your new entry, and the highest sub_tile_id value)

b)if it does, read the highest sub_tile_id, increment it by one and use this value to insert a new row.
c) if the user_id does not exist, insert a new record with the new user_id and the sub_tile_id set to 1.

[makamo66]

Even if I remove the auto increment id and change sub_id to auto increment (since only one auto-increment is allowed) I still can't reset the auto increment to a value less than it has already been. I can only restart the auto increment to a higher value and I don't want to do that.

[rh_lloydnorthov]

Auto increment fields are supposed to be unique for each record in the table as they are designed to be used for record identification, and cannot be repeated.

Since you want to have repeated values in the sub_tile_id field you can't use auto increment on that field, and you will have to manually generate the values.

[NogDog]

You could probably use a sub-query to get the greatest tile_id so far for that user_id, and add 1 to it (or set it to 1 if it returns null).

$sql = "
INSERT INTO tiles (user_id, sub_tile_id)
VALUES (
    $userId,
    IFNULL((SELECT MAX(sub_tile_id) FROM tiles WHERE user_id=$userId), 1)
)
"; 

However, my suspicion is that this DB design may not be ideal if it's causing this sort of headache; but without any understanding as to what you are actually trying to represent with it, I'm not sure what a better design might be.

[NogDog]

PS: You'd probably want to change the IFNULL line above to:

IFNULL((SELECT MAX(sub_tile_id) FROM tiles WHERE user_id=$userId), 0) + 1

[makamo66]

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)
)

[makamo66]

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.

[Derokorian]

You could instead give the divs a class named draggalble and change those 3 jquery lines to:

$('.draggable').draggable();

[makamo66]

No because I need to know which meal tile was dragged. They have to be identifiable. You have to submit to the next page which divs you dragged, that is which meal tile you chose.