Access the id of the object inserted after a 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 obtain 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 0 always despite there being an entry in the database

Solution The element was being inserted into the database. The problem was when I had created the table id wasn't an integer it was a varchar which represented an employee id. To fix this, i added the employee id as an additional column in the table and used the default id int auto_increment primary key and it worked.

Answer 1

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

get_result() is more for SELECT queries, rather than INSERTs.

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

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