SELECT LAST_INSERT_ID()

[lucasgirod]

I have a (headerless) custom-file, in which I (among a lot of other things) write a record into a database table which has an ID field with auto_increment set.

In PHPMaker 2023 the following code worked:

ExecuteStatement("INSERT INTO files (file_name,file_Header_ID, file_IBAN, file_content) VALUES ('" . $bookings["Filename"] . "','" . $bookings["ID"] . "', '" . $bookings["IBAN"] . "', '" . $file_content . "')");
$file_id = ExecuteScalar("SELECT LAST_INSERT_ID();");

In PHPMaker 2024 $file_id is always 0 instead of the used ID.

How do I query the last inserted ID from MySQL?

Thanks for any help!

[mobhar]

Double check your variables that used by INSERT INTO statement, make sure it contains the valid values.

[lucasgirod]

The variables are correct and the record is created. Just LAST_INSERT_ID() always returns 0.

[mobhar]

It should work as long as you've an auto-increment column in the table.

https://stackoverflow.com/questions/203 ... ing-for-me

[arbei]

You better enable Debug and check if there is any errors during INSERT.

[lucasgirod]

The insert works without error and AUTO_INCREMENT is set in the database. The same code works in PHP Maker 2023.

Could it have to do something with the change of the DBAL in PHPMake 2024?

[arbei]

No, because you select last insert ID directly by SQL yourself, the result comes from the database. Are you sure you have AUTO_INCREMENT field in the table "files" (not just "set in the database")?

[lucasgirod]

Funny enought the following code works - guess I'll have to change all my code...

            $sql_connection = conn();
            $sql_statement = $sql_connection->prepare("INSERT INTO files (file_name,file_Header_ID, file_IBAN, file_imported)
					VALUES ('" . $bookings["Filename"] . "','" . $bookings["ID"] . "', '" . $bookings["IBAN"] . "')");
            $sql_statement->execute();
            $file_id = $sql_connection->lastInsertId();