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 7 of 7
  1. #1
    Regular Coder
    Join Date
    May 2002
    Location
    London, England
    Posts
    367
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Getting the last insert id of a record

    Getting the primary key value of the record that was last inserted by a user.

    Often, we need to insert a new record in a table with an autonumber variable as primary key, and get the value of the automatically generated primary key back. For instance, when we start a new session for a user, and we need the sessionID to insert as a foreign key into an order table or so.

    There are two function that return this value:

    mysql_insert_id()--> PHP function
    LAST_INSERT_ID()--> MySQL function that can be used by PHP and other Languages or in MySQL command line interface or MySQL front-end

    PHP example from php.net:

    PHP Code:
    <?php
       mysql_connect
    ("localhost""mysql_user""mysql_password") or
           die(
    "Could not connect: " mysql_error());
       
    mysql_select_db("mydb");

       
    mysql_query("INSERT INTO mytable (product) values ('kossu')");
       
    printf ("Last inserted record has id %d\n"mysql_insert_id());
    ?>
    MySQL example from mysql.com (in the second insert, the ID is inserted in another table):

    INSERT INTO foo (auto,text)VALUES(NULL,'text');
    INSERT INTO foo2 (id,text)VALUES(LAST_INSERT_ID(),'text');


    Links for more info and comments:

    http://www.mysql.com/doc/en/Getting_unique_ID.html
    http://www.php.net/mysql_insert_id

    Created by: raf
    Jeewhizz - MySQL Moderator
    http://www.sitehq.co.uk
    PHP and MySQL Hosting

  • #2
    Senior Coder Morgoth's Avatar
    Join Date
    Jun 2002
    Location
    Ontario, Canada Remaining Brain Cells: 6
    Posts
    1,402
    Thanks
    2
    Thanked 1 Time in 1 Post
    <edit>
    This post was asking a question about 'LAST_INSERT_ID()' in ASP, but I found out, that I'm an idiot. The code I posted did not work, but it wasn't the MySQL function's fault.
    I edited the code for ASP that is using the function so now it works.
    You can see what my stupid error was that I did not catch right away.
    </edit>
    Code:
    <%
    SQL = "INSERT INTO tblA (fld1, fld2) VALUES ('Text', " & Integer1 & ")"
    oConn.Execute(SQL)
    SQL = "INSERT INTO tblB (id) VALUES (LAST_INSERT_ID())"
    oConn.Execute(SQL)
    %>

  • #3
    New Coder
    Join Date
    Feb 2005
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exclamation hellllllllllllp

    hi
    i need some help
    please

    i have a coloumn in my mysql table

    it is called speciality

    it already has a value called "good"
    now i need to add more values to it without deleteing the present value

    for example:- first value = GOOD
    after new value inserted

    it will be GOOD,OK
    and so on!

    remember the old value shud not be delted!

    please help me
    i have 3000 records to be updates liek that

    urgent
    bye

  • #4
    New to the CF scene
    Join Date
    Feb 2005
    Location
    India
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Wrong place I guess...

    But this should be of help

    SQL="Update <table name> set speciality=speciality+',Ok' "
    conn.execute(SQL)

    Hope this helps

  • #5
    New Coder
    Join Date
    Apr 2005
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts
    So is there a way of knowing what id you are inserting when you are doing an insert operation ? e.g. I have a picture column and I want to name it with the id of the tuple..For the record with id 5, the column picture will have the value picture5 I mean..to be able to do this I should know the id I am going to insert, but I cannot guess how I can manage this..

    I need some help

  • #6
    New to the CF scene
    Join Date
    Aug 2005
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    mysql provides a "show table status" expression to learn various states of our tables. in this case we can learn our next insert id as:
    http://www.randomind.com/forum/viewtopic.php?t=6

  • #7
    New Coder bigtiger's Avatar
    Join Date
    Oct 2005
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    hi, odtufatih

    the code written by cybermantas is almost completed code that you can do what you want, but missing that last word that is important:

    Code:
         SQL="Update <table name> set speciality=speciality+',Ok' where id=" & your last id inserted. 
         ( see post above how to get last inserted id)
         conn.execute(SQL)
    - hope this help


  •  

    Posting Permissions

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