SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Aug 2000
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    auto_increment, last_insert_id, and searching

    :: transmission initiated

    how do people usually assign their IDs to primary keys in a table?

    how do i reset the last_insert_id variable?

    and let's say i have five rows in a table having auto_increment IDs ranging from 1 - 5. i deleted the row with ID = 4. now when i insert a new row, how do i get it to assign an ID = 4 instead of ID = 5? as in, how do i search through the column and determine and unused number so that i can use it? is there a built in function for that?

    cheers, joannou.

    :: transmission terminated

  2. #2
    Talk to the /dev/null Theiggsta's Avatar
    Join Date
    Mar 2001
    Location
    Tampa, FL
    Posts
    376
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    insert_id returns the information of the row it just inserted into the database...

    as far as inserting a specific ID increments...you would have to whip up code that keeps the increments ID and just re-inserts the ID number in the database.

    say I just removed a row with the ID of 2 all I have to do is this

    INSERT INTO test(ID) VALUES('2')

    Because the auto_incrementer will take values other than NULL and not increment the counter, it only increments with its given NULL.

    so when you delete a record, grab its ID to a variable before hand and after deleting it...just re-insert the ID variable when you do the INSERT command.

    Hope that helps.
    Aaron "Theiggsta" Kalin
    Pixel Martini
    Ruby and Rails Developer

  3. #3
    SitePoint Member
    Join Date
    Aug 2000
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    :: transmission initiated

    now i get it. say i have a table with 5 rows (ID 1 - 5). i deleted row 2. then i deleted row 4. i can put all the freed id numbers in another table. and when i wanna add a new row, it will look at the other table and see if any ids are available and use it. otherwise, it will pass a null and let auto_increment do it's thing.

    so, you mean there is no way to change the value that is grabbed by last_insert_id()? where does last_insert_id() grab the information from? maybe if i know where it grabs the information from, i can change or reset it.

    thanks a lot, theiggsta!

    cheers, joannou.

    :: transmission terminated

  4. #4
    SitePoint Guru
    Join Date
    Jan 2001
    Location
    Alkmaar, Netherlands
    Posts
    710
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It is not a good idea what you wanna do, just use primary keys created automatically. I recommend you to search forums about the question you asked, it has been discussed couple of times.


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
  •