Get the new record primary key ID from MySQL insert query?

You need to use the LAST_INSERT_ID() function: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

Eg:

INSERT INTO table_name (col1, col2,...) VALUES ('val1', 'val2'...);SELECT LAST_INSERT_ID();

This will get you back the PRIMARY KEY value of the last row that you inserted:

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client.

So the value returned by LAST_INSERT_ID() is per user and is unaffected by other queries that might be running on the server from other users.

BEWARE !! of LAST_INSERT_ID() if trying to return this primary key value within PHP.

I know this thread is not tagged PHP, but for anybody who came across this answer looking to return a MySQL insert id from a PHP scripted insert using standard mysql_query calls - it wont work and is not obvious without capturing SQL errors.

The newer mysqli supports multiple queries - which LAST_INSERT_ID() actually is a second query from the original.

IMO a separate SELECT to identify the last primary key is safer than the optional mysql_insert_id() function returning the AUTO_INCREMENT ID generated from the previous INSERT operation.

From the LAST_INSERT_ID() documentation:

The ID that was generated is maintained in the server on a per-connection basis

That is if you have two separate requests to the script simultaneously they won't affect each others' LAST_INSERT_ID() (unless you're using a persistent connection perhaps).