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

How to get last insert id with Phalcon ?

I tried using LAST_INSERT_ID() when getting the last id of the autoincrement primary key column but I get EOF exception :

 function add($tab) {

        $champs= "";
        $value = "";
        $separateur ="";

        $tab["commande_date"] = convertDateFormat5($tab["commande_date"]);

        foreach ($tab as $k => $v){
            if ($k == "salle_code" || $k == "table_code")
                continue;
            $champs .= $separateur . $k;
            $value .= $separateur . "'" . $v . "'";
            $separateur = ",";
        }
        $champs = '('.$champs.')';
        $value = '('.$value.')';
        $sSQL = "
                INSERT INTO Commande $champs
                VALUES $value
                ";
        $query = new Query($sSQL,$this->getDI());

        $ret = $query->execute();

        $sSQL = "SELECT LAST_INSERT_ID() as last_id";
        $queryId = new Query($sSQL,$this->getDI());

        return $queryId->execute();

    }

So how to get the last id with Phalcon ?

First of all, this code is too insecure, you may want to update it to use bound parameters: https://docs.phalcon.io/en/latest/reference/db.html#binding-parameters.

Following the kind of programming you're using, you can better use the db component instead of PHQL: https://docs.phalcon.io/en/latest/reference/db.html#inserting-updating-deleting-rows

Personally, I would recommend using the ORM and using the save() method.

For example:

<?php

//Create a new instance of the model
$newModel = new ModelNameHere();  

//Set The Data
$newModel->model_column_name_here = "Value1";
$newModel->model_column2_name_here = "Value2";

//And So Forth for all the columns you want to save data to.

//Save the Data to the database
  $newModel->save();
?>

Now when you want the id of the last id you just saved, simply call this:

<?php

//Sets last id to the variable $the_last_id
$the_last_id = $newModel->id;

?>

The ORM, will know what the id is of your last save. You'll have to rewrite your code, however, this allows you to jump databases (MySQL,PostgreSQL, etc) and not have to change your code.

Estoy usando phalcon 3.4.0 y no me funciona eso, que puede ser??

Personally, I would recommend using the ORM and using the save() method.

For example:

<?php

//Create a new instance of the model
$newModel = new ModelNameHere();  

//Set The Data
$newModel->model_column_name_here = "Value1";
$newModel->model_column2_name_here = "Value2";

//And So Forth for all the columns you want to save data to.

//Save the Data to the database
 $newModel->save();
?>

Now when you want the id of the last id you just saved, simply call this:

<?php

//Sets last id to the variable $the_last_id
$the_last_id = $newModel->id;

?>

The ORM, will know what the id is of your last save. You'll have to rewrite your code, however, this allows you to jump databases (MySQL,PostgreSQL, etc) and not have to change your code.