sgray;10983432 wrote:
the concepts of double linked lists can probably be applied to a mysql table
No. A double linked list contains "nodes", "items" or whatever you want to call them. Each node contains 3 things: a pointer/reference to the next item, another to the previous item, and the actual data. So yes the data of each node can be compared to the data of each DB row, but a db row has no internal ordering (well, they must be stored in some kind of order, but this is arbitrary and according to the SQL standard you can't assume there is one) until you specify one in an order by clause, so you have no means of manipulating the order of rows in this way.
sgray;10983432 wrote:
In terms of writing that back into a database
Since you're using a database, you are dealing with something that more ressembles an array, but in this case you have different means of performing updates. But, while the array has an inherent ordering in its data structure, you instead carry the row ordering in a specific column, which means this is what you'll be updating.
(part of) the table data is id (identifying the player) and order (identifying position in the ladder)
-- table: player
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-- no idea what you call this, but order is a reserved word, so if you use it
-- you have to quote it: "order" in sql standard compliant DBMS, `order` in
-- default setup of mysql
-- i prefer not quoting, so I call it lorder as a shorthand for ladder_order
lorder INT UNSIGNED,
INDEX(lorder)
Since you need to refer to rows by comparing the value of the "lorder" field, you should index it.
The statements to perform the task are actually simple
# move player 4
$player = 4;
# to position
$newPos = 3;
# retrieve player 4's order
$query = printf('SELECT lorder WHERE id=%d', $player);
$r = $db->query($query);
$row = $r->fetch();
$oldPos = $row['lorder'];
# move everyone up one step, from (inclusive) where the player is moved to,
# up to the players previous position
$query = printf('UPDATE player SET lorder = lorder + 1 WHERE lorder >= %d AND lorder < %d',
$newPos,
$oldPos
);
/* Don't do this until you inspect the output of the query so that end cases work etc
* I believe they should, but before persisting data in a live database, it's good
* practice to just check the queries first
*/
# $db->exec($query);
echo $query;
# and move the player to his new position
$query = printf(UPDATE PLAYSER SET lorder = %d WHERE id=%d',
$newPos,
$player
);
# $db->exec($query);
echo $query;
