Sort according to database | PHP

Question

I have the following structure:

I have 3 types of classification, following the coherence: 1.0.0, 1.1.0, 1.1.1, 1.1.2 - I have to display in front of the titles, each content that belongs to its classification, getting the following way in time to display on the screen:

> 1. Meu conteudo Administrativo
> 1.1 Novo Conteudo
> 1.1.1 André Ribeiro
> 1.1.2 Teste de Gestão de Pessoas

You need to follow from every field.

I’m using codeigniter, but it can be done in basic SQL PHP, and I set it in codeigniter.

I tried to do it this way:

$this->db->select("coe_classificacao");
$this->db->where("set_base != ", 12); // conteudo sobre
$this->db->order_by("coe_classificacao", "ASC");
$this->db->group_by("coe_classificacao");
$consulta = $this->db->get('conteudos')->result();

foreach($consulta as &$valor){
    $this->db->select("coe_titulo, coe_classificacao, coe_classificacao_1, coe_classificacao_2");
    $this->db->where("set_base != ", 12); // conteudo sobre
    $this->db->where("coe_classificacao", $valor->coe_classificacao);
    $this->db->order_by("coe_classificacao_1", "ASC");
    $retorno = $this->db->get('conteudos')->result();

        foreach($retorno as &$valor_retorno){
            $this->db->select("coe_titulo, coe_classificacao_1, coe_classificacao_2");
            $this->db->where("set_base != ", 12); // conteudo sobre
            $this->db->where("coe_classificacao_1", $valor_retorno->coe_classificacao_1);
            $this->db->order_by("coe_classificacao_2", "ASC");
            $valor_retorno->sub = $this->db->get('conteudos')->result();                    
        }
}

But equally, it does not bring me the expected result, I believe you can disregard what I did.

The point is, how do I turn everything into that array, so that I can display it the way I transcribed it?

Answer 1

Your logic is correct apparently, I seem to miss only "save" the reference data to each item:

$this->db->select("coe_classificacao");
$this->db->where("set_base != ", 12); // conteudo sobre
$this->db->order_by("coe_classificacao", "ASC");
$this->db->group_by("coe_classificacao");
$consulta =  json_decode(json_encode($this->db->get('conteudos')->result_array()), True);

foreach($consulta as $key => $valor){
    $this->db->select("coe_titulo, coe_classificacao, coe_classificacao_1, coe_classificacao_2");
    $this->db->where("set_base != ", 12); // conteudo sobre
    $this->db->where("coe_classificacao", $valor->coe_classificacao);
    $this->db->order_by("coe_classificacao_1", "ASC");
    $retorno = $this->db->get('conteudos')->result_array();
    $consulta[$key][] =  json_decode(json_encode($retorno), True);

        foreach($consulta[$key] as $key_neto => $valor_retorno){
            $this->db->select("coe_titulo, coe_classificacao_1, coe_classificacao_2");
            $this->db->where("set_base != ", 12); // conteudo sobre
            $this->db->where("coe_classificacao_1", $valor_retorno->coe_classificacao_1);
            $this->db->order_by("coe_classificacao_2", "ASC");
            $consulta[$key][$key_neto][] = 
    $consulta[$key][] =  json_decode(json_encode($this->db->get('conteudos')->result_array()), True);                    
        }
}

I think that solves!

Answer 2

PHP code, I don’t understand what you mean by PHP pure, use the mysqli:

$sql = "SELECT coe_class, coe_class1, coe_class2, coe_titulo FROM TESTE ORDER BY coe_class,coe_class1,coe_class2;"
  or die("Erro na consulta: " . mysqli_error($sql));

$query = $conn->query($sql);

if ( $query->num_rows > 0 ){
  while ($dados = $query->fetch_assoc()){
      $out = '';
      $out = ($dados['coe_class']  > 0 ? $dados['coe_class']  .'.' : '') .
             ($dados['coe_class1'] > 0 ? $dados['coe_class1'] .'.' : '') .
             ($dados['coe_class2'] > 0 ? $dados['coe_class2'] .'.' : '');

      echo rtrim(trim($out), '.') . ' - ' . $dados['coe_titulo'];
      echo '<br>';
  }
} else {
    echo 'Nenhuma linha retornada.';
}

Example of an exit:

1 - Administrative
1.1 - New Content
1.1.1 - Andre
1.1.2 - Management Test