add new field to db with php mysql

add unique customer number

[weddingm]

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?

Thanks,
Matt

[digitsix]

Is there some sort of ID field already in the db for deleting / editing? Ie some soft of unique index at all?

[weddingm]

Yes, the table already has a unique field...the email (hence the problem).

[digitsix]

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.
}

?>

[digitsix]

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.

[weddingm]

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

Matt

[weddingm]

Thanks again....worked like a charm.