Welcome to WebmasterWorld Guest from

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

add new field to db with php mysql

add unique customer number

10:59 pm on Jan 17, 2011 (gmt 0)

5+ Year Member

I have a db that I want to add a customer number field to a table in a db. However, there are already many exixting customers to do one at a time. I want to create unique numbers for each one. An example would be 6516f6511r100000. Then, each one thereafter would increment by one. In addtion, each new entrant would auto-increment. Can anyone help with this?

11:04 pm on Jan 17, 2011 (gmt 0)

10+ Year Member

Is there some sort of ID field already in the db for deleting / editing? Ie some soft of unique index at all?
11:09 pm on Jan 17, 2011 (gmt 0)

5+ Year Member

Yes, the table already has a unique field...the email (hence the problem).
11:16 pm on Jan 17, 2011 (gmt 0)

10+ Year Member

Ok i'm not sure whats up with customer number you gave for an example as far as there being letters involved... but if I was going to just do this with numbers here's how I would do it.

First create a field in the table for the number, I would use INT(11) for this but if you want letters you'll have to use VARCHAR - with whatever size will fit the size string you are using. For the example my new field name is custnumber.
// do standard db connection and selecting

$number = 1000; // starting at 1000

$result = mysql_query("select email from tablename"); // this will give me the emails in the order they were created in the database.
while(list($curemail) = mysql_fetch_row($result)) {

mysql_query("update tablename set custnumber='$number' where email='$curemail');

$number++; // this increments the number for the next record.

12:00 am on Jan 18, 2011 (gmt 0)

10+ Year Member

Oh and then you can actually set the custnumber field to "auto_increment" then in the table options you can set the auto_increment value to the next iteration after the last one... Then when you add new rows to the db you give a blank value for custnumber and then mysql will auto fill that value with the next iteration. I'm pretty sure this will only be solid if you are using integers and no letters.

Also you should set custnumber to the primary key of the table.
7:50 pm on Jan 18, 2011 (gmt 0)

5+ Year Member

Ok, thanks digitsix. I will give it a try.

4:59 am on Jan 19, 2011 (gmt 0)

5+ Year Member

Thanks again....worked like a charm.

Featured Threads

Hot Threads This Week

Hot Threads This Month