SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Member
    Join Date
    Jan 2010
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Reset Auto Increment to lower value

    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. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    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.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  3. #3
    SitePoint Wizard wonshikee's Avatar
    Join Date
    Jan 2007
    Posts
    1,223
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    You can run something like this:

    $user_id = 3;

    Code:
    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. #4
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,837
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    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.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  5. #5
    SitePoint Member
    Join Date
    Jan 2010
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This didn't work
    Code:
    CREATE TABLE `tiles` (  
    	`sub_tile` ENUM('1','2','3') NOT NULL,
    	`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,  
    	`user_id` INT(22)
     )

  6. #6
    SitePoint Member
    Join Date
    Jan 2010
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #7
    SitePoint Wizard wonshikee's Avatar
    Join Date
    Jan 2007
    Posts
    1,223
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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. #8
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,837
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by makamo66 View Post
    This didn't work
    Code:
    CREATE TABLE `tiles` (  
    	`sub_tile` ENUM('1','2','3') NOT NULL,
    	`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,  
    	`user_id` INT(22)
     )

    I'd have thought that the following would be a better definition for the table as the id field seems to be superfluous.
    Code:
    CREATE TABLE `tiles` (  
    	`sub_tile` ENUM('1','2','3') NOT NULL,
    	`user_id` INT(22),
                PRIMARY KEY (`user_id`, `sub_tile`)
     )
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  9. #9
    SitePoint Member
    Join Date
    Jan 2010
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by wonshikee View Post
    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.
    http://myownmealplanner.com/mealplans/add is the visual. Thanks.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •