0

I have parent and child entries in my database like:

| ID   | Team   |
| 100  | Team_1 |
| 200  | Team_2 |
| 300  | Team_3 |

And players like

| ID | Team_ID   | Player   |
| 1  | 100       | Player_1 |
| 2  | 300       | Player_2 |

Now i need a column with auto-incremtent based for every Team. It should start new count every time a new team will be added.

A fontend table should like:

  • Team players:
  • 100_1 = ...
  • 100_2 = ...
  • 200_1 = ...
  • 200_2 = ...
  • 200_3 = ...
  • 300_1 = ...
  • ...

How to do this in mysql / php?

Thanks.

Marco
  • 131
  • 1
  • 9
  • just add that column as primary key. It will get auto increment every time – Rupal Feb 07 '18 at 09:28
  • Does it matter if later you delete (for example) player 200_2, this would leave a gap. Would you be better off leaving the numbering to your front end code and leave the player table with just a unique id(across all teams). – Nigel Ren Feb 07 '18 at 09:29
  • Sure there will be a gap but that is not important in this case. It is better in this case as absolute reference. If i do it in frontend and a child will de deleted, the reference ID is variable and falsified. – Marco Feb 07 '18 at 09:40

2 Answers2

1

If you just want to display the data at the front end it can be achieved as below using MySQL query:

SET @prev_value = NULL;
SET @rank_count = 1;
SELECT id, Team_ID, 
CONCAT(Team_ID, "_", CASE
    WHEN @prev_value = Team_ID THEN @rank_count := @rank_count + 1
    WHEN @prev_value := Team_ID THEN @rank_count := 1
END) as Team_players
FROM player
ORDER BY Team_ID

SQL Demo: http://sqlfiddle.com/#!9/5df40d/12

Mittal Patel
  • 2,732
  • 14
  • 23
  • Thanks. Seems that i realy do it in frontend. I found a working solution but as the autor wrote it is not save https://stackoverflow.com/questions/10879104/how-auto-increment-within-a-subset-of-the-table-mysql#10881874 – Marco Feb 07 '18 at 14:43
  • Hi, thanks again i use this now in my app but have a sorting issue. So the sorting is 100_1, 100_10, 100_11,100_2, 100_3 ... i think i can´t fix it by mysql ORDER BY – Marco Mar 01 '18 at 07:34
  • http://sqlfiddle.com/#!9/8a7ccb/1- Check this demo it will give the order by like100_1,100_2,100_3,100_4,...,100_11,100_12,200_1 – Mittal Patel Mar 01 '18 at 08:07
0

You need to alter your table,add the needed column then create a trigger on this table after "insert" to fill it with your goal string teamId_playerId. Good luck

BELGOUGI
  • 29
  • 1
  • 7
  • I know I find code only answers need a bit of explanation at times, but this answer provides no code but some suggestions. Would be useful to show some code as to how to achieve this proposed method. – Nigel Ren Feb 07 '18 at 09:32