CODEWITHSUNDEEP
phpmysql
Anu
Anu

Reputation: 31

Autodisplay custom format invoice no in PHP and Mysql

How can I make MySQL auto increment in 4 digit format? So instead of '1' make '0001'? and Inv-0001? This invoice no was also display in text value by default while page loading.

Upvotes: 3

Views: 251

Answers (2)

Rahul
Rahul

Reputation: 2474

Use zerofill attribute:

CREATE TABLE my_table (
  prefix CHAR(2),
  number INT(5) ZEROFILL AUTO_INCREMENT,
  PRIMARY KEY (number)
);

INSERT INTO my_table (prefix) VALUES ('XX'), ('XX'), ('XX');

SELECT concat(number,prefix) FROM my_table;

Demo

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

One option is to keep using the same auto increment column, and use LPAD to pad the left of the id column with zeroes, whenever you need your four digit ID in the presentation layer. Note that LPAD accepts a string argument, not a numeric one, so you should cast the id column first to CHAR before calling it:

SELECT LPAD(CAST(id AS CHAR(4)), 4, '0')
FROM yourTable

Demo here:

SQLFiddle

Upvotes: 3

Related Questions