Posted by:
CyberSpatium
(---.hsd1.or.comcast.net)
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.