#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    Norcross, GA
    Posts
    458
    Rep Power
    19

    Getting the last INSERTed record from Mysql


    I remember reading about a function that will get the last inserted mysql record, but I don't recall if this is a php or mysql function.

    What is the name of the function?

    Thanks.
  2. #2
  3. No Profile Picture
    Clueless llama
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Feb 2001
    Location
    Lincoln, NE. USA
    Posts
    2,353
    Rep Power
    122
    LAST_INSERT_ID()
    it is a mysql function

    http://www.mysql.com/doc/M/i/Miscell...functions.html
    about half way down.

    Comments on this post

    • phpfresh agrees
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2001
    Location
    Alkmaar
    Posts
    96
    Rep Power
    19
    Here comes php function for last inserted record's id

    PHP Code:
    int mysql_insert_id (resource [link_identifier]) 
    http://www.php.net/manual/en/functio...-insert-id.php
  6. #4
  7. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Caro, Michigan
    Posts
    14,971
    Rep Power
    4576
    Or read the FAQ posted at the top of this forum!!

    Question #13

    ---John Holmes...
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2001
    Location
    On a screen near you
    Posts
    498
    Rep Power
    18
    funny how somethings are?


    I needed to get the mysql_insert_id() but it always returned 0
    i think this is because im using an old version of MySQL


    So i had to do


    Code:
    
    SELECT * FROM table ORDER BY ID DESC LIMIT 1

    Mark
    100 trillion calculations per nanosecond
  10. #6
  11. /(bb|[^b]{2})/

    Join Date
    Nov 2001
    Location
    Somewhere in the great unknown
    Posts
    5,163
    Rep Power
    797
    why not do:
    Code:
    select max(id) from table
  12. #7
  13. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Caro, Michigan
    Posts
    14,971
    Rep Power
    4576
    If you are a newbie reading this, please DON'T do either of the two suggestions right before this. You are setting yourself up for failure. Don't do things "because they work", do the right thing.

    In PHP use the function mysql_insert_id() AFTER an INSERT query.

    In MySQL, you can use the LAST_INSERT_ID() in the queries after an INSERT.

    It works, trust me. It doesn't matter on your version of anything. If it's not working, you're doing something wrong, so please troubleshoot it until you get it to work.

    ---John Holmes...
    -- Cigars, whiskey and wild, wild women. --
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2001
    Location
    On a screen near you
    Posts
    498
    Rep Power
    18
    I get what your saying John but ive tried doing
    mysql_insert_id() and it returns 0


    Im sure ive tried both but to be on the safe side
    Should i do mysql_insert_id() before the insert or after


    Mark
    100 trillion calculations per nanosecond
  16. #9
  17. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jul 2001
    Location
    England
    Posts
    965
    Rep Power
    18
    Marky, after:

    @mysql_query("INSERT INTO table [cols] VALUES ([values])");

    $last_id = mysql_insert_id();

    echo $last_id;
  18. #10
  19. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Caro, Michigan
    Posts
    14,971
    Rep Power
    4576
    after...it works. If you can't get it to work, you're doing something wrong. The other methods work most of the time, but will cause trouble when you start getting any kind of actual traffic to your site.

    ---John Holmes...
  20. #11
  21. Web Developer
    Devshed Novice (500 - 999 posts)

    Join Date
    Oct 2001
    Location
    Finland
    Posts
    719
    Rep Power
    20
    According to the manual, the mysql_insert_id() must be done AFTER the actually query, 'cause MySQL will fetch the last auto_incremented value.

    Have you tried running mysql_query by using the LAST_INSERT_ID(), function?
    -- Tomi Kaistila
    -- Developer's Journal

    The more you learn, the more you know.
    The more you know, the more you forget.
    The more you forget, the less you know.
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    Norcross, GA
    Posts
    458
    Rep Power
    19

    Thumbs up


    The MySQL function will not do you wrong according to documentation.

    It will return only the last inserted ID from that person's connection - it will ignore other connections/inserts that may have happened at or right after your insert.

    Btw, I got it to work on the first attempt and have since went back and recoded a couple other scripts to use the function (don't ask what I was doing as a workaround ).
  24. #13
  25. /(bb|[^b]{2})/

    Join Date
    Nov 2001
    Location
    Somewhere in the great unknown
    Posts
    5,163
    Rep Power
    797
    The reason for my alternate way for getting the last inserted record is because I don't use MySQL so I must find other ways to track a unique id field. (Although, I don't have and auto-increment type for this database I still use the concept.) Plus, it is always good to know more than one way to do something.
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2001
    Location
    On a screen near you
    Posts
    498
    Rep Power
    18
    Hey guys i got mysql_insert_id(); working
    it is easier to do it like this


    My script kept returning 0 but it's ok now



    Mark
    100 trillion calculations per nanosecond

IMN logo majestic logo threadwatch logo seochat tools logo