Access the identifier of the object inserted after the prepared statement in PHP using MYSQLi

Question

I need the id of the last inserted object. I use prepared statements to avoid sql injection. But I'm not sure how to get the id.

$sql = "INSERT IGNORE INTO faculty (id, term, role, prefix, first_name, middle_name, last_name, suffix) VALUES (?,?,?,?,?,?,?,?)"; if (!($stmt = $mysqli->prepare($sql))) echo "Faculty Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error; $stmt->bind_param('sissssss', $faculty['id'], $faculty['term'], $faculty['role'], $faculty->name->prefix, $faculty->name->first, $faculty->name->middle, $faculty->name->last, $faculty->name->suffix ); if (!$stmt->execute()) echo "Faculty Execute failed: (" . $mysqli->errno . ") " . $mysqli->error; $result = $stmt->insert_id; echo "\n Result:" . $result; $stmt->close(); 

The result is always 0, despite the presence of a record in the database.

Solution The item was inserted into the database. The problem was that when I created the table identifier, it was not an integer, it was a varchar that represented the employee identifier. To fix this, I added the employee id as an extra column in the table and used the default id int int auto_increment primary key and it worked.

Answer 1

Try changing $result = $stmt->get_result(); on $result = $stmt->insert_id;

get_result() more for SELECT queries, not INSERT s.

http://php.net/manual/en/mysqli-stmt.get-result.php

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