www.webdeveloper.com
Results 1 to 15 of 15

Thread: Auto Increment Problem

  1. #1
    Join Date
    Dec 2012
    Posts
    32

    Auto Increment Problem

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

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

  2. #2
    Join Date
    Dec 2012
    Posts
    32
    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;

  3. #3
    Join Date
    Dec 2012
    Posts
    32
    After dropping the id field, I tried both of these and got syntax errors
    Code:
    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;

  4. #4
    Join Date
    Dec 2012
    Posts
    32

    Alter Table Syntax What to use instead of where?

    After dropping the id field, I tried both of these and got syntax errors
    Code:
    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;

  5. #5
    Join Date
    Dec 2012
    Posts
    32
    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

  6. #6
    Join Date
    Dec 2012
    Posts
    32
    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

  7. #7
    Join Date
    Oct 2012
    Posts
    41
    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.

  8. #8
    Join Date
    Dec 2012
    Posts
    32
    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.

  9. #9
    Join Date
    Oct 2012
    Posts
    41
    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.

  10. #10
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,144
    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).
    PHP Code:
    $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.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  11. #11
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,144
    PS: You'd probably want to change the IFNULL line above to:
    Code:
    IFNULL((SELECT MAX(sub_tile_id) FROM tiles WHERE user_id=$userId), 0) + 1
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  12. #12
    Join Date
    Dec 2012
    Posts
    32
    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)
    )

  13. #13
    Join Date
    Dec 2012
    Posts
    32
    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.

  14. #14
    Join Date
    Oct 2010
    Location
    Ohio
    Posts
    233
    You could instead give the divs a class named draggalble and change those 3 jquery lines to:
    Code:
    $('.draggable').draggable();
    ~Ryan
    www.rdennispallas.com <-- Personal Site, changing regularly cuz its ugly.

  15. #15
    Join Date
    Dec 2012
    Posts
    32
    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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles