How do I get the ID of an inserted row?

Question

I have this following example query, which works - I CAN insert values into my MySQL table, which also includes an unique id column. I want to get the id from the inserted row, after I execute the query. However what I get is 0 every time ($gotId=0).

What am I doing wrong?

 $stmt = $conn->prepare("INSERT INTO ....... ");                                
 $stmt-> bind_param("ss", ....);
 $stmt->execute();      
 $gotId = $conn->insert_id;

Full query:

$conn = $db->connect(); 
$stmt = $conn->prepare("INSERT INTO table(value1, value2) VALUES(?, ?)");                               
$stmt-> bind_param("ss", $value1, $value2);
$stmt->execute();       
$gotId = $conn->insert_id;

What DB interface are you using `mysqli` or `PDO`? looks like mysqli but its better that we dont assume :-) — prodigitalson, Jan 02 '15 at 18:28
Is your sql query is correct? Please post full sql query. Its because if your sql query is wrong then it will not insert in db and return id will be 0 — Chittaranjan Sethi, Jan 02 '15 at 18:31
strings, the point is - that the inserting successes - there is a row created - also with a new unique id, but I can't get the created id :D — Steward, Jan 02 '15 at 18:34
the insert ID will be in the $stmt object, not the db $conn. — Marc B, Jan 02 '15 at 19:22

score 3 · Accepted Answer · edited May 23 '17 at 12:06

3

After calling the execute() method on the PreparedStatement, the id of the insert row will be in the insert_id attribute Only read it.

$stmt->execute(); 
$gotId = $stmt->insert_id;

Taken from here

edited May 23 '17 at 12:06

Community

1
1

answered Jan 02 '15 at 18:36

Indra Kumar S

2,818
2
16
27

score 0 · Answer 2 · answered Jan 02 '15 at 18:40

0

 $query = "INSERT INTO .......";
 $mysqli->query($query);
 printf ("New Record has id %d.\n", $mysqli->insert_id);

More Info

answered Jan 02 '15 at 18:40

Priyank

3,778
3
29
48

Can you explain Where does $sql come from ? You are mixing Object oriented style and Procedural style – Indra Kumar S Jan 02 '15 at 18:43

How do I get the ID of an inserted row?

2 Answers2