#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    6
    Rep Power
    0

    Reset Auto Increment to lower value


    I need to create a tiles table that has a structure like this:

    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?
  2. #2
  3. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,905
    Rep Power
    6351
    Make it by hand? You can't have two auto-increment IDs on the same table anyway.

    You also don't necessarily need to do this, just don't show them their sub IDs if they're not important.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    6
    Rep Power
    0

    Enum


    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. #4
  7. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,905
    Rep Power
    6351
    No kidding.

    You cannot do this. There is no such thing as "an auto-increment which auto-increments based on the unique value of another column." You have to write it yourself, or not do it this way.

    When you go to insert a new row in this table, select the max of the sub_tile column for that user ID, add one, and then do the insert.

    This isn't foolproof though because it's subject to race conditions.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  8. #5
  9. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    why is this so important to you, anyway? Obviously, there is no technical reason to do this, because the numbering schema is completely irrelevant to the database (as long as the numbers are unique).

    So what are you trying to do?
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    6
    Rep Power
    0
    Good question. The jquery at http://myownmealplanner.com/mealplans/add contains the following code (see the view source):

    Code:
    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.

IMN logo majestic logo threadwatch logo seochat tools logo