Reset Auto Increment to lower value

PHP
1

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

user_id sub_tile_id
1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
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? According to the manual I can’t restart auto-increment with a lower value than it has already displayed so this needs to be done with php somehow.

2

You cant auto-increment this.

an auto-increment must be a unique value; you want to reuse 1 2 and 3.

EDIT: Sorry, misread your post. You can do this, but only if your index is a multi-field one (userid, sub_tile_id). Your table must be of type MyISAM.

3

You can run something like this:

$user_id = 3;

INSERT INTO table (user_id, sub_tile_id) VALUES ($user_id, (
        SELECT t.id FROM (SELECT MAX(sub_tile_id) + 1 as id FROM table WHERE user_id = $user_id) t
    )
)

This will find the highest sub_tile_id from the same table given user_id, increment it by 1.

4

NEVER try to reset the auto-increment value. That value is intended to be unique for each record and resetting it immediately breaks it from being unique.

If the value is supposed to have some meaning then set it yourself to the value it is supposed to have and get rid of the auto-increment completely.

5

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

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.

7

If you had a visual of what it looks like and what you like it to look like, I might understand your problem better. As is, I’m lost.

8

I’d have thought that the following would be a better definition for the table as the id field seems to be superfluous.

CREATE TABLE `tiles` (  
	`sub_tile` ENUM('1','2','3') NOT NULL,
	`user_id` INT(22),
            PRIMARY KEY (`user_id`, `sub_tile`)
 )
9

http://myownmealplanner.com/mealplans/add is the visual. Thanks.