If you’ve been working with Firebird, Oracle, or any number of other databases, the first thing you’ll want to understand is how to work with MySQL auto_increment keys. Anytime you work with keys that are auto incremented by the database engine, you will need a way to retrieve the key values for your insert statements. Relational database are built on joins and understanding how to assign your foreign keys is essential.

Using MySQL to manage your foreign key inserts you can use the LAST_INSERT_ID() function. This allows you to directly insert the value for your foreign keys without having to execute a query or some other mechanism. The LAST_INSERT_ID() MySQL function returns the value of the PRIMARY KEY previously inserted.

This works perfectly for simple designs where only a single foreign key exists and you want to link to the master table.

Example: Tables used

Example: Insert statements

In PHP we have the mysql_insert_id() function, which allows us to retrieve the last key inserted. This function works pretty good, but it must be executed before you commit your transaction, otherwise you might get unexpected results. Additionally, it’s ideal to execute the statement directly after you submit your insert statement.

Using this function gives us more flexibility in key management in our applications, especially when working with tables that have multiple foreign keys.

Example: PHP statements

Note: In a “data aware controls” environment, it’s not always easy to manage your auto incrementing keys (a lot of this is due to design, not just environment). In most cases where I have the decision making power I try to avoid data aware control development (especially on the web, now that Delphi for PHP is available). Read only grids are an exception, but most of the time you can design your applications to avoid “live data aware” entry/edit forms. It may seem like more work, but I believe in the end it’s cleaner and easier to debug.