November 27th, 2008, 05:10 PM
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
November 27th, 2008, 05:20 PM
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.
November 27th, 2008, 05:22 PM
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.
November 27th, 2008, 05:39 PM
you could try something along the lines of
SELECT job_id FROM jobtable ORDER BY job_id DESC LIMIT 1
Comments on this post
November 27th, 2008, 05:43 PM
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
November 27th, 2008, 05:55 PM
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
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.
SELECT * FROM jobtable ORDER BY job_id DESC LIMIT 1;
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.
November 27th, 2008, 05:58 PM
thank you misterdanny that worked perfectly
November 28th, 2008, 01:46 PM
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.
Originally Posted by Glossolalia
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.