www.webdeveloper.com
Results 1 to 15 of 15

Thread: [RESOLVED] mysql smart auto_increment

  1. #1
    Join Date
    Sep 2008
    Posts
    61

    resolved [RESOLVED] mysql smart auto_increment

    Hi!

    I wonder if it is possible to make some kind of auto_increment in php that first changes all the id values of the rows so that they are increasing 1-x without jumping over any numbers.. I mean if there are 5 rows in a database and the id values are

    1
    2
    5
    6
    7

    because I've removed 3 and 4.. I'd want a php script to change those to

    1
    2
    3
    4
    5

    when I put in a new object, and then put in the id 6 for the new object. So it would result in

    1
    2
    3
    4
    5
    6

    instead of

    1
    2
    5
    6
    7
    12

    Which is a pretty irritating thing that the mysql auto_increment does if you remove some stuff and put some stuff in, etc..

    Could someone help me with the script for that?

    Cheers,
    Artheus

  2. #2
    is the auto increment field set as the primary key?

    If so, I'm not sure you can re-use primary keys.

    plus mySql stores the next auto increment in the table data structure. auto-increment=14 or something like that if memory serves me correct.

    so even if you change the values of the field to correctly fill in the blanks, mysql will add the next row at whatever the next auto-increment value of the table is.

    Does that make sense?

    perhaps if possible moving to postgreSQL would help solve the problem as it handles the auto increment primary key differently.

  3. #3
    Join Date
    Sep 2008
    Posts
    61
    Yes that makes sense.. And I know all that.. I am not after reusing a primary key.. And I am not after using the actual auto_increment inside of mysql.. What I am after is to make a php function which first changes the

    1
    2
    5
    6
    7

    id's to become

    1
    2
    3
    4
    5

    And then count the rows, which are 5, add one to it.. which becomes 6.. and then add a id manually using an INPUT Query.. and then it's

    1
    2
    3
    4
    5
    6

    Ok?

  4. #4
    ahh .. well in that case a simple for loop using count() would help you

    PHP Code:
    //
    $entries //array of data rows sorted by that id field;
    $counter 1;
    for(
    $i 0$i count($entries); $i++){
          
    $sql "UPDATE table SET id='" $counter "' WHERE id='" $entries[$i]['idField'] . "' LIMIT 1";
          
    $mysql_query($sql); 
         
    $counter++;


    Should get the job done.

  5. #5
    Join Date
    Sep 2008
    Posts
    61
    So if I just make up a way to write that.. so maby I can elucidate what I mean...

    *Not a real PHP script, just a way to try to elucidate what I mean*

    Code:
    $link = mysql_connect("localhost", "username", "password");
    mysql_select_db("database", $link) or die(mysql_error());
    
    $i = 1;
    
    $result = mysql_query("SELECT * FROM table") or die(mysql_error());
    while($row = mysql_fetch_array($result)) {
    
    $row['id'] = $i; //Here I want to update the value of the current row to the value of $i
    ++$i;
    
    }

  6. #6
    that will work, but it won't actually change the database values, just the value within the resulting array.

    you will also want to make sure you ORDER BY the correct field

    SELECT * FROM table ORDER BY id

    this will ensure that at the end row 1 is still row 1

  7. #7
    Join Date
    Sep 2008
    Posts
    61
    well.. I solved it xD

    Code:
    $i = 1;
    
    $result = mysql_query("SELECT * FROM table") or die(mysql_error());
    
    while($row = mysql_fetch_array($result)) {
    
    	$id = $row['id'];
    	
    	mysql_query("UPDATE table SET id=$i WHERE id=$id");
    	++$i;
    
    }
    Last edited by artheus; 07-22-2009 at 08:05 AM. Reason: removed some unnecessary code ^^

  8. #8
    Cool, good job.

    Hope I helped and didn't hinder

  9. #9
    Join Date
    Sep 2008
    Posts
    61
    Yeah ^^ I know that too.. :P Well thanks alot!

    And Thanks for the tip of ORDER BY!

    Cheers,
    Artheus

  10. #10
    Join Date
    Sep 2008
    Posts
    61
    You helped!
    Soo, Thanks!

  11. #11
    Join Date
    Nov 2008
    Posts
    2,477
    OUCH! You intend to call a query for every row?

    You could do this all in one query - update multiple rows with one query.

    I suppose it depends if this is a one time job or something to be done regularly though.

  12. #12
    Join Date
    Sep 2008
    Posts
    61
    well not really.. It's for a Image gallery, which will never hold more than abour a 30 pictures.. so.. that's not really a problem I will encounter..
    But a great tip! Which I will use, for a more effective code!

    Thanks!

  13. #13
    Join Date
    Sep 2008
    Posts
    61
    --
    Last edited by artheus; 01-26-2010 at 04:47 AM. Reason: wrote in the wrong post... sorry..

  14. #14
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,248
    Frankly, if you need or depend on the primary key to be sequentially numbered with no gaps, there is probably a flaw in your design logic. The only purpose of that field should be to provide a unique identifier. The moment you start renumbering it, you start breaking any other table that references that table (now or in future enhancements), or else you have to cascade any renumbering into all such referencing tables (and hope like heck that you don't screw it up).
    "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

  15. #15
    Join Date
    Sep 2008
    Posts
    61
    Thanks for that. But the thing is that it was just a simple little thing for a image gallery. And that was the easiest way of making it. Or something. I don't really remember. But I see what you mean. And I agree on that using the primary key otherwise than a unique identifier is not very smart. I don't use that method anymore.

    But thanks!

    Cheers,
    Artheus

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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