insert_id in Kohana 3

Question

I'm using Kohana 3 framework with Mysql stored procedures. How can I get id of the last inserted record? Here's the code:

class Model_MyModel extends Kohana_Model
{
    public function insertNew($param1, $param2)
    {
        $result = $this->_db->query(Database::INSERT, 'CALL insertNew('.$param1.', '.$param2.', false)';
        return $result;
    }
    ...
    ...
}

Documentation says, the query() method returns an array with the last insert id and affected rows number, when executing an insert query. When I call: print_r($result) I'm getting: Array ( [0] => 0 [1] => 1 ) The insert_id key is 0, though I'm having many records in the db. What I'm doing wrong?

Answer 1

I think you'll have to use SQL's LAST_INSERT_ID() after inserting using a procedure:

SELECT LAST_INSERT_ID() as last_insert_id FROM table_name

( in your procedure just define this query in the end ).

The problem in this case is that Kohana automatically returns mysql_insert_id and mysql_affected_rows as result for Database::INSERT, so you'll need to call the procedure as a SELECT query and fetch it (Database::SELECT).