I am working on a program to queue players of any video game of 4 players
this is my table structure:
+-----------------+--------------+------+-----+-------------------+-------------
---+
| Field | Type | Null | Key | Default | Extra
|
+-----------------+--------------+------+-----+-------------------+-------------
---+
| user_id | int(11) | NO | PRI | NULL | auto_increme
nt |
| user_name | varchar(255) | NO | | NULL |
|
| user_priority | varchar(2) | NO | | NULL |
|
| user_status | varchar(2) | NO | | NULL |
|
| user_event_id | int(11) | NO | MUL | NULL |
|
| user_modifiedon | timestamp | NO | | CURRENT_TIMESTAMP |
|
+-----------------+--------------+------+-----+-------------------+-------------
and this is the inserted data:
mysql> SELECT * FROM `ozbah_users`
-> ORDER BY `ozbah_users`.`user_modifiedon` ASC;
+---------+-----------+---------------+-------------+---------------+-----------
----------+
| user_id | user_name | user_priority | user_status | user_event_id | user_modif
iedon |
+---------+-----------+---------------+-------------+---------------+-----------
----------+
| 1 | A | A1 | A | 1 | 2015-06-26
22:22:45 |
| 2 | B | A2 | A | 1 | 2015-06-26
23:02:25 |
| 3 | C | A3 | A | 1 | 2015-06-26
23:22:16 |
| 4 | D | A4 | A | 1 | 2015-06-26
23:22:17 |
| 5 | E | W1 | A | 1 | 2015-06-26
23:22:19 |
| 6 | F | W2 | A | 1 | 2015-06-26
23:22:35 |
| 7 | G | W3 | A | 1 | 2015-06-26
23:22:55 |
as you see user_priority has 2 values An for Active or current players Wn for waiting players n is the queue number of players list
Active players An from 1 to 4
waiting players Wn from 1 to no limit
the condition is that Active players are 2 teams
team 1: user_priority A1 and A2
team 2: user_priority A3 and A4
if team loses the game both players will move to the end of waiting list, and the firs 2 players in waiting list will move to the end of Active list and the winner team will move to the top on Active player list
for example team 2 loses so A3 and A4 will move to end of waiting list and the first 2 names of Waiting list will go to end of Active list
I'll just change the name of players and user_modifiedon
+---------+-----------+---------------+-------------+---------------+-----------
----------+
| user_id | user_name | user_priority | user_status | user_event_id | user_modif
iedon |
+---------+-----------+---------------+-------------+---------------+-----------
----------+
| 1 | A | A1 | A | 1 | 2015-06-26
22:22:45 |
| 2 | B | A2 | A | 1 | 2015-06-26
23:02:25 |
| 3 | E | A3 | A | 1 | 2015-06-26
23:22:16 |
| 4 | F | A4 | A | 1 | 2015-06-26
23:22:17 |
| 5 | G | W1 | A | 1 | 2015-06-26
23:22:19 |
| 6 | H | W2 | A | 1 | 2015-06-26
23:22:35 |
| 7 | I | W3 | A | 1 | 2015-06-26
23:22:55 |
| 8 | C | W4 | A | 1 | 2015-06-26
23:42:18 |
| 9 | D | W5 | A | 1 | 2015-07-01
22:32:49 |
+---------+-----------+---------------+-------------+---------------+-----------
----------+
and if I want to insert a new player will be at the end of waiting list like this:
+---------+-----------+---------------+-------------+---------------+-----------
----------+
| user_id | user_name | user_priority | user_status | user_event_id | user_modif
iedon |
+---------+-----------+---------------+-------------+---------------+-----------
----------+
| 1 | A | A1 | A | 1 | 2015-06-26
22:22:45 |
| 2 | B | A2 | A | 1 | 2015-06-26
23:02:25 |
| 3 | E | A3 | A | 1 | 2015-06-26
23:22:16 |
| 4 | F | A4 | A | 1 | 2015-06-26
23:22:17 |
| 5 | G | W1 | A | 1 | 2015-06-26
23:22:19 |
| 6 | H | W2 | A | 1 | 2015-06-26
23:22:35 |
| 7 | I | W3 | A | 1 | 2015-06-26
23:22:55 |
| 8 | C | W4 | A | 1 | 2015-06-26
23:42:18 |
| 9 | D | W5 | A | 1 | 2015-07-01
22:32:49 |
| 10 | J | W6 | A | 1 | 2015-07-01
22:32:49 |
+---------+-----------+---------------+-------------+---------------+-----------
----------+
Also if I want to delete a user from the list I'll change user_status from A Active to D Deleted and user_priority to -1. But the issue here is that user_priority number of the next players must be shifted and modified.
so if I have (1,2,3,4,5) and delete number 2 i Have to change them to this (1,-1,2,3,4)
(IT IS NOT A BIG DEAL FOR NOW) because I am going to ignore its value on my program I am using it to just sort the list
but if there is a better table structure or better DB Design to fix this I'm welling to hear your suggestions
+---------+-----------+---------------+-------------+---------------+-----------
----------+
| user_id | user_name | user_priority | user_status | user_event_id | user_modif
iedon |
+---------+-----------+---------------+-------------+---------------+-----------
----------+
| 1 | A | A1 | A | 1 | 2015-06-26
22:22:45 |
| 2 | B | A2 | A | 1 | 2015-06-26
23:02:25 |
| 3 | E | A3 | A | 1 | 2015-06-26
23:22:16 |
| 4 | F | A4 | A | 1 | 2015-06-26
23:22:17 |
| 5 | G | W1 | A | 1 | 2015-06-26
23:22:19 |
| 6 | H | -1 | D | 1 | 2015-06-26
23:22:35 |
| 7 | I | W3 | A | 1 | 2015-06-26
23:22:55 |
| 8 | C | W4 | A | 1 | 2015-06-26
23:42:18 |
| 9 | D | W5 | A | 1 | 2015-07-01
22:32:49 |
| 10 | J | W6 | A | 1 | 2015-07-01
22:32:49 |
+---------+-----------+---------------+-------------+---------------+-----------
----------+
I have create it a similar thing in java using 2 arraylists like this is the java code
These 2 arraylist will contain the list of Active and Waiting Players
public class ArrayListDemo {
public static void main(String[] args) {
// create an array list
ArrayList current = new ArrayList();
ArrayList waiting = new ArrayList();
// Display size
System.out.println("Initial size of current: " + current.size());
System.out.println("Initial size of waiting: " + waiting.size());
// add elements to the current array list
current.add("A");
current.add("B");
current.add("C");
current.add("D");
System.out.println("Size of current after additions: " + current.size());
// add elements to the waiting array list
waiting.add("E");
waiting.add("F");
System.out.println("Size of waiting after additions: " + waiting.size());
// display the array list
System.out.println("Contents of current: " + current);
System.out.println("Contents of waiting: " + waiting);
// Remove elements from current array list
current.add(waiting.get(0));
current.add(waiting.get(1));
System.out.println("Contents of current BEFORE Deleting: " + current);
System.out.println("Size of current BEFORE Deleting: " + current.size());
waiting.add(current.get(0));
waiting.add(current.get(1));
System.out.println("Contents of waiting BEFORE Deleting: " + waiting);
System.out.println("Size of waiting BEFORE Deleting: " + waiting.size());
current.remove(0);
System.out.println("current.get(0): "+current.get(0));
current.remove(0);
System.out.println("current.get(0): "+current.get(0));
System.out.println("Contents of current AFTER Deleting: " + current);
waiting.remove(0);
waiting.remove(0);
System.out.println("Contents of waiting AFTER Deleting: " + waiting);
}
}
and this is the output
Initial size of current: 0
Initial size of waiting: 0
Size of current after additions: 4
Size of waiting after additions: 2
Contents of current: [A, B, C, D]
Contents of waiting: [E, F]
Contents of current BEFORE Deleting: [A, B, C, D, E, F]
Size of current BEFORE Deleting: 6
Contents of waiting BEFORE Deleting: [E, F, A, B]
Size of waiting BEFORE Deleting: 4
current.get(0): B
current.get(0): C
Contents of current AFTER Deleting: [C, D, E, F]
Contents of waiting AFTER Deleting: [A, B]
my question is, what is the best SQL query to update multiple rows and keep the sequence correct as my example?
thank you
Aucun commentaire:
Enregistrer un commentaire