Which Data Type To Use In Mysql Table Using a 5 character number

[mrdee 1]

If I wanted to use an ID field which automatically increments when data are addad, but displays the ID as a 5 figure number, which data type would I have to use?I would want a type that assigns a number to a record in the format "00001", "00002", etc.However, when I use "int" as datatype, it only displays th field contents as "1", "2" etc.So, which data type could do the job for me?i do not need a decimal point or anything, just an id that displays in 5 figures, including the leading zeros.Thanks in advance.

[jlhaslip 4]

here: http://forums.xisto.com/no_longer_exists/ keep that handy while you move along.

 

What you may need to do is define the ID as an INT value for the purpose of being an index, but cast it as a String value and pad the left side with zeroes when you display or print it. Strings don't make the best Index values.

 

Check this: http://dev.mysql.com/doc/refman/5.7/en/numeric-types.html

 

Especially the comments about ZEROFILL under that first Table.

 

Unsigned smallint is good for 64K values. that is a whole bunch of students. And define it as auto-increment.

[mrdee 1]

You see,the intention is to generate like a customer number for them, a sort of customer ID, such as an ID to be printed on invoices.Also good as recognition or to look them up in the database.Eg. 00023= Mr. John Smith, 00038=Mrs. Sally Jones.Or else, a way to auto generate something like:(For piano students) PI00023(For singing students) SI00038(For keyboard students) KE00057Get my drift?That is, of course, difficult to do for me as a beginner, hence the, what probably looks to some reafers her like, moronic questions.All help is very much appreciated and gratefully accepted, though.

[jlhaslip 4]

There are no stupid questions if you learn something from the answer. Even if all you learn is that the respondent doesn't know anything.Anyways, as a suggestion based on the experience I have had, you would be better off not including the alphabetic indicator on those customer id's.What happens if a student quits being a singing student and want lessons on the piano? start a different number with a 'P'?Nope, one student, one number. Let the Income side determine the source of revenue for you and indicate the type of student they are. Believe me, trying to 'code' a stack of numbers is a nightmarish experience. I did it once for a Construction Company that owned several thousand pieces and types of equipment, like a rental Company... yuck! Drove us nuts. They wanted to identify the difference between trucks that had a radio and those that did not have one: into an 8 digit number and make them all signifigant... Nightmare experience.

[mrdee 1]

True really.That, as you say, would probably complicate things needlessly.It was only like a thing of me getting carried away really.Thanks for the advice.