MYSQL get autoincrement / mysql _insert_id () in INSERT
I would like to get the auto_incr value of a new record when doing INSERT and create the table "url" field on the fly.
Example:
database table fields:
id, category, gender, age, url (I want to insert url with auto_incr at the end)
variables:
$ category = employee;
$ sex = male;
$ age = 30;
INSERT INTO COST VALUES (NULL, $ category, $ sex, $ age, 'mywebsite.com/employee-male-30-00000001')
Note: it is assumed that the entered ID is 00000001
I am currently inserting a new record with an empty url and then getting mysql_insert_id () and then updating the new record with the url.
Is there a better way to accomplish this with only one database interaction?
source to share
You can do it with one transaction, but you cannot do it with one SQL statement.
The reason is that you cannot get the generated ID until it is too late to change the values in other columns. For example, if you try to do this with triggers, you won't be able to get the auto-increment value in the BEFORE trigger, because it hasn't been created yet. But you cannot change the value of type columns url
in AFTER trigger.
The only solution is to do an INSERT and then immediately use UPDATE to change yours url
. Do this inside 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 are using the old ext / mysql interface, you need to execute strings START TRANSACTION
and COMMIT
as query strings because there are no direct functions for this interface for them.
But it would be better to switch to PDO, because ext / mysql is deprecated and will be removed in a future version of PHP.This will allow you to put PHP variables in 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();
source to share