Solved

Get Auto-Increment during insert

Posted on 2009-06-29
9
809 Views
Last Modified: 2013-12-13
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)


$SQL = "INSERT INTO items (RecordID, ItemID)";
		$SQL .= " VALUES(NULL, '$ItemID')";

Open in new window

0
Comment
Question by:SaltyDawg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 

Expert Comment

by:sandeep_manne
ID: 24738216
Use SELECT recordId FROM items ORDER BY recordId DESC LIMIT 1;
This will return last insert rows recordId and in php you can increase that value by 1 and insert into new row.
0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24738303
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()");

Open in new window

0
 
LVL 1

Author Comment

by:SaltyDawg
ID: 24738581
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.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 39

Accepted Solution

by:
Roger Baklund earned 500 total points
ID: 24738714
>> get the RecordID then format it with 6 digits

...or you could define the RecordID column with ZEROFILL, then it would be automatically padded with zeroes.
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24742657
I agree with the genius cxr
ALTER TABLE yourtable CHANGE RecordID RecordID INT(6) ZEROFILL AUTO_INCREMENT PRIMARY KEY;

Open in new window

0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24742662
Backup database first :)
ALTER TABLE items CHANGE RecordID RecordID INT(6) ZEROFILL AUTO_INCREMENT PRIMARY KEY; 

Open in new window

0
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 24751566
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?
0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24753121
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;

Open in new window

0
 
LVL 1

Author Closing Comment

by:SaltyDawg
ID: 31598028
Thanks
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Dump data from mysql to xls php 10 52
MySQL 6 43
Data not being replaced when CSV is uploaded 7 40
Windows 10, 64 bit WAMP - won't start 9 24
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question