Return cluster in Mysql vector

Question

I am trying to assemble a Mysql query with multiple joins, in this query has a column that may or may not come duplicated, I would like to put the duplicated rows by this column in an array, using the GROUP BY, query returns only one of the records. Follow the query:

ETID: The idea is to group the classes of a discipline by the day of the week.

'SELECT `l`.`lnkId`, `s`.`schWeekDay`, `d`.`dscType`, `d`.`dscName`, `s`.`schStartAt`, `r`.`rmName`
FROM '.DATABASE['name'].'.`tblschedule` `s`
INNER JOIN '.DATABASE['name'].'.`tblstudent` `st`
    ON `st`.`stdCode` = :username 
INNER JOIN '.DATABASE['name'].'.`tbllinks` `l`
    ON `l`.`lnkUser` = `st`.`stdRegister`
INNER JOIN '.DATABASE['name'].'.`tbldiscipline` `d`
    ON `d`.`dscCode` = `s`.`schDiscipline`
INNER JOIN '.DATABASE['name'].'.`tblroom` `r`
    ON `r`.`rmId` = `s`.`schRoom`
WHERE `s`.`schDiscipline` = `l`.`lnkDiscipline`
    GROUP BY `s`.`schWeekDay`
    ORDER BY `s`.`schWeekDay`, `s`.`schStartAt`'

I’d like to return to something like:

Array(
[0] => Array (
    [0] => stdClass Object
        (
            [lnkId] => 61
            [schWeekDay] => 1
            [dscType] => DI
            [dscName] => Nome da Disciplina 1
            [schStartAt] => 19:20:00
            [rmName] => Sala 1
        )
    [1] => stdClass Object
        (
            [lnkId] => 62
            [schWeekDay] => 1
            [dscType] => DI
            [dscName] => Nome da Disciplina 1
            [schStartAt] => 20:10:00
            [rmName] => Sala 1
        )

[1] => stdClass Object
    (
        [lnkId] => 63
        [schWeekDay] => 2
        [dscType] => DPDI
        [dscName] => Nome da Disciplina 2
        [schStartAt] => 19:20:00
        [rmName] => Sala 3
    )

)

Answer 1

The only solution I found was through PHP even though there is a better way to do it:

// Se a requisição ocorrer com sucesso
if ($stmt->execute()) {
    // Se algum registro for encontrado
    if ($stmt->rowCount() > 0) {
        $group = [1 => [], 2 => [], 3 => [], 4 => [], 5 => []];

        // Filtrar os valores por dia da semana
        foreach ($stmt->fetchAll(PDO::FETCH_OBJ) as $key) {
            switch ($key->schWeekDay) {
                case '1': $group[1][] = $key; break;
                case '2': $group[2][] = $key; break;
                case '3': $group[3][] = $key; break;
                case '4': $group[4][] = $key; break;
                case '5': $group[5][] = $key; break;
                default: break;
            } // switch
        } // foreach

        // Retornar os dados para o cliente
        echo json_encode([
            'error' => false,
            'data' => $group
        ], JSON_THROW_ON_ERROR); // echo

        return;
    } // if

    // Se nenhum dado for encontrado
    throw new RuntimeException('Nenhum registro encontrado!');
} // if

// Se ocorrer algum erro durante a requisição
throw new RuntimeException('Erro ao conectar-se com o servidor!');

Which resulted in the following array:

Array
(
[1] => Array
    (
        [0] => stdClass Object
            (
                [lnkId] => 61
                [schWeekDay] => 1
                [dscType] => DI
                [dscName] => Disciplina teste 1
                [schStartAt] => 19:20:00
                [rmName] => Sala 1
            )

        [1] => stdClass Object
            (
                [lnkId] => 61
                [schWeekDay] => 1
                [dscType] => DI
                [dscName] => Disciplina teste 1
                [schStartAt] => 20:40:00
                [rmName] => Sala 1
            )

        [2] => stdClass Object
            (
                [lnkId] => 61
                [schWeekDay] => 1
                [dscType] => DI
                [dscName] => Disciplina teste 1
                [schStartAt] => 21:30:00
                [rmName] => Sala 1
            )

    )

[2] => Array
    (
        [0] => stdClass Object
            (
                [lnkId] => 63
                [schWeekDay] => 2
                [dscType] => DPDI
                [dscName] => Disciplina teste 2
                [schStartAt] => 19:20:00
                [rmName] => Sala 3
            )

    )

[3] => Array
    (
        [0] => stdClass Object
            (
                [lnkId] => 62
                [schWeekDay] => 3
                [dscType] => DPDI
                [dscName] => Disciplina teste 3
                [schStartAt] => 19:20:00
                [rmName] => Sala 2
            )

        [1] => stdClass Object
            (
                [lnkId] => 64
                [schWeekDay] => 3
                [dscType] => DI
                [dscName] => Disciplina teste 4
                [schStartAt] => 19:20:00
                [rmName] => Sala 4
            )

    )

[4] => Array
    (
    )

[5] => Array
    (
        [0] => stdClass Object
            (
                [lnkId] => 65
                [schWeekDay] => 5
                [dscType] => DI
                [dscName] => Disciplina teste 5
                [schStartAt] => 19:20:00
                [rmName] => Sala 5
            )

    )

)