Get ID of an inserted row in sql

Question

I am creating a forum service ( https://www.orbitrondev.com/forum/ )

When someone creates a new thread it will execute:

// Example values
$UserID = 23123;
$ForumID = 1;
$ThreadName = 'Example title';

$sQuery = 'INSERT INTO threads (user_id, board_id, topic, time, lastPostUserId, lastPostTime)
VALUES ("' . $UserID . '", "' . $ForumID . '", "' . $ThreadName . '", "' . $time . '", "' . $UserID . '", "' . $time . '")';

The ID is in the column thread_id

Now I have to get the ID (thread_id) of the inserted row. So I can create a post, and to create a post I need the ID.

I thought about getting the last inserted thread id an adding 1 so I have the id, but SQL looks finer :P

How can I know the thread_id value for the newly inserted row?

What are using to interface with mysql (`mysql_`, `mysqli_`, PDO)? I think they all have a method to get the last inserted id. — Dan, Dec 23 '14 at 19:18

score 0 · Answer 1 · answered Dec 23 '14 at 19:22

0

You can retrieve the most recent AUTO_INCREMENT value with the LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function. These functions are connection-specific, so their return values are not affected by another connection which is also performing inserts

http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html

mysqli::$insert_id -- mysqli_insert_id — Returns the auto generated id used in the last query

http://php.net/manual/en/mysqli.insert-id.php

answered Dec 23 '14 at 19:22

Rajesh Chamarthi

18,568
4
40
67

You mean, I shuold use: "SELECT LAST_INSERT_ID() FROM threads"? – D3strukt0r Dec 23 '14 at 19:24
Noty realy, click the 2nd link for example. He also meant between the lines u should use prepared statements – DarkBee Dec 23 '14 at 19:25
Ouuuu I understand what you mean with the php version :D. Thanks a lot :) – D3strukt0r Dec 23 '14 at 19:27
I will try it and leave a comment again if it worked – D3strukt0r Dec 23 '14 at 19:28
i didn't worked, it return "(int) 0" istead of "(int) 4". 4 because it's the fourth thread I created – D3strukt0r Dec 23 '14 at 19:34
Post the code you tried so we can tell you what you did wrong. – Hobbes Dec 23 '14 at 19:41

score 0 · Accepted Answer · edited May 23 '17 at 12:29

0

You should use mysqli::$insert_id.

Where $mysqli is your connection;

$result = $mysqli->query($sQuery);
$lastid = $mysqli->insert_id;

Although you should use prepared statements when inserting data into the database.

Note: You need to have an auto incremented ID field in the database for this to work.

You have;

$oResult = $Database->query($sQuery);
$ThreadID = $oResult->insert_id;

which will not work.

You should use the connection to find the last inserted ID, like this;

$oResult = $Database->query($sQuery);
$ThreadID = $Database->insert_id;

Hope this helps.

edited May 23 '17 at 12:29

Community

1
1

answered Dec 23 '14 at 19:33

worldofjr

3,868
8
37
49

Then this should work (that's what will be returned)!! – worldofjr Dec 23 '14 at 19:43
@ManueleVaccari Updated my answer. You need to use the database connection, not the result set, to get the last insert ID. – worldofjr Dec 23 '14 at 19:50

Get ID of an inserted row in sql

2 Answers2