MYSQL get auto increment/mysql_insert_id() at INSERT

Question

I would like to get auto_incr value of new entry while performing INSERT, and create the table field 'url' on the fly.

Example:

database table fields:
id,category,sex,age,url (i want to insert a url with the auto_incr at the end)

variables:
$category = employee;
$sex = male;
$age = 30;

INSERT INTO table VALUES(NULL,$category,$sex,$age,'mywebsite.com/employee-male-30-00000001')

note: assuming the newly inserted id is 00000001

I am currently inserting the new entry with a blank url, and then getting the mysql_insert_id(), and then updating the new entry with the url.

Is there a better way to accomplish this with just one database interaction?

Answer 1

Don't bother storing in the URL field like that. Just store the URL. When you're selecting out you can either select out the url and id columns and build the concatinated url in the application layer user use SQL like

select concat(url,'-',id) from mytable;

Answer 2

You can do it with one transaction, but you cannot do this with one SQL statement.

The reason is that you can't get the id generated until it is too late to modify the values in other columns. For example, if you were to try to do this with triggers, you can't get the auto-increment value in a BEFORE trigger because it hasn't been generated yet. But you can't modify the value of columns like url in an AFTER trigger.

The only solution is to perform the INSERT and then immediately use an UPDATE to modify your url. Do this within a transaction to ensure that no other thread can see the partially-complete row.

mysql_query("START TRANSACTION");
mysql_query("INSERT INTO `table` 
             VALUES(NULL,$category,$sex,$age,'mywebsite.com/employee-male-30-')");
mysql_query("UPDATE `table` SET url = CONCAT(url, LPAD(id, 8, '0')) 
             WHERE id = LAST_INSERT_ID()");
mysql_query("COMMIT");

If you're using the old ext/mysql interface, you have to execute the START TRANSACTION and COMMIT as query strings, because that interface doesn't have any direct functions for them.

But it would be better for you to switch to PDO, because ext/mysql is deprecated and will be removed in a future version of PHP. That would allow you to stop putting PHP variables into SQL strings.

$pdo = new PDO(...);

$pdo->beginTransaction();

$stmt = $pdo->prepare("
    INSERT INTO `table` 
    SET category = ?, sex = ?, age = ?, url = ?");
$stmt->execute([$category, $sex, $age, "mywebsite.com/employee-male-30-"]);

$pdo->exec("
    UPDATE `table` 
    SET url = CONCAT(url, LPAD(id, 8, '0')) 
    WHERE id = LAST_INSERT_ID()");

$pdo->commit();