Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
PHP Databases Using PHP in conjunction with databases. PHP questions not specific to databases should be directed to one of the other PHP forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the PHP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 31st, 2004, 04:17 AM
Authorized User
 
Join Date: Oct 2003
Location: KL, wilayah persekutuan, Malaysia.
Posts: 91
Thanks: 0
Thanked 0 Times in 0 Posts
Default auto increment?

CREATE TABLE EMPLOYEE
(
    EMP_ID VARCHAR(6) NOT NULL,
    EMP_FNAME VARCHAR(10) NOT NULL,
    EMP_LNAME VARCHAR(10) NOT NULL,
    PRIMARY KEY(EMP_ID),
);
above is my employee table in the mysql database. The emp_id is like 'EMP001', 'EMP002' and so on.
i would like the addemployee.php page to automatically displayed the next available emp_id. For example,
if the last emp_id is 'EMP54', then the addemployee.php page would display 'EMP55'. Is this possible?
i can do it if the emp_id are number, but i can't do it if it involved characters.

 
Old February 2nd, 2004, 03:56 PM
Friend of Wrox
Points: 2,570, Level: 21
Points: 2,570, Level: 21 Points: 2,570, Level: 21 Points: 2,570, Level: 21
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: San Diego, CA, USA
Posts: 836
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Well, it makes sense that you can't do it with characters. I mean, what comes after EMP009? If you said EMP010, you'd be wrong. Look at an ascii chart. The next character after 9 is the colon (:).

You can use PHP to figure out the next number in the sequence. You can use a string manipulation function (e.g. regular expression match) to find the digits part of the string, convert that string to an integer, increment the integer, and then recreate the string. Don't forget to left-pad your number with zeros! Otherwise, EMP001 would increment to EMP2.

Also, this limits you to 999 employees. (I'm assuming there is no EMP000.) What happens when you hire the 1000th employee? You'll have to modify your database schema since your column limits the string size to 6 characters in the string. All of a sudden, EMP1000 doesn't fit anymore.

Can you see why using strings as primary key fields is not a good way to go?

If you want a numerical index, don't confuse things by storing these numerical indexes as strings. For one thing, it takes more room. An integer typically takes four bytes to store. The string "EMP001" takes 6 if using a fixed-size string. Since you're using a VARCHAR field, the actual length of the string is greater than 6, because you need to store the length of the string as well.



Take care,

Nik
http://www.bigaction.org/




Similar Threads
Thread Thread Starter Forum Replies Last Post
Regarding auto increment of id Rajesh225 ASP.NET 1.0 and 1.1 Basics 1 June 27th, 2007 05:35 PM
Auto increment prad_a MySQL 3 April 7th, 2007 05:47 AM
How to auto increment ? Shawn Mohan SQL Server 2000 2 June 22nd, 2006 03:00 AM
How to auto increment? Shawn Mohan ASP.NET 2.0 Basics 6 June 20th, 2006 10:36 PM
Auto-increment using VBScript CraigKornacki VB How-To 1 April 6th, 2004 02:17 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.