Category and subcategory in PHP listing

Question

I have the following function:

public function listar($tipo=null){
    $this->db->select('p1.*, p2.titulo as subcategoria, p2.id as id2');
    $this->db->join("produto_categoria as p2", "p1.id=p2.id_categoria", "LEFT");
    $consulta = $this->db->get('produto_categoria as p1')->result();
    return $consulta;
}

My Table

My current result

I need, when listing, each subcategory to fall within its category, example: Lashes > Yarn, Lashes > 3D. And the rest that don’t have subcategory, just list. What I did wrong?

Listing

<table class="table table-hover m-b-0 c_list">
    <thead>
        <tr>
            <th>Cod</th>                                    
            <th>Titulo</th>                                    
            <th width="10%">Ações</th>
        </tr>
    </thead>
        <tbody>
        <?php foreach($lista as $s_lista){ ?> 
        <tr>
            <td>
                <p class="id"><?php echo $s_lista->id; ?></p>
            </td>
            <td>
                <?php if($s_lista->subcategoria==""){ ?>
                    <strong>Principal Raiz</strong> <i class="fa fa-angle-double-right"></i>
                <?php } else { ?>
                    <?php echo $s_lista->titulo; ?> <i class="fa fa-angle-double-right"></i>

                <?php } ?>
               <?php echo $s_lista->subcategoria; ?>
            </td>                                   
            <td>                                            
                <button type="button" class="btn btn-info" title="Editar"><i class="fa fa-edit"></i></button>
                <button type="button" data-type="confirm" class="btn btn-danger js-sweetalert" title="Deletar"><i class="fa fa-trash-o"></i></button>
            </td>
        </tr>
        <?php } ?>
    </tbody>
</table>

Expected result:

> Cilios 
> Cilios > 3D
> Cilios > Fio a Fio
> Pentes
> Toucas

Answer 1

Dude changed a little its query the only "problem" is that the CI does not work very well with UNION so I rode it and used the $this->db->query().

public function listar($tipo=null){
    $query = "SELECT Id, IdCategoria, Titulo, Ordem, SubCategoria, Id2 FROM
            (
            SELECT id, IdCategoria, Ordem, Titulo, NULL AS SubCategoria, NULL AS Id2 FROM produtocategoria
            UNION
            SELECT p1.id, p1.IdCategoria, p1.Ordem, p1.Titulo, p2.Titulo, p2.id FROM produtocategoria AS p1 LEFT JOIN produtocategoria AS p2 ON p1.id = p2.idcategoria ORDER BY Id, SubCategoria
            ) AS tab
            WHERE IdCategoria = 0"

    return $this->db->query($query)->result();
}

You need to change the column names to fit your case, but that’s the logic. Here’s the result.