SitePoint Sponsor

User Tag List

Results 1 to 12 of 12

Thread: mysql_insert_id

  1. #1
    SitePoint Evangelist
    Join Date
    Feb 2000
    Location
    England
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I use this to get the id number of a row just inserted, it is very useful.

    But how do i do it with.. guess what... phplib's db_mysql? I can't find a function in there anywhere for it...

    thanks.

  2. #2
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah... you are moving along quite well, so on to the next phplib lesson. Its called nextid, phplib runs on the notion that your tables don't use auto-incrementing id columns. There is a function in the db_mysql class that is called nextid, so what you do is you create a table in your db called db_sequence


    //Table structure for table 'db_sequence'
    CREATE TABLE db_sequence (
    seq_name varchar(100) DEFAULT '' NOT NULL,
    nextid varchar(10),
    PRIMARY KEY (seq_name)
    );
    INSERT INTO db_sequence VALUES ('users','0');
    INSERT INTO db_sequence VALUES ('templates','0');


    I have inserted some dummy data in there for example, notice how there is a sequence name and then a number which should be 0 to start with. This whole table will allow you to keep track of multiple id numbers for multiple tables, so basically you can use one method to get the next id for a table and then insert that id number into the reocrd, follow me so far.

    Now for an example say we have a table users and its time to add a record to the table. So we need to get the next id in the sequence from the db_sequence table for our users sequence so we use.

    PHP Code:
    //Remember our sequence name for the users sequence in the
    //db_sequence table was users so we will use this in our nextid
    //call so we can make sure we increment the correct sequence.
    $nextid $db->nextid(users); 

    Now $nextid holds the next number in the sequence.

    So that is basically how to use the db_sequence table using phplib.


    I hope that helps out.
    Last edited by freddydoesphp; Apr 26, 2001 at 09:32.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  3. #3
    SitePoint Evangelist
    Join Date
    Feb 2000
    Location
    England
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thank you, yes i understand that although have to say it seems a bit strange. You get the id number and then insert into table with that id? So you have to do 2 sql queries. I know if you want to get the id back you would have to do 2 sql queries but i will not always get the id back.

    Also, how does the db_sequence table get updated? do i have to update that column every time i do an insert as well or is it automatic somehow?

    ek.. seems a bit complicated ....

  4. #4
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No the mysql class takes care of incrementing the id number after you call $db->nextid(seqname);

    You don't have to worry about it. Also no need for two queries:

    PHP Code:
    $nextid $db->nextid(sequencename);
    $db->query("INSERT INTO TABLENAME set id = $nextid, name = '$name', email = '$email'"); 
    Thats it and you already have the id in a variable $nextid, so you don't need to do two queries.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  5. #5
    SitePoint Evangelist
    Join Date
    Feb 2000
    Location
    England
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    um ok... but that does mean i have to do the $nextid line every time i do an insert even if i don't need to know the nextid? As in i should turn of auto-increment and have to do add that nextid thing every time.

    Out of interest, why do they do it this way? I can't see the advantage ...

  6. #6
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The reasoning is this, let's say you have a table with users info in it, and each one has a unique id, now let's say you use that unique id in another table, like articles. So a little down the road, one user has an id of 15 and has 4 articles in the articles table with a key of 15, Now let's say the user is tyhe newesr user and he gets deleted and then another user gets added, using regular old auto-increment, would give the new user 15 again. Now the new user is still associated with the old user in the articles table. With this method, you will always get an id assigned that hasn't been assigned before, therefore maintaining the integrity of your table relations.

    No you don't have to use everytime unless the new record getting inserted needs an id number, I mean how difficult can it be to add $db->nextid(sequencename); right before your insert statements.

    Let's not get the function of this confused, it is to provide you with the next true id number for your table. If you are running an update statement, you wouldn't need it, right? Only when adding a new record to the table.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  7. #7
    SitePoint Evangelist
    Join Date
    Feb 2000
    Location
    England
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeah, that is true only for new rows so not that often. I see now, i never realised that auto-update filled in blanks that had been left, interesting.

    Thanks for all the info on this, there is so much to phplib - you could write an article on it for sitepoint

  8. #8
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually it doesn't fill in blanks but if the last record is deleted it will fill that one, at least that is what I thought, I am gonna go check it out right now... It could be entirely possible that my argument is completely false, although I am 99% sure that is what they meant for you do, with the function nextid()
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  9. #9
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay so I am completely wrong, mysql does not fill in the blanks with the ids that have already been used. And this is the reason that the guys who wrote phplib give for nextid()

    nextid($sequence_name)
    This function will return a sequence number from the sequence named by $sequence_name. This number is guaranteed to be obtained in an atomic manner and can be used as a primary key.
    from: http://phplib.netuse.de/documentatio...n-3.html#ss3.1
    So heres what I am gonna do for ya, leave the auto-increment thing and put this function into the mysql_db class, you can just stick at the bottom right before the final closing }

    PHP Code:
      function last_id() {
          return 
    mysql_insert_id($this->Link_ID);
          } 



    Usage:

    PHP Code:
    include("db.php");
    $db = new DB;
    $db->query("INSERT into events set name = 'Beavis2'");
    $db->query("SELECT id, name from events");
    while(
    $db->next_record()) {
        print 
    $db->f(id) ." "$db->f(name) ."<br>";
        }
    print 
    $db->last_id(); 
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  10. #10
    SitePoint Evangelist
    Join Date
    Feb 2000
    Location
    England
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hehe... at least you are learning 1 thing for every 20 things you teach me

    thanks, got to say i prefere this way.. I am not sure if you put this bit

    PHP Code:
    $db->query("SELECT id, name from events");
    while(
    $db->next_record()) {
        print 
    $db->f(id) ." "$db->f(name) ."<br>";
        } 
    in just to get data back to then use but to just get the id back (and we obviously know the other data as it has just been inserted) it was not needed.

    Thanks again, with this i can send of my "you need to authenticate your registration" email and that is my registration script finished... phew, at least 5% done now!!!

  11. #11
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You are correct sir! I had the extra query in there for debugging purposes only. I think I will figure out a few more extras to put in themysql_db class, I'll let you know what I come up with.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  12. #12
    SitePoint Evangelist
    Join Date
    Feb 2000
    Location
    England
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    looking forward to it


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
  •