We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Phalcon\Db insert that returns inserted id

Hi All,

I have a poblem with the Phalcon\Db insert method. I want to return the current inserted record's id. Is this possible?

public function createSession($clientId, $ownerType, $ownerId)
{
        $this->db->insert(
            $this->tables['oauth_sessions'],
            [$clientId, $ownerType, $ownerId],
            ['client_id', 'owner_type', 'owner_id']
        );                          
        return $this->db->lastInsertId();                        
}

// Here is the lastInsertID function    
function lastInsertId(){
        $sql = 'SELECT CAST(SCOPE_IDENTITY() AS int) AS ID';               
        $this->db->fetchOne($sql, Db::FETCH_ASSOC);        
        return $id;
}

I use a custom mssql adapter to connect my SQLSRV database and it works fine but I cannot call a method ( SELECT SCOPE_IDENTITY() ) to return the inserted ID. It just returns null.

Please help.

Thanks!



98.9k

I think you have to return the fetchOne into one variable and then return the value:

function lastInsertId(){
        $sql = 'SELECT CAST(SCOPE_IDENTITY() AS int) AS ID';               
        $id = $this->db->fetchOne($sql, Db::FETCH_NUM);        
        return $id[0];
}


22.8k
edited Aug '14

Hi @Phalcon, I still cant get this to work. So it looks like this is a PHP MSSQL Driver issue. I can get around this problem by doing something like this :

    $tsql = "INSERT INTO oauth_sessions(client_id,owner_type,owner_id) VALUES (?,?,?); SELECT SCOPE_IDENTITY() AS NewUserId";
    $params = array('aaa','user',3);
    $id = $this->db->query($tsql,$params);// Db::FETCH_ASSOC,         

But now I get this MSSQL Error : PDOException: SQLSTATE[IMSSP]: The active result for the query contains no fields.

Any ideas what I can do about this?

My other workaround is just to return the MAX id of the table then.

Thanks



22.8k

Oops, just read now that PHQL only supports one statement at a time and not composite statements ( to avoid SQL Injection ) Reference : PHQL only allows one SQL statement to be executed per call preventing injections https://docs.phalcon.io/en/latest/reference/phql.html

So something like this wont work :

 $query1 = "select * from Person.Address where City = 'Bothell';";
 $query2 = "select * from Person.ContactType;";

 $stmt = $conn->query( $query1 . $query2 );

 $rowset1 = $stmt->fetchAll();
 $stmt->nextRowset();
 $rowset2 = $stmt->fetchAll();
 var_dump( $rowset1 );
 var_dump( $rowset2 );

So for now i'm sticking to returning the MAX id of the table.