Sort by category

Question

Following guys I’ve reviewed this community and got some nice fonts but did not help me, I’m trying to separate my links by categories that are also stored in the database.

If anyone has a tutorial to pass me thanks.

See the prints below

1st print category table 2nd print link table 3rd print home page

As you can see in the third image, it’s not separating by categories it’s a tremendous mess, now let’s go to the codes:

My model:

<?php

class Docs_Model extends Model {

    public function __construct() {
        parent::__construct();
    }

    public function getCategories() {
        return $this->_db->read('SELECT * FROM ' . DATABASE_PREFIX . 'cats', array());
    }

    public function getDocsCategories() {

        $data = $this->getCategories();

        $arr = [];

        foreach ($data as $value) {
            $arr = $value;
        }

        //return $this->_db->read('SELECT * FROM ' . DATABASE_PREFIX . 'docs WHERE id_cat=:id', array(
        //  ':id' => $arr['id_cat']
        //));

        return $this->_db->read('SELECT * FROM ' . DATABASE_PREFIX . 'docs c1, ' . DATABASE_PREFIX . 'docs c2 WHERE c1.id_cat = 1 AND c1.id_cat = c2.id_cat', array(
            //':idcat' => $arr['id_cat']
            ));
    }


}

My view:

<section class="section">
    <div class="container">
        <div class="row">
            <div class="col-md-3">
                <?php foreach($data['cats'] as $key => $value): ?>
                <div class="panel panel-default">
                    <div class="panel-heading"><?php echo $value['title'] ?></div>
                    <div class="list-group">
                    <?php foreach($data['docs'] as $key2 => $value2): ?>
                      <a href="#" class="list-group-item"><?php echo $value2['title'] ?></a>
                    <?php endforeach; ?>
                    </div>
                </div>
                <?php endforeach; ?>
            </div>

If you need more information I have, I expect help.

Answer 1

It depends on the link you want to point to. So that common ones don’t repeat themselves you can use something around that

return $this->_db->read('SELECT * FROM ' . DATABASE_PREFIX . 'cats GROUP BY id_cat', array()); // Agrupamento pelo codigo da categoria.

Further research on SUB-SELECT

Example

SELECT * FROM (
SELECT * 
FROM tabela
ORDER BY id_cliente DESC
) AS retorno
GROUP BY id_cat
LIMIT 4

Complementing the help

Reference that can give you a north.

Basically, from what I understand, you will bring the names of the categories and with the subconsulta bring the records referring to each of them

mysql> SELECT A.id_cat,a.title,(SELECT GROUP_CONCAT(id_doc, title) FROM hb_docs WHERE id_cat = A.id_cat) AS retorno FROM hb_cats A ORDER BY a.title ASC;


+--------+-----------+----------------------------------------------------------+
| id_cat | title     | retorno                                                  |
+--------+-----------+----------------------------------------------------------+
|      1 | Começando | 1Visão Geral,2Requisitos,3Intalação                   |
|      3 | Helpers   | 8Database,9Password,10Session,11Url,12Input,13Validações|
|      2 | O Básico  | 4Controladores,5Modelos,6Visualizações,7Erros          |
+--------+-----------+----------------------------------------------------------+
3 rows in set

hope it helps.