Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    hgs
    hgs is offline
    New Coder
    Join Date
    Jan 2010
    Location
    Germany
    Posts
    83
    Thanks
    3
    Thanked 5 Times in 5 Posts

    Retrieve ID of newly inserted record

    Hi

    Here we talk about tables with an id field of type auto increment.

    Inserting a record into such a table assigns a new value to the id field, but how do you know about that value ?

    Here is just another method to get that value.

    The usage is pretty simple:

    $newid=insert("mytable","myid","somefiled");


    PHP Code:
    <?php
    function insert($table,$idfield,$uuidfield)
    /****************************************/
    {    
    //////////////////////////////////////////////////////////////////////
    //
    // This function is for use with mysql.
    // It is assumed that the table has a column if type 'auto increment'
    // and a character field at least 36 charaters long
    //
    //////////////////////////////////////////////////////////////////////

        
    $query="select max($idfield) from $table";
        
    $result mysql_query($query);
        if (!
    $result) {    
            die(
    "$query<br>" mysql_error());        
            exit;
        }
        
    $row mysql_fetch_row($result);    
        
    $idmax=$row[0]; // used  later to speed up location of new record            
    //    
    //    create a UUID     
    //          
        
    $myid=sprintf'%04x%04x-%04x-%04x-%04x-%04x%04x%04x',
               
    mt_rand00xffff ), mt_rand00xffff ), mt_rand00xffff ),
               
    mt_rand00x0fff ) | 0x4000,
               
    mt_rand00x3fff ) | 0x8000,
               
    mt_rand00xffff ), mt_rand00xffff ), mt_rand00xffff ) );
    //
    // insert new record with this UUID
    //
        
    $query="INSERT INTO `$table` ( `$idfield` , `$uuidfield` ) VALUES (NULL , '$myid');";        
        
    $result mysql_query($query);
        if (!
    $result) {    
            die(
    "$query<br>'"mysql_error());        
            exit;
        }
    //
    // locate new record now using the UUID and the last auto increment value
    //
        
    $newid=$myid;    
        
    $query="Select $idfield from $table where $uuidfield='$myid' and $idfield>='$idmax'";
        
    $result mysql_query($query);
        if (!
    $result) {    
            die(
    "$query<br>'"mysql_error());        
            exit;
        }
        
    $num_rows mysql_num_rows($result);
        if(
    $num_rows 0) {
    //
    //        clear the  field that temporarily holds the UUID
    //
            
    $row mysql_fetch_row($result);    
            
    $newid=$row[0];        
            
    $query="Update $table set $uuidfield='' where $idfield='$newid'";
            
    $result mysql_query($query);
            if (!
    $result) {    
                die(
    "$query<br>'"mysql_error());        
                exit;
            }
        } else {    
    //        
    //        TROUBLE !!!
    //            
            
    die('Could not find inserted record');
            exit;        
        }            
        return 
    $newid;    
    }
    ?>
    Have fun !

    Regards
    Heinz

  • #2
    Senior Coder kbluhm's Avatar
    Join Date
    Apr 2007
    Location
    Philadelphia, PA, USA
    Posts
    1,509
    Thanks
    3
    Thanked 258 Times in 254 Posts
    Why not just use mysql_insert_id(), $mysqli->insert_id(), etc? Or even query MySQL's LAST_INSERT_ID() directly.
    Last edited by kbluhm; 01-20-2010 at 11:57 AM.

  • #3
    hgs
    hgs is offline
    New Coder
    Join Date
    Jan 2010
    Location
    Germany
    Posts
    83
    Thanks
    3
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by kbluhm View Post
    Why not just use mysql_insert_id(), $mysqli->insert_id(), etc? Or even query MySQL's LAST_INSERT_ID() directly.
    Good question

    Call me paranoid .....

    I am not using the functions you mention because I don't know what happens to
    the 'insert_'id' in case multiple users do inserts on the same table at the same time.
    These inserts, as well as the calls above, might be protected by transactions , but who knows.

    The method I use worked for me the last years.

    Regards
    Heinz

  • #4
    Senior Coder kbluhm's Avatar
    Join Date
    Apr 2007
    Location
    Philadelphia, PA, USA
    Posts
    1,509
    Thanks
    3
    Thanked 258 Times in 254 Posts
    I'm sorry, but I see you making 4 queries, along with an expensive sprintf() call, to get the latest ID value... where mysql_insert_id() will only use a single query to get it. Isn't your solution more susceptible to returning an incorrect value? It is at the very least a heavier burden on the server... and if you're worried about ID's getting crossed due to new INSERTs, you're already under a heavy load as MySQL is really damn fast. The last thing I would think you'd want to do is be sending more queries than necessary.
    Last edited by kbluhm; 01-20-2010 at 12:16 PM.

  • #5
    hgs
    hgs is offline
    New Coder
    Join Date
    Jan 2010
    Location
    Germany
    Posts
    83
    Thanks
    3
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by kbluhm View Post
    I'm sorry, but I see you making 4 queries, along with an expensive sprintf()
    call, to get the latest ID value... where mysql_insert_id() will only use a single query to get it.
    Isn't your solution more susceptible to returning an incorrect value?
    It is at the very least a heavier burden on the server... and if you're
    worried about ID's getting crossed due to new INSERTs,
    you're already under a heavy load as MySQL is really damn fast.
    The last thing I would think you'd want to do is be sending more queries than necessary.
    Without a doubt 100% full ACK!

    However, in the past there where reasons for me to do it that way
    and it never failed.

  • #6
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by hgs View Post
    Good question

    Call me paranoid .....

    I am not using the functions you mention because I don't know what happens to
    the 'insert_'id' in case multiple users do inserts on the same table at the same time.
    These inserts, as well as the calls above, might be protected by transactions , but who knows.

    The method I use worked for me the last years.

    Regards
    Heinz
    http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html

    best regards

  • #7
    hgs
    hgs is offline
    New Coder
    Join Date
    Jan 2010
    Location
    Germany
    Posts
    83
    Thanks
    3
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by oesxyl View Post
    Ok ok I surrender

    I will give it a try.

    Regards

  • #8
    hgs
    hgs is offline
    New Coder
    Join Date
    Jan 2010
    Location
    Germany
    Posts
    83
    Thanks
    3
    Thanked 5 Times in 5 Posts
    According to the common wisdom

    Here is what I am using and testing now and it works



    PHP Code:
    function insert($table)
    /****************/
    {    
         
    //////////////////////////////////////////////////////////////////////
    //
    // This function is for use with mysql.
    //
    //////////////////////////////////////////////////////////////////////

        
    $query="Lock tables $table WRITE";
        
    $result mysql_query($query);
        if (!
    $result) {    
            die(
    "$query<br>" mysql_error());        
            exit;
        }    
        
    $query="INSERT INTO `$table` () VALUES ()";        
        
    $result mysql_query($query);
        if (!
    $result) {    
            die(
    "$query<br>'"mysql_error());        
            exit;
        }
        
    $idmysql_insert_id();
        
    $result mysql_query("unlock tables");
        if (!
    $result) {    
            die(
    "$query<br>" mysql_error());        
            exit;
        }    
        
        return 
    $id;    
            

    Last edited by hgs; 01-21-2010 at 08:30 AM. Reason: premature submit

  • #9
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,854
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    I'd recommend to check the article at http://www.faqts.com/knowledge_base/...d/1405/fid/272
    One notable exception is getting the value of the id you just inserted
    in an auto_increment field. You might think that, between the time you
    insert the row into the table and the time you ask for the last
    auto_increment ID, some other thread might have also inserted a row, and
    thus you would get their ID back instead of the one you inserted. But
    this is not the case - MySQL was designed to make this operation easy to
    work with. So the last_insert_id() is stored *per connection*, and you
    do not need to worry about another thread affecting that value.
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)


  •  

    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
    •