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

    Join Date
    Oct 2007
    Posts
    252
    Rep Power
    56

    Returning row after insert


    Ok I know after you use a SELECT query you can use mysqli_fetch_assoc($result); to retrieve the row. But what function would you use after an INSERT
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2005
    Posts
    22
    Rep Power
    0
    None, the INSERT does not return the row it just inserted. Using an INSERT on the mysql console e.g. gives you back either nothing which means the INSERT worked as intended or it gives you back an error. So if you want to have a look at the row you just INSERTed, you have to query with a new SELECT.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2007
    Posts
    252
    Rep Power
    56
    ok but what exactly would i querry for is there a way to SELECT the last row that was inserted?

    Basicly I have two tables jobs and auctions on the job table job_id is the auto incremented pkey and i wish to get that value after the insert into the jobs table and place it into my auction table so i can link the two later on.
    Last edited by odm4286; November 27th, 2008 at 05:27 PM.
  6. #4
  7. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    618
    Rep Power
    15
    you could try something along the lines of

    Code:
    SELECT job_id FROM jobtable ORDER BY job_id DESC LIMIT 1

    Comments on this post

    • misterdanny disagrees : (0) - This is a situation where you will want to use mysql_insert_id()
  8. #5
  9. Null Pointer Exception
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Mar 2006
    Location
    america
    Posts
    3,306
    Rep Power
    1582
    Use mysql_insert_id() after a successful insert query to get the the value of the auto_increment id that has been inserted.

    Comments on this post

    • odm4286 agrees
    • jasonlfunk agrees : Good to know. Thanks.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2005
    Posts
    22
    Rep Power
    0
    There's actually more than one way to do it, but both have caveats and can produce unwanted results.

    First, you can just retrieve the row with the highest autoincremented number so far by

    Code:
    SELECT * FROM jobtable ORDER BY job_id DESC LIMIT 1;
    using this can cause troubles if you don't handle it properly. It gives you back the row with highest auto increment value in the current db. But if you deleted some entries in between this number need not be the current number of the autoincrement counter: i.e. I had entries 1 through 12, so my current auto incrementer says 12, next number 13. Now I delete rows 9-12... my autoincrementer still is at 12/13 but the highest number I would get with the query from above would be 8.
    But this should not be a probleme as long as you insert first and then retrieve the highest number directly afterwards..

    And then there's MySQL's native LAST_INSERT_ID() which gives you the last autoincrement value for the current db connection, but only if you did not put in more than row. If you entered more than one row it gives you the autoincrement number of the first insert of the current session. For this, have a look at: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id
    Whatever you decide to use, test, test, test if it really gives you back correct results (i.e. what happens when the db connection fails between the different statements). And decide if you really want to use an auto incremented value as the key for a relation between the two tables.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2007
    Posts
    252
    Rep Power
    56
    thank you misterdanny that worked perfectly
  14. #8
  15. Null Pointer Exception
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Mar 2006
    Location
    america
    Posts
    3,306
    Rep Power
    1582
    Originally Posted by Glossolalia
    There's actually more than one way to do it, but both have caveats and can produce unwanted results.

    First, you can just retrieve the row with the highest autoincremented number so far by

    Code:
    SELECT * FROM jobtable ORDER BY job_id DESC LIMIT 1;
    using this can cause troubles if you don't handle it properly. It gives you back the row with highest auto increment value in the current db. But if you deleted some entries in between this number need not be the current number of the autoincrement counter: i.e. I had entries 1 through 12, so my current auto incrementer says 12, next number 13. Now I delete rows 9-12... my autoincrementer still is at 12/13 but the highest number I would get with the query from above would be 8.
    But this should not be a probleme as long as you insert first and then retrieve the highest number directly afterwards..

    And then there's MySQL's native LAST_INSERT_ID() which gives you the last autoincrement value for the current db connection, but only if you did not put in more than row. If you entered more than one row it gives you the autoincrement number of the first insert of the current session. For this, have a look at: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id
    Whatever you decide to use, test, test, test if it really gives you back correct results (i.e. what happens when the db connection fails between the different statements). And decide if you really want to use an auto incremented value as the key for a relation between the two tables.
    mysql_insert_id() is the only correct way to do this, if you do "SELECT * FROM jobtable ORDER BY job_id DESC LIMIT 1" you are not taking into account that many other people could be working with the table at the same time, and if they are it will lead to a mess.

    If your wrapping all your sql queries in if statements then you do not need to worry about anything failing.
    Last edited by misterdanny; November 28th, 2008 at 01:52 PM.

IMN logo majestic logo threadwatch logo seochat tools logo