Get Auto-Increment during insert
I am inserting records into a table. The 2 fields that are relevant here are the fields I made: "RecordID" and "ItemID"
RecordID is the tables primary key with an auto-increment.
ItemID is specific to the table varchar(255).
Is there a way I can insert the primary key value into ItemID also.
If the next increment being inserting into the RecordID is 5 then I want the ItemID to be "000005". All I need is to be able to get the increment number then format it to ItemID. Formatting is not a problem, I just need to be able to get the increment number (during insert)
RecordID is the tables primary key with an auto-increment.
ItemID is specific to the table varchar(255).
Is there a way I can insert the primary key value into ItemID also.
If the next increment being inserting into the RecordID is 5 then I want the ItemID to be "000005". All I need is to be able to get the increment number then format it to ItemID. Formatting is not a problem, I just need to be able to get the increment number (during insert)
$SQL = "INSERT INTO items (RecordID, ItemID)";
$SQL .= " VALUES(NULL, '$ItemID')";
I don't understand why you want to do this, as redundant information is usually something you would try to avoid. However, you could use the last_insert_id() function, like this:
$SQL = "INSERT INTO items (RecordID) VALUES(NULL)";
mysql_query($SQL);
mysql_query("UPDATE items set ItemID=RecordID WHERE RecordID=last_insert_id()");
ASKER
cxr:
Yes the data is redundant for normalization purposes, but I need the ItemID to be a 6 digit number. Yes I could alternatively get the RecordID then format it with 6 digits but some apps that read into this may have a few issues then I have to go back to each of those apps and update them. Too much work.
I did come up with exactly what you did, running 2 queries - Inserting then Updating. I could can do that, I was just wondering if there was a simpler way with some built-in function I can use to do this in a single query. If not running 2 queries is not a problem.
Yes the data is redundant for normalization purposes, but I need the ItemID to be a 6 digit number. Yes I could alternatively get the RecordID then format it with 6 digits but some apps that read into this may have a few issues then I have to go back to each of those apps and update them. Too much work.
I did come up with exactly what you did, running 2 queries - Inserting then Updating. I could can do that, I was just wondering if there was a simpler way with some built-in function I can use to do this in a single query. If not running 2 queries is not a problem.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I agree with the genius cxr
ALTER TABLE yourtable CHANGE RecordID RecordID INT(6) ZEROFILL AUTO_INCREMENT PRIMARY KEY;
Backup database first :)
ALTER TABLE items CHANGE RecordID RecordID INT(6) ZEROFILL AUTO_INCREMENT PRIMARY KEY;
sandeep_manne: Never do that. Use the built-in LAST_INSERT_ID() function instead. Blithely selecting the max value from the table is subject to race conditions (if another connection inserts after you insert but before you select, you'll get the wrong value).
Yeah, you could lock the table, or use a transaction, or promise on your grandmother's teeth that you are the only one ever using the application, but why take the risk or limit your ability to scale out?
Yeah, you could lock the table, or use a transaction, or promise on your grandmother's teeth that you are the only one ever using the application, but why take the risk or limit your ability to scale out?
Remove "PRIMARY KEY" in the ALTER TABLE statement from NerdsOfTech, and note that the column will automatically be defined as UNSIGNED when you do this:
ALTER TABLE items CHANGE RecordID RecordID INT(6) ZEROFILL AUTO_INCREMENT;
ASKER
Thanks
This will return last insert rows recordId and in php you can increase that value by 1 and insert into new row.