Add sequential numbers to duplicate values inside while loop

Question

I am pulling out som data from a table called taskmanager. One of the columns contains 'assigned_user_id' and can occur multiple times.

I am trying to figure out, how do I add sequential numbers where assigned_user_id is the same (duplicate).

But I dont really know where to start.

Inside my while loop, I have a counter that counts the duplicates, and I tried playing with that, but couldnt get it to work.

And I dont know if this is the correct path to be on.

The counter inside loop:

$CountOccurence = $conn->prepare("SELECT COUNT(task_assigned_user) FROM taskmanager WHERE task_assigned_user = ?");
$CountOccurence->bind_param("i", $gettaskassigneduser);
$CountOccurence->execute();
$CountOccurence->bind_result($total);
    while ($CountOccurence->fetch()) {
        echo "(".$total.")";
    }                                   
$CountOccurence->close();

This gives:

Normal sequential counter   Assigneduserid     **sequential by assigned_user_id**
  1                              1                          2 ($total)
  2                              2                          2 ($total)
  3                              2                          2 ($total)
  4                              4                          1 ($total)
  5                              1                          2 ($total)

What I would like to have is:

Normal sequential counter   Assigneduserid     **sequential by assigned_user_id**
  1                              1                          1
  2                              2                          1
  3                              2                          2
  4                              4                          1
  5                              1                          2

Can someone pls help me or point me to a direction I can look at. Thank you.

Answer 1

Just add the user ids into an array while you are looping over the data, then you can use array_count_values to easily determine how many times each one occurred so far.

In the first loop iteration, you add the AUid 1 to your array, then the array_count_values will return you the array [1 => 1]. So you use your AUid 1 as key to access the value 1, and output that into your 3rd column.

In the second loop iteration, you add the AUid 2 to your array. Then array_count_values will return [1 => 1, 2 => 1]. Now you use the AUid 2 to access that value 1, and output it into your 3rd column.

In the third loop iteration, you add another AUid 2 to your array. Then array_count_values will return [1 => 1, 2 => 2]. Now you use the AUid 2 to access that value 2, and output it into your 3rd column.

And so on ...