How to get last insert id with Phalcon?

Question

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 ?

have you tried this. I used that couple of years ago `lastInsertId()` — urfusion, Sep 23 '15 at 14:07
yes that was my previous analysis. :) now check for `lastInsertId()` is that working? — urfusion, Sep 23 '15 at 14:10

score 12 · Answer 1 · answered Sep 26 '15 at 02:34

The way you are handling this is fairly anti-MVC and you leave a lot of the Phalcon functionality on the table. You should have a database model named Commande which should be created something like:

$comande = new Commande();

then you can assign values like:

$comande->field1 = "hello";
$comande->adifferentfield = "world";
...

You'd then follow that up with:

$comande->save();  //yes I am aware we should test for success

Then you could access your key field, which would be populated automatically, something like:

print $comande->id;

You're missing out on a lot of the error handling and validation going about it the way you are.

sorry, I use phalcon 3.4, it returns 0 when I call $object->id (after $object->save()). How can I fix it, thanks — Phu Sanh, Jun 04 '20 at 05:44

score 5 · Answer 2 · edited Sep 26 '16 at 11:06

5

$yourModel = new YourModel();
$yourModel->save() // Or create();
$newId = $yourModel->getWriteConnection()->lastInsertId();

I think it's easier in this way. Hope it's helpful.

edited Sep 26 '16 at 11:06

Nhan

3,595
6
30
38

answered Sep 26 '16 at 09:48

X.Herry

65
1
3

urfusion · Accepted Answer · 2015-09-24T07:17:32.170

3

you can use the lastInsertId() function

$sSQL = "INSERT INTO Commande $champs VALUES $value ";
$query = new Query($sSQL,$this->getDI());
$ret = $query->execute();
$lastId = $query->lastInsertId();

Update

Here is an example of inserting and getting back the lastinsert id.

$success = $connection->insert(
     "robots",
     array("Astro Boy", 1952),
     array("name", "year")
 );

 //Getting the generated id
 $id = $connection->lastInsertId();

I think this might help you.

edited Sep 24 '15 at 07:17

answered Sep 23 '15 at 13:56

urfusion

5,528
5
50
87

no ! `Fatal error: Call to undefined method Phalcon\Mvc\Model\Query::lastInsertId()` : even with $query->lastInsertId() or $ret->lastInsertId() – pheromix Sep 23 '15 at 14:16
@pheromix : I am checking the PDO class http://phalcon.agent-j.ru/en/1.2.6/Phalcon/Db/Adapter/Pdo. and there is a method http://phalcon.agent-j.ru/en/1.2.6/Phalcon/Db/Adapter/Pdo#lastInsertId-details. You might be missing something. – urfusion Sep 23 '15 at 14:22
here is the latest https://docs.phalconphp.com/en/latest/api/Phalcon_Db_Adapter_Pdo_Mysql.html which also have lastinsertid function – urfusion Sep 24 '15 at 06:46
thank you for the link :) now it works after following their example. – pheromix Sep 24 '15 at 07:13
Glad to know that. :) – urfusion Sep 24 '15 at 07:14
but you should update your answer accordingly to the doc :) – pheromix Sep 24 '15 at 07:14

score 0 · Answer 4 · answered Aug 16 '16 at 00:09

In controller when you save:

   if ($client->save() == false) {
        foreach ($client->getMessages() as $message) {
            $this->flash->error($message);
        }
        return $this->forward('clients/new');
    }

    // Get new client ID and redirect to new client
    $this->response->redirect("client/page/$client->id");
    $this->view->disable();

How to get last insert id with Phalcon?

4 Answers4