How do you append a number for duplicate records in a mysql table?

I think the solution is somewhat easy, of course it is eluding me.

I have two tables and am Joining them on a field with identical values:

example records:

art0001,
art0001,
art0001,
art0002,
art0002,
art0003

What I want to do is to append a number to count every duplicate and echo it out like this:

art0001-1,
art0001-2,
a开发者_StackOverflow社区rt0001-3

art0002-1,
art0002-2,

art0003-1

I came up with this code, but the output just adds a number, but does not restart when a new duplicate is found.

$query = mysql_query( "SELECT * FROM product_image JOIN my_art ON product_folder = product_code" );
if( !$query ) {
     die( mysql_error() ); 
}
$row =  mysql_fetch_array($query);


$i = 0;
while($row = mysql_fetch_array($query)) {
    if($row['COUNT(base_folder)'] < 1 && $row['image_type'] == 'B' && $row['view'] == 'FF') {
        echo $row['base_folder']."-".$i++;
        echo "<br />";
    }

}

Can anyone please help me and tell me what I did wrong?

---

If you want to keep a counter for each 'product' individually, you'll have to check that the product is still the same in the loop, or if this another product. Mind you: the order by clause in the query is needed for this to work.

<?php
$query = mysql_query( "SELECT * FROM product_image JOIN my_art ON product_folder = product_code ORDER BY product_folder" );
if( !$query ) {
     die( mysql_error() ); 
}
$row =  mysql_fetch_array($query);

$i = 0;
$last = '';

while($row = mysql_fetch_array($query)) {
    if( $row['COUNT(base_folder)'] < 1 && $row['image_type'] == 'B' && $row['view'] == 'FF' ) {
        if( $last !== $row['basefolder'] ) {
             $i = 0;
        }
        echo $row['base_folder']."-".$i++;
        echo "<br />";
        $last = $row['basefolder'];
    }
}

---

You can use an array to keep track of the item count:

<?php

$test_data = array(
    'art0001',
    'art0001',
    'art0001',
    'art0002',
    'art0002',
    'art0003',
);

$item_count = array();
foreach($test_data as $item){
    if( isset($item_count[$item]) ){
        $item_count[$item]++;
    }else{
        $item_count[$item] = 1;
    }

    echo $item . '-' . $item_count[$item] . PHP_EOL;
}

---

Here is a simple function for taking an array with duplicate values and appending a number to only the duplicate names.

            Array
            (
                [0] => samename
                [1] => namesame
                [2] => samename
                [3] => namename
                [4] => namename
                [5] => notsame
                [6] => samename
            )

            Array
            (
                [0] => samename
                [1] => namesame
                [2] => samename(2)
                [3] => namename
                [4] => namename(2)
                [5] => notsame
                [6] => samename(3)
            )       


            function uniqueNames($array){
                        $alreadyoccured=array();
                        $newarray=array();
                        foreach($array as $arr){
                            if(array_key_exists($arr,$alreadyoccured)){
                                $alreadyoccured[$arr]=$alreadyoccured[$arr]+1;
                                $newarray[]=$arr.'('.$alreadyoccured[$arr].')'; 
                            }else{
                                $alreadyoccured[$arr]=1;
                                $newarray[]=$arr; 
                            }
                        }

                        return $newarray; 

                    }