SELECT LAST_INSERT_ID()

PHPMaker General Discussion (PHPMaker)
1

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!

2

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

3

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

4

It should work as long as you’ve an auto-increment column in the table.https://stackoverflow.com/questions/2033366/mysql-why-is-last-insert-id-not-working-for-me

5

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

6

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?

7

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”)?

8

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();