WampServer

Is there a way to start the ID number with alpha numeric. let's say, it normally by default starts with 1, now i would like to start it with "emp001" and so on.


thank you

The default way MySQL stores numbers is without any zeros in front of the number. However, there is an easy way make MySQL store numbers with a set number of zeros before the number like what you are looking for using a little known MySQL attribute called UNSIGNED, and another called ZEROFILL. Now, it will be highly impossible to try to tell you how to alter your MySQL database id column to use the UNSINGED ZEROFILL attribute unless you are a MySQL guru and phpMyAdmin pro. So, I have created a small demo database you can use/view so you can see how to use UNSIGNED ZEROFILL. Since this forum does not allow extra spaces, I have uploaded the file to a file service so you can see the correct sql syntax to use.

Download my sql file here:
[rapidshare.de]

You can dump sql files with phpMyAdmin using the import feature.

Now, back to the show... Take a look at my sql file, and you will notice that I have added the UNSIGNED ZEROFILL attribute to the id column in my demo database. The UNSIGNED part tells MySQL that the id column I have in my sql statement will only contain a positive number. And since I am useing the TINYINT type, my data will be an integer (numbers) type with the lowest possible value being 1 and a max value of 65535. The ZEROFILL part tells MySQL to store zeros before the number (1 will now be 001).

You also need to take notice of this setting, id TINYINT(3). The (3) part tells MySQL that the id column will have a max length of 3. This means when you store number 1 in the id column in the database, MySQL will now store that number as 001. Another example, if you store the number 55, then MySQL will save the number as 055. Also, when you try to access that data in the database, they will still have the zeros in front.

Here is a sample php code you can use with your empXXX numbers:

$dblink = myql_connect ('localhost', 'root', 'password');
mysql_select_db ('somedata', $dblink);

$sql = "SELECT id, text FROM testdb WHERE id = 1";
$results = mysql_query($sql, $dblink);

$empid_number = "emp" . $results['id'];

echo 'Emp Number: ' . $empid_number;

Also a small note, if you use the format emp001, the max number you can have is 999 because you cannot use emp1000 because it is to many characters. If you want to have more, you can set the length to 4 and have up to int9999.

More Info (Highly Recommended):

Unsigned and Zerofill info and other column types:
[dev.mysql.com]



Post Edited (04-23-06 23:13)

CyberSpatium
----------------------
WAMP Forum Admin

Web Development for Newbie's Blog - Check out my new blog. It is for web developers, and especially tailored for the web development newbie. If you are not fluent in “geek speak”, then this incredible resource is just you. And even if you are a web development pro, this is a great resource to check out some of the latest web development tips, news, tutorials, codes and more.

Thank you very very very much. I did try your demo and it worked. I can now have the 000 in front of 1. Although the last code you gave me (php code) is very appreciated but i am only, for now, trying to learn how to put different attribute of id. I am still trying to look for an answer on how to create an automatic alpha-numeric ID like "emp001" and so on, in MYSql. Since i can put 000 in front of 1, how do i put the text "emp" in fron of 0001? Please help again. Thank you and i really appreciated your good respond to my previous issue.

use of alpha numeric for your id column is not recommended. use integers (numbers) so you can search, indexes, and use primary key. also it is much easier to write php code that uses numbers then alpha numeric. especially with arrays and loops.

okay. thanks for the good advice.