Suffix a Number to Duplicate Records

[HoboTraveler]

Hello,

When querying the DB for records, many of the records have the same name. These names are not really duplicates because they are unique.

For the records returned that have the same name, I would like to suffix a number at the end.

For example:

john01
john02
chris01
chris02
chris03
tom
jack

Btw, I do not want to add the incremented ID, because there are many records, 100,000+ and above. The ID would be too big of a number to add as a suffix.

I need to add the suffix number to the duplicate names and not to the names that do not repeat.

TIA

[eelixduppy]

Is there any other reason why you wouldn't want to use unique IDs for each name? Having an auto_increment field is very useful in this case, and will actually help you optimize your database. I'd go with unique IDs if you can.

[HoboTraveler]

Hello,

I am using unique ID's in the DB. If there are two million records, the number to be suffixed would be a seven digit number. I do not want to suffix such huge numbers in the URL.

Also, I do not want to suffix a number on the non duplicate records.

Basically, count the number of duplicates and display each duplicate record with the number suffix in the URL.

TIA

[cameraman]

I didn't test this, but I think it will work:

$qry = mysql_query("SELECT firstname,recordID FROM atable ORDER BY firstname");
if(mysql_num_rows($qry)) {
 list($lastfn,$lastid) = mysql_fetch_row($qry);
 $i = 1;
 while(list($id,$fn) = mysql_fetch_row($qry)) {
  if($fn == $lastfn) {
   if($i == 1) {
    mysql_query("UPDATE atable SET firstname = '$lastfn" . sprintf("%02d",$i++) . "' WHERE recordID=$lastid");
    }// EndIf update the first of a 'series'
   mysql_query("UPDATE atable SET firstname = '$fn" .  sprintf("%02d",$i++) . "' WHERE recordID=$id");
   }// EndIf this name is same as last
   else {
   $lastid = $id;
   $lastfn = $fn;
   $i = 1;
   }// EndIf this is a new name
  }// EndWhile getting records
 }// EndIf have duplicates