DB::LastId();

I believe the DB::LastID(); would need to be ran directly after the last sql insert, another query maybe changing the output of this or is the ID not the primarykey?.

In "After Record Added" if you look in the $values, this should contain the id.

There are so many misconceptions here.

First of all, if you just need the date, how is that ID is going to help you? In AfterAdd event you have access to all values enetered into a form, simply save the date to a session variable and redirect to another Add page. As simple as that.

In regards to DB::LastId() - it will only work right after the record was added as Chris rightfully points out. More than that, it will only work when you add a record from your own code as the example in the manual explains:
https://xlinesoft.com/phprunner/docs/db_lastid.htm

Thanks, Chris, your suggestion helped somewhat.

Thanks, Sergey,

I had read the section on DB::LastId() before making my original post. The word "Date" was a typo; it should have been "Data." I have my own code in the "after record added" event, and it works, except the values weren't passing correctly from it to the before display. All my redirects work. I traced the problem back to the "after record added" event.

Where I'm struggling is trying to figure out why the session variable is showing a number that I can't find anywhere. The $value gives me the right field info, except the "ID" primary key doesn't show.

I could upload my code, but it's quite a lot. That's why I broke it down to a debug page in my first message to simplify my debugging. As I mentioned, the problem I'm trying to solve is why the session variable's number is so out of whack. By the way, the code above is in the "after record added" event, and it does pull $value correctly.

Mark, Are you passing the ID from the inputs?

I never display the ID in the add/edit page or have it ticked in the fields, I just use it as the key field, it's always in the $values in after record added. I then use this if I have an associated record which needs updating.

To try and find out where the $_SESSION variable is getting set, try using vscode to search for the variable name on the built project, while the events are grouped into 1 file it may help you find if it's set in multiple places rather than just the one as you're expecting.

I tend to find debugging within phprunner time consuming due to the rebuid project time. With large amounts of code I try to move them into a class or a function in the custom file folder in the style page, I can then edit those to make a quick change to test something/debug rather than rebuilding the project each time, remember to paste the code back into PHPRunner from the editor or you'll just overwrite any changes.

Chris,

I don't display the ID either; I'm just echoing it temporarily to track the variable's output value at various steps.

Here is where the session variable is set:

// Get the last inserted ID using DB::LastId()
$lastID = DB::LastId();

// Set the session variable
if ($lastID) {
$_SESSION['lastRoomPMEngID'] = $lastID;

This code is in the "After Record Added" event. When I echo other fields from $value, they display correctly, but not the "ID." As I mentioned before, the session variable holds a number that doesn't match the ID or row count. It's odd.

Great idea on using VSCode! I'll try that. I haven't coded for a month or two, and my skills are a bit rusty. I've found that if I start a comment with "/" and forget to close it with "/" in events, it can mess up the code. I need to be careful about that. Sometimes, when coding late at night, my mind isn't as sharp, and I miss simple things.

Thanks again for the suggestions; I'll give them a try.

Understood the typo, "date" vs "data". However the question remains, why do you need to use DB::LastID() at all?

As manual says, this function only makes sense when you add a record from your own code. In AfterAdd event you already have access to everything. This is what confuses me.

I thinh that LastInsertedID function works on a connection level. That is why admin and documentation is telling you that you should only use it in your code RIGHT after you insert a record with YOUR code. Because if you just call this function it will return you the ID from some other function that last used the INSERT on the connection. In your case it could be maybe writing to audit log table the record that was added and it returns the ID from that table.

After re-reading the manual on the DB::LastId() function, I finally realized that it was an API command. No wonder you all thought I was nuts... LOL.

This is what happens when you work all day and code all night. Anyway, once I understood why I was getting weird results, I rewrote the code, omitting the DB::LastId() command. Now everything works as it should, passing variables to the "Before Display" and "Javascript Onload" events and ultimately populating the data from the original record to the new add page in another table.

Here is the working code from the "After record added" event for anyone's reference or inspiration:

// Start the session if not already started
if (session_status() == PHP_SESSION_NONE) {
session_start();
}

// Directly query the last inserted ID
$sql = "SELECT MAX(ID) as lastID FROM RoomPMEng";
$result = CustomQuery($sql);
$row = db_fetch_array($result);

$lastID = $row['lastID'];

// Set the session variable
if ($lastID) {
$_SESSION['lastRoomPMEngID'] = $lastID;

}

Thanks again to everyone for the suggestions and help!

Just a note to other programmers why you should try to avoid the SELECT MAX approach for getting the last inserted autoincrement id.

Lots of years ago when I was working in a team that worked on an application (then desktop VB6 MSSQL) with lots of users and transactions, one developer used this SELECT MAX to get the last ID. Everything worked ok, but sometimes the wrong ID was returned (it was for bank transactions).
Yes, we are talking about milliseconds, but when the app is used by many users who insert records into the same table at the same time it could happen.

Especialy if the table grows to millions of records and then in some cases the MAX() can be very slow (seconds).

That is why server databases have functions like:
MySQL: SELECT LAST_INSERT_ID()
MSSQL: SELECT SCOPE_IDENTITY()

These functions return the last inserted autoincremented ID from the current connection and the current session.
PHPRunner internally for the database function "DB::LastId()" uses these database functions on the server databases to retrieve the autoincrement ID.
So if you can, avoid the SELECT MAX approach and use the DB::LastId() right after the sql "INSERT" if you are inserting the records from your own code or If you are in the After Record Added event get the ID from the $values or $keys arrays.

From the MySQL documentation:
LAST_INSERT_ID(): This function returns the last auto-incremented value generated by the INSERT statement for the current connection. This is connection-specific, meaning that each user/session will get the correct value they generated, regardless of other operations happening simultaneously in the database.
It is a built-in function designed specifically to return the last auto-incremented value, it is optimized for performance and incurs minimal overhead.

MAX(): Using SELECT MAX(id) FROM table might not return the correct result in a high-concurrency environment. If another user inserts a row after your insert but before you run the MAX() query, the MAX() value could reflect this other user's insert instead of your own.
Using SELECT MAX(id) FROM table requires scanning the index to find the highest value. This can be significantly slower, especially on large tables with many rows.