2

I know this may seem like a similar question out there, but it really is not. I am trying to create a seven digit ID number that is saved in my mySQL database.

I'm using this sprintf("%07d", $idNumber);

This works, and creates a seven digit id number. However, I want to insert this into my database. The problem is I don't know how to increment this ID number then insert it into the database.

Any ideas?

Thanks!

Omar Dajani
  • 378
  • 6
  • 16

1 Answers1

2

You can define a column with the ZEROFILL option, and then insert unformatted integers.

mysql> create table foo (id int(7) zerofill auto_increment primary key);

mysql> insert into foo values (123);

mysql> insert into foo () values ();

mysql> select * from foo;
+---------+
| id      |
+---------+
| 0000123 |
| 0000124 |
+---------+

This is the only time the numeric argument to the INT type has any practical use. I wish they had made the argument on the ZEROFILL keyword instead of the INT keyword. It would have avoided a lot of confusion.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Sorry to bother you again Bill. But how would I go about reseting the auto increment back to zero? – Omar Dajani Jun 16 '17 at 22:04
  • Not surprisingly, this question has already been asked and answered on Stack Overflow: https://stackoverflow.com/questions/8923114/how-to-reset-auto-increment-in-mysql – Bill Karwin Jun 16 '17 at 22:18