List Category and sub-egoria

Asked

Viewed 124 times

0

Good afternoon, I am developing a framework in the Mvc model and wanted to list the categories and subcategories I am using a single table CATEGORY. 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      |
+--------------+-----------+-----------+--------------+

only that the problem and when I will try to recover the data I cannot separate the data from the type.

IN CATEGORY VEHICLES bring only the groups of vehicles as well 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 recover 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

  • I may be wrong and have not understood your question, but I believe that you are performing the database relationship incorrectly, you should create a table for CATEGORY and another for SUBCATEGORY, because in my grant the CATEGORY may have several SUBCATEGORY, then you should relate the SUBCATEGORY table to the CATEGORY table, that is, each registered category you should inform a unique category.

  • Thanks friend for the tip. :)

  • Do you know how to do or would like an example?

  • create a table categories and subcategories and in the table subcategories put the table id categories in the table subcategories, and name the field in a simple way like this => id_subcategory and ai yes relate through category.id = subcategory.id_category

  • Thank you friend thanks for example. I thought I would give to make 1:1 table relationships with herself. XD

  • actually N subcategories may belong to 1 category, already 1 category may not belong to N subcategories, so it is CATEGORY 1 to N SUBCATEGORIES

Show 1 more comment

2 answers

0

|         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 registered SUBCATEGORIES and bring the CATEGORY that it is related to.

  • Friend I did the way you said most ended up returning the same result I had done

0

this here my query;

in case I wanted to order 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. ?

  • I edited my post above, one look at SQL, just use ordey by, it will sort by category, and then by subcategria.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.