[SOLVED] How to Identify last record INSERTED into mySQL..

[A JM]

I have an auto incrementing (ID) field in my database for invoice numbers.

 

Since the 'ID' field doesn't increment until the record is submitted how do I make sure that I have the correct 'ID' for the record I just submitted?

 

If more than one user is submitting an entry this could cause a problem with getting the wrong 'ID' if something sneaks in before I can query the table for the 'ID'

 

How do you guys handle this situation?

 

A JM,

[ohdang888]

queries move so fast that its highly unlikely, nearly impossible, for a regular site to experience that. it should be fine.

[Philip]

mysql_insert_id()

 

And unless you're getting a lot of calls per second, I wouldn't worry too much about it.

[A JM]

So what should the process be?

 

Run the INSERT query and then run a query to get the last invoice submitted (SELECT ID FROM claimcue ORDER BY ID DESC LIMIT 1) and then pass that to the Invoice page?

 

A JM,

 

 

[Philip]

See the [tweaked] example on the page I linked to:

mysql_query("INSERT INTO mytable (product) values ('kossu')");
echo "Last inserted record has id ", mysql_insert_id();

[PFMaBiSmAd]

Read the link KingPhilip posted above. That function returns the last insert id for the insert query that was just executed by your script. The value is kept per mysql client connection and is 100% accurate no matter how many insert queries are executed.

[A JM]

mysql_insert_id()

 

And unless you're getting a lot of calls per second, I wouldn't worry too much about it.

 

Sorry I missed the link...

 

I must be thinking about this in correctly then.

 

From my form submitting the information

//set session variable for last invoice generated in the system

session_register ("last_invoice");

$last_invoice = mysql_insert_id();

 

then on my Invoice:

 

<?php echo $_SESSION['last_invoice']; ?>

 

It generated an invoice number butit was incorrect, maybe because of the session itself, not sure here..

 

A JM,

[Philip]

Make sure to have session_start(); at the top of both scripts.

 

Then, setting the session:

$_SESSION['last_invoice'] = mysql_insert_id();

[A JM]

Make sure to have session_start(); at the top of both scripts.

 

Then, setting the session:

$_SESSION['last_invoice'] = mysql_insert_id();

 

Affirmative on the session_start()..

 

I get '0' now as opposed to '50' which I had previously.. does it matter where $_SESSION['last_invoice'] = mysql_insert_id(); is located in the script?

 

 

[Philip]

Place it right after your query.

[A JM]

That worked but creates a slight problem... I need the 'ID' used in the query for building a file name... any thoughts on how to accomplish that?

 

Will the mysql_insert_id() function work from a select query as well?

 

A JM,

[A JM]

Finally figured out a work around by calling a SELECT query just before the INSERT query and incrementing the SELECT +1...

 

Thanks again for all the help and guidance.

 

A JM,