Create select for a PHP table

Question

I’m having trouble creating the select to later use in a table.

The mandatory SQL tables are the following:

“Aluno” (**PK:** id, numero, nome), 
“Area” (**PK:** id, nome), 
“UC” (**PK:** id, nome, **FK:** id_area) 
“Classificacao”
    (
      **PK:** id, 
      **FK:** id_uc, 
      **FK:** id_aluno,
      nota
    )

Goal is to create a table in php (mvc) as follows:

1. Column - Student No
2. Column - Student name
3. Column - Nº disciplines of the student in the area 1
4. Column - Nº disciplines of the student in the area 2
5. Column - If the sum of disciplines is greater than or equal to 6 "Yes" (Admitted)

With the entities and relationships that exist between the tables, which select I have to do the SQL to fill the table?

I actually have the skeleton MVC and select of the leaderboard

function getClassificacao()
{
    $classificacao = array();

    $db=new db();
    $con=$db->connect();

    $sql_query = "SELECT * FROM classificacao";
    $result = $con->query($sql_query);

    $i = 0;
    while($row = mysqli_fetch_array($result))
    {
        $classificacao[$i]["id"] = $row["id"];
        $classificacao[$i]["id_uc"] = $row["id_uc"];
        $classificacao[$i]["id_aluno"] = $row["id_aluno"];
        $classificacao[$i]["nota"] = $row["nota"];
        $i++;
    }
    return $classificacao;
}

Answer 1

You can use a querylike this:

SELECT aluno.nome AS 'Nome Aluno', aluno.numero AS 'Numero Aluno', area.nome AS 'Área', COUNT(area.id) AS 'Disciplinas da Área' FROM aluno INNER JOIN classificacao ON aluno.id = classificacao.id_aluno INNER JOIN uc on classificacao.id_uc = uc.id INNER JOIN area ON uc.id_area = area.id WHERE aluno.id = 1 GROUP BY area.id, aluno.nome;

This returns a result like

I think this is what you want. Then it’s manipulating on the PHP / HTML side