Using last insert id in query

[Kieran Menor]

Here is my table:

 

TABLE `categories` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `name` varchar(255) NOT NULL,

  `order` int(10) unsigned NOT NULL,

  PRIMARY KEY (`id`)

)

 

Now, consider this query:

 

INSERT INTO `categories` (`name`, `order`) VALUES ('Test', ????)

 

I want the "order" field to have the same value as the ID field will get when the row is inserted. Is this possible?

 

Edit: And if so, how.

[Maq]

I think you need, mysql_insert_id.

[Kieran Menor]

Well, I would rather do it at insert time, but if that's not possible I suppose I can just update the row afterwards.

[Maq]

Well, I would rather do it at insert time, but if that's not possible I suppose I can just update the row afterwards.

 

I see.  Sorry, I'm not aware of a way to do this in a single query.

[angelcool]

Use LAST_INSERT_ID()

 

http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html

 

I will check the offset, what I mean is that you might be getting the id of the row inserted before the query.

 

Cheers!

[Maq]

That would still require two queries...

[angelcool]

This will work:

 

LAST_INSERT_ID()+1

 

Too much for me tho!! 

 

 

[angelcool]

This is my understanding:

 

LAST_INSERT_ID() & mysql_insert_id()

 

This 2 guys work on a connection basis.

 

If a host connects to MySQL and inserts a record, you can get the query ID using LAST_INSERT_ID() & mysql_insert_id() after executing the query.

 

If another hosts connects an execute LAST_INSERT_ID() & mysql_insert_id() will get 0, this is because no record has been inserted with this connection.

 

The only solution I can thing of to do what the OP wants, is to run a separate query or perhaps create a transaction.

 

Using two queries:

INSERT INTO `categories` (`name`) VALUES ('Test');

UPDATE `categories` SET `order`=LAST_INSERT_ID() WHERE Id=LAST_INSERT_ID();

 

Angel

 

 

 

[Maq]

The only solution I can thing of to do what the OP wants, is to run a separate query or perhaps create a transaction.

 

That's no what the OP wants.  Please read the previous posts.

[angelcool]

The OP wants to do this:

 

INSERT INTO `categories` (`name`) VALUES ('Test');

UPDATE `categories` SET `order`=LAST_INSERT_ID() WHERE Id=LAST_INSERT_ID();

 

with a single query.

 

The only solution I can think of to achieve the final result that OP will get, is to run a separate query or perhaps create a transaction.  :'(