List Category and sub-category

Question

List Category and sub-category

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

Good afternoon, I am developing a framework in the MvC model and wanted to list the categories and the subcategory I am using a unique CATEGORY table. with the following fields.

 mysql> select * from ws_categoria;
+--------------+-----------+-----------+--------------+
| id_categoria | id_parent | categoria | subcategoria |
+--------------+-----------+-----------+--------------+
|            4 |      NULL | VIATUAS   | NULL         |
|            5 |      NULL | DESPESAS  | NULL         |
|            6 |      NULL | OUTROS    | NULL         |
|            7 |         4 | NULL      | viatura 1    |
|            8 |         4 | NULL      | viatrua 2    |
|            9 |         5 | NULL      | gasolina     |
|           10 |         5 | NULL      | pneus        |
|           11 |         6 | NULL      | Reparacao    |
|           12 |         6 | NULL      | Pintura      |
+--------------+-----------+-----------+--------------+

The problem is when I try to recover the data, I can not separate the type data.

IN THE CATEGORY VIATRUAS bring only the groups of vehicles as the other categories, the query I am using is this.

SELECT
app.categoria.categoria,
app.subcategoria.subcategoria
FROM
app.ws_categoria AS subcategoria
JOIN app.ws_categoria AS categoria
ON app.subcategoria.id_parent = app.categoria.id_categoria

RESULT

+-----------+--------------+
| categoria | subcategoria |
+-----------+--------------+
| VIATUAS   | viatura 1    |
| VIATUAS   | viatrua 2    |
| DESPESAS  | gasolina     |
| DESPESAS  | pneus        |
| OUTROS    | Reparacao    |
| OUTROS    | Pintura      |
+-----------+--------------+

CONCLUSION

When I try to retrieve the values in foreach the result I get is this:

array (size=6)
  0 => 
    object(stdClass)[13]
      public 'categoria' => string 'VIATUAS' (length=7)
      public 'subcategoria' => string 'viatura 1' (length=9)
  1 => 
    object(stdClass)[14]
      public 'categoria' => string 'VIATUAS' (length=7)
      public 'subcategoria' => string 'viatrua 2' (length=9)
  2 => 
    object(stdClass)[15]
      public 'categoria' => string 'DESPESAS' (length=8)
      public 'subcategoria' => string 'gasolina' (length=8)
  3 => 
    object(stdClass)[16]
      public 'categoria' => string 'DESPESAS' (length=8)
      public 'subcategoria' => string 'pneus' (length=5)
  4 => 
    object(stdClass)[17]
      public 'categoria' => string 'OUTROS' (length=6)
      public 'subcategoria' => string 'Reparacao' (length=9)
  5 => 
    object(stdClass)[18]
      public 'categoria' => string 'OUTROS' (length=6)
      public 'subcategoria' => string 'Pintura' (length=7)

mysql

asked by anonymous 07.12.2017 / 17:55

2 answers

Send html file to FTP - Python 3.6 CMD File and Task Scheduler

score 0 · Answer 1

here is my query;

In case I wanted to sort by groups

   mysql> SELECT
    -> app.subcategoria.subcategoria,
    -> app.categoria.categoria
    -> FROM
    -> app.ws_subcategoria AS subcategoria
    -> JOIN app.ws_categoria AS categoria
    -> ON app.subcategoria.categoria_id_categoria = app.categoria.id_categoria;
+--------------+-----------+
| subcategoria | categoria |
+--------------+-----------+
| Gasolina     | DESPESAS  |
| Papel        | DESPESAS  |
| VIATURA1     | VIATURAS  |
| VIATURA2     | VIATURAS  |
+--------------+-----------+
4 rows in set (0,00 sec)

I would have to pass a WHERE condition to sort by category and subcategory. ?

score 0 · Answer 2

|         CATEGORIA       |          SUBCATEGORIA
|                         |
| ID - INT - PRIMARYKEY   | ID - INT - PRIMARYKEY
| DESCRICAO - VARCHAR(50) | DESCRICAO - VARCHAR(50)
|                         | CATEGORIAID - INT FOREINGKEY (CATEGORIA)

    SELECT SUB.DESCRICAO
           CAT.DESCRICAO
      FROM SUBCATEGORIA SUB
           JOIN CATEGORIA CAT ON CAT.ID = SUB.CATEGORIAID
     ORDER BY CAT.DESCRICAO, SUB.DESCRICAO

This SQL will bring all SUBCATEGORIES registered and bring the CATEGORY that it is related to.