Reset Auto Increment to lower value

[makamo66]

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.

[Christian F.]

You don't. This is either a many-to-many relation, or you you want the users to have unique sub_title_ids. You don't want to have multiple subtitles to share the same ID, as that will only create a lot of headaches for you.

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

)

[Christian F.]

OK...

 

Don't see how that relates to a many-to-many relation though.

[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.