Getting the current insert ID

[Kryptix]

I'm trying to automatically insert a new topic on a FluxBB forum but I'm running into problems.

 

INSERT INTO `posts` (`poster`, `poster_id`, `message`, `posted`, `topic_id`) VALUES ('RSCEmulation', 19821, 'Here's my post', UNIX_TIMESTAMP(), (SELECT MAX(`id`) + 1 FROM `topics`));

 

INSERT INTO `topics` (`poster`, `subject`, `posted`, `last_post`, `last_post_id`, `last_poster`, `forum_id`) VALUES ('RSCEmulation', 'Here's my topic', UNIX_TIMESTAMP(), UNIX_TIMESTAMP(), (SELECT MAX(`id`) FROM `posts`), 'RSCEmulation', 37);

 

Both `posts` and `topics` use each others ID (which is a primary key and automatically incremented) to link together. When inserting the entry I need to get the corresponding ID.

 

Someone in IRC said to use the above but it's not working, it works OK but if I delete a record the expected ID is 2 but the above returns 0 (and continues increasing the more rows that have been deleted).

 

I'm also writing this in Java so it would help if I don't use any PHP functions.

 

Cheers guys.

[Kryptix]

I found this post but it returns an array which I don't know how I can use in the middle of a SQL query.

[Kryptix]

LAST_INSERT_ID(`id`) is the same as MAX(`id`). 

[Mchl]

You need three queries for that.

1. Insert a new topic - with no post information for time being

2. Insert a new post using LAST_INSERT_ID() to link it to topic

3. Update topic with post information.

 

And no: LAST_INSERT_ID() is not the same as MAX(id) (although it often gives same result).

See mysql_insert_id

 

[edit]

Just noticed you do this in Java

 

Anyway, Java probably has an eqivalent of PHP's mysql_insert_id() - what package do you use to connect to database?

[fenway]

LAST_INSERT_ID(`id`) is the same as MAX(`id`). 

That's not even close to being true in the general case.

[Kryptix]

I meant it gave the exact same result.