Transpose query result set

Question

So I have a database with players names and their skill level. It looks like this:

Id | Name  | Level
1  | Peter |  24
2  | Andy  |  23
...
24 | John  |  1

The first player in the list with the highest level is the strongest one, and the last is the weakest.

I need to sort them in groups with 4 players, so if I have 24 people there will be 6 groups.

The way I need to sort it I call "zig-zag".

It goes like this:

Ag Bg Cg Dg Eg Fg
01 02 03 04 05 06
12 11 10 09 08 07
13 14 15 16 17 18
24 23 22 21 20 19

So the A group will consist of players: 1, 12, 13, 24.

B group of players: 2, 11, 14, 23.

C group of players: 3, 10, 15, 22 and so on.

It's easy to do it by hand, but how I could automate this sort with PHP language?

The groups should be array list (I think so) which could I easily put to the group tables in database.

Is it just term about text writing style or is there this sorting a thing? — Bam Bino, Mar 21 '15 at 19:16
Not for sorting, since this isn't a special type of sort, just a way of sorting differently in different groups. However, the style of group sorting you are doing has the same characteristics as boustrophedon writing, so it reminded me of it. — siride, Mar 22 '15 at 15:10

user3942918 · Accepted Answer · 2015-03-21T16:15:40.113

The idea would be to:

Sort your starting data (or preferably, start with it sorted).
Split it into chunks, basically one per each of your rows.
Reverse the order of every other chunk.
Flip the matrix so you've got your groups - one per column instead of one per row.

Example:

// Basic sample data.
$players = range(1, 24);

// Sort them ascending if you need to.
sort($players);

// Make a matrix. 2d array with a column per group.
$matrix = array_chunk($players, ceil(count($players)/4));

// Reverse every other row.
for ($i = 0; $i < count($matrix); $i++) {
    if ($i % 2) {
        $matrix[$i] = array_reverse($matrix[$i]);
    }
}

// Flip the matrix.
$groups = array_map(null, ...$matrix); // PHP 5.6 with the fancy splat operator.
//$groups = call_user_func_array('array_map', array_merge([null], $matrix)); // PHP < 5.6 - less fancy.

// The result is...
print_r($groups);

Output:

Array
(
    [0] => Array
        (
            [0] => 1
            [1] => 12
            [2] => 13
            [3] => 24
        )

    [1] => Array
        (
            [0] => 2
            [1] => 11
            [2] => 14
            [3] => 23
        )

    [2] => Array
        (
            [0] => 3
            [1] => 10
            [2] => 15
            [3] => 22
        )

    [3] => Array
        (
            [0] => 4
            [1] => 9
            [2] => 16
            [3] => 21
        )

    [4] => Array
        (
            [0] => 5
            [1] => 8
            [2] => 17
            [3] => 20
        )

    [5] => Array
        (
            [0] => 6
            [1] => 7
            [2] => 18
            [3] => 19
        )

)

Transpose query result set

1 Answers1

Example:

Output:

Linked

Related