Setting the unique ID for row in MySQL database

Question

I want to set a unique ID for each item that is added to my database similar to Drupal nodes. Just not sure how I know what the next number would be when adding a new item to my database. MySQL statment is:

$query = "INSERT INTO `HERDSIRES`(uid, name, color, gender) VALUES ( VALUE of next uid, '$name', '$color',  '$gender')";

I am thinking I need to query the database prior to the INSERT and find out what the value of the last uid and then add 1 to it and then save it into a variable. I am just not sure if this is the best way.

Any Thoughts?

Answer 1

If the column is set to autogenerate by the database, you can just pass null in the INSERT statement and MySQL will do the rest.

For example, on a primary key column with auto_increment specified.

Yours would be:

INSERT INTO `HERDSIRES`(uid, name, color, gender) 
VALUES ( null, '$name', '$color',  '$gender')";

Or you could omit it entirely since you are enumerating the fields anyway:

INSERT INTO `HERDSIRES`(name, color, gender) 
VALUES ( '$name', '$color',  '$gender')";

edit: Here's how to use PHP to get the last autogenerated ID from a table:

$query = mysql_query('INSERT INTO ...');
$new_row_id = mysql_insert_id();

// do something with $new_row_id