Retrieving last id entered.

[RobertHolzhauser]

I'm wanting to return the id of the row that I just inserted. Right now I'm using the $this->app->db()->run($sql_insert, $data); method for the insert because I'm inserting a subset of the columns in the table. I then get the id with this:

$goal_id_query = $this->app->db()->run('SELECT MAX(id) as goal_id FROM goals');   # most likely this will be accurate
$goal_id = $goal_id_query->fetchAll();
$g_id = $goal_id[0]['goal_id']; 

That works ok, but I have a concern that there is a small chance the id that I return could, albeit very rarely, belong to another goal that was inserted from another session in that gap.

It's probably not much of a concern for our projects for class, but in the real world we'd want to guarantee accuracy.

I thought of these possible solutions: 1) Use a stored procedure in MySQL that would both do the insert and return the max id. Having them in the same proc call would tighten that gap even further, but there would still be that little tiny chance that every so often. 2) Use some of the column values that I just inserted in the where clause to virtually guarantee. But it still would not be perfect, as there is an infinitely small chance that another insert could be done in the gap between insert and select max(id). 3) Combine 1 & 2 into a proc. Which is even better yet, but still that tiny chance remains ....

So, I'm not going to solve for this perfectly for the final project, but out of curiousity does Laravel have a more perfect solution for this scenario??

[bar181]

In the real World I use insertGetId with Laravel (using query builder for complex queries) for inserts to get the id of the insert. Eloquent has more built-in ways to do it. Query builder is similar to the run method for this course.

For this project, you can use the existing framework's $this->app->db()->insert(). It returns the id of the new row that was created. So you will get the $g_id when you insert to goals.

I used to create a unique variable value then save it to a row (e.g. timestamp) and run a query on the timestamp to get the id ... but the build in Laravel framework methods are vastly superior. Now I feel satisfaction deleting 100s of lines of old code and replacing with one line - waiting until the continuation course next term to learn how to delete 100s of more lines.

[richiecarey]

I may be misunderstanding the use case but wondering if the e2 framework insert() method may do what you need ...

https://hesweb.dev/e2/notes/php/e2framework/database#insert

---

Insert a new row into a table. Returns the id of the new row that was created.

$app->db()->insert(string $table, array $data)

---

.. I did something like ...

$this->game_id = $this->app->db()->insert('games', [ ... etc.

[RobertHolzhauser]

Thanks to both of you! :) I had totally missed that the db()->insert returns the id of the row inserted.

Really Appreciate it!

[susanBuck]

Great input @bar181 & @richiecarey!

Having the insert method return the ID is a new addition (per thread #47) which may be why you missed it, @RobertHolzhauser.

Here's the underlying DB insert method if you want to see what's happening underneath the hood:

[RobertHolzhauser]

Very cool how you use the code snippet that follows in the E2 framework to get the id : return (int)$this->pdo->lastInsertId();

Thanks much!