Member Avatar for tryphy

Hi people,

My issue is more of a MySQL issue than php.
I am trying to store a 3digit integer value with a leading Zeros in front such as 001

i have a company table , which auto increments the id as 001, 002, 003...and so on...
In the company table i also have a country radio box..
Which has two country names,
eg: India, Australia..

So when creating the company..
The user types in the data and select a country eg: India,
The Id should be I001,
The next record if he chooses Australia,,, then it will be
A002 ... A003, I004..and so on...till A999(as the last record).

Can anyone help me how to implement this?..

Ur help is much appreciated...
Thanks in advance..

  • 3 Contributors
  • 4 Replies
  • 4K Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by axelotl

Dani AI

Generated

mwasif is right that AUTO_INCREMENT must be numeric and that leading zeros are a presentation concern. In modern MySQL, rely on formatting (for example, LPAD) rather than the old ZEROFILL/display width, which were deprecated in MySQL 8.0.17. Format when you query or in your application layer. Also, phone numbers should be stored as text; collations affect comparison/sort rules, not whether a leading zero is stored. If a 0 disappeared, something was casting to a number before insert. See the notes on numeric attributes and on collations for context. (dev.mysql.com)

For tryphy’s I001/A002 pattern: if you want a single global sequence no matter the country, keep a simple id INT AUTO_INCREMENT and derive the display code at read time:

SELECT
  CONCAT(LEFT(country, 1), LPAD(id, 3, '0')) AS company_code,
  id, country, ...
FROM company;

Do not try to make a generated column that refers to the AUTO_INCREMENT id; MySQL forbids that. If you want a persisted, queryable field, expose it via a view instead: (dev.mysql.com)

CREATE VIEW v_company AS
SELECT
  id,
  country,
  CONCAT(LEFT(country, 1), LPAD(id, 3, '0')) AS company_code
FROM company;

If you ever need per-country counters (e.g., I001 resets independently of A001), use a tiny sequence table and the LAST_INSERT_ID() pattern, which is concurrency-safe per connection: (dev.mysql.com)

-- one row per country_code, initialize next = 0
UPDATE country_seq
SET next = LAST_INSERT_ID(next + 1)
WHERE country_code = 'I';
SELECT LAST_INSERT_ID();  -- returns the next serial for 'I'

Practical tip: avoid hard 3-digit limits; if you might exceed 999, just change the width in LPAD. (dev.mysql.com)

Recommended Answers

All 4 Replies

Member Avatar for mwasif

AUTO_INCREMENT can only be numeric. For leading zeros you can set the field to .You should concatenate character while displaying the data.

Member Avatar for tryphy

AUTO_INCREMENT can only be numeric. For leading zeros you can set the field to .You should concatenate character while displaying the data.

Hi mwasif,

Thanks, i used as numeric field and used Zerofill..
It helped me a lot..
Thanks again

Member Avatar for mwasif

You are welcome tryphy.

Member Avatar for axelotl

I just had this exact problem with tel numbers having a leading zero - 011 123 4567 would be stored as 11 123 4567.

These numbers are entered from a php online form.

I checked the DB field for tel_number which is VARCHAR with length 12 and saw that it was missing the collation parameter - this was blank.

Changing that to " latin1_swedish_ci" seems to have fixed the problem.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.