MySQL Help

[txrandom]

I have a database of entries with unique ids that are automatically created/incremented. So when I insert an entry in the database, I insert all the information besides the Id.

Is there anyway to get this Id when you insert information?

The only way I can think of doing it right now is:

INSERT Data1 Data2 Data3

SELECT id WHERE data1='$data1', data2='$data2'...


Is there anyway I can insert data and have the ID returned in the process?

I'm probably overlooking something very easy...

 

[clamum]

You can use the LAST_INSERT_ID() function. Something like this: "SELECT LAST_INSERT_ID() FROM tablename".

If you happen to be using PHP with MySQL, you can use the PHP function mysql_insert_id(). That would go like this: $id_num = mysql_insert_id($connection_link, optional).

Reference: MySQL function, PHP function

 

[txrandom]

Thanks, exactly what I was looking for.

 

[Evadman]

It is @@IDENTITY in MS SQL if anyone cares when searching in the future.

 

[txrandom]

I'm actually using MS SQL at work and MySQL for my personal website, so thanks as well.

 

[WannaFly]

Originally posted by: Evadman
It is @@IDENTITY in MS SQL if anyone cares when searching in the future.

I'd highly recommend using SCOPE_IDENTITY() if you are getting into any kind of complex operations - especially when using triggers. @@IDENTITY returns the last identity value inserted on your connection, where as SCOPE_IDENTITY() returns the identity only within scope (that Stored Proc, Trigger, etc).

Given the difference in the two, I'm not sure @@IDENTITY should ever be used.