Autoincrement MySQL table conditional on other column values

Question

I have a database with the following structure:

ID  LEVEL  MOVE NUMBER  VALUE
1     1         1        123
1     1         2        345

I am recording the variable "value" with javascript and sending it to the database via PHP. What I need to do is build an SQL query to update the table by autoincrementing the column "move number". However, this should occur conditional on the columns values "ID" and "Level". This means that if a player with ID=2 plays level 1, the database should update as:

ID  LEVEL  MOVE NUMBER  VALUE
1     1         1        123
1     1         2        456
2     1         1        789

The same goes for the variable level. In the end, say we have a total of 3 players, two levels and 2 moves, the database should look like:

ID  LEVEL  MOVE NUMBER  VALUE
1     1         1        123
1     1         2        456
2     1         1        789
3     1         1        012
1     2         1        345
2     1         2        678
...

Grouping by either id or level is not important in this case. The important thing is that each move is recorded sequentially together with its value. As I am not so much familiar with SQL syntax, I am unsure how to achieve this

So basically the move will increase when ID and level (both) are repeated? — Pankaj, Jun 01 '22 at 21:51
You can do this in MyISAM using a secondary key auto increment. There's no automatic method in InnoDB. — Barmar, Jun 01 '22 at 21:54

score 0 · Accepted Answer · answered Jun 01 '22 at 22:01

I think I understand what you want. "MOVE NUMBER" should be one higher every time you insert a new move for a certain player "ID" and "Level". Calling this "autoincrementing" is somewhat misleading, because MySQL already has an AUTO_INCREMENT, which is something different.

Let's first get the last "MOVE NUMBER" for a "ID" = 1 and "Level" = 1:

SELECT MAX(`MOVE NUMBER`) FROM GameScore WHERE ID = 1 AND Level = 1;

For the last results in your question this should return 2. However, this could return NULL, so we do:

SELECT IFNULL(MAX(`MOVE NUMBER`), 0) FROM GameScore WHERE ID = 1 AND Level = 1;

then it will return 0.

Now all we need to do is insert a new "VALUE", for instance 463. This goes like this:

INSERT INTO GameScore (ID, 
                       LEVEL, 
                       `MOVE NUMBER`, 
                       VALUE)
SELECT 1,
       1,
       IFNULL(MAX(`MOVE NUMBER`), 0) + 1, 
       463 
FROM GameScore 
WHERE ID = 1 AND 
      Level = 1;

Please note that queries are untested, they are just given as examples.

Autoincrement MySQL table conditional on other column values

1 Answers1