how to use Order by in ENUM

Asked

Viewed 134 times

2

Hello would like to know how to use the ORDER BY to do the following in my SQL table category column module I have a ENUM with the following modes 'media','filme','ova' would like to make an application to sort as follows in the code below to sort the items in order media, ova e filme that in the ORDER BY am2.modulo and possible ?

    $medias_skin = $MySQLiconn->query("
    SELECT `am1`.id,`am1`.cat,`am1`.subcat,`am1`.numero,`am1`.episodioTipo,`am1`.fillerType,`am1`.duploType,`am1`.finalType, `am1`.autor,`am1`.fansub,`am1`.arquivo_nome,`am1`.url,`am1`.url2,`am1`.url3,`am1`.url4,`am1`.url5,`am1`.url6,`am1`.url7,`am1`.url8,`am1`.url13,`am1`.url14,`am1`.url15,`am1`.url16,`am1`.url17,`am1`.url18,`am1`.url19,`am1`.url20,`am1`.url25,`am1`.url26,`am1`.url27,`am1`.url28,`am1`.url29,`am1`.url30,`am1`.url31,`am1`.url32,`am1`.linknome01,`am1`.linknome02,`am1`.linknome03,`am1`.linknome04,`am1`.linknome05,`am1`.linknome06,`am1`.linknome07,`am1`.linknome08,`am1`.linknome13,`am1`.linknome14,`am1`.linknome15,`am1`.linknome16,`am1`.linknome17,`am1`.linknome18,`am1`.linknome19,`am1`.linknome20,`am1`.linknome25,`am1`.linknome26,`am1`.linknome27,`am1`.linknome28,`am1`.linknome29,`am1`.linknome30,`am1`.linknome31,`am1`.linknome32, `am1`.player,`am3`.`nome` as mediaNome, `am3`.`animes_categoria_url` as catUrl, `am3`.`animes_subcategoria_url` as subcatUrl, `am2`.`modo` as categoriaModo, `am2`.`modulo` as categoriaModulo, `am2`.`nome` as catNome 
    FROM `m_medias` as am1 USE INDEX (medias_indexadas)
    INNER JOIN `m_categoria` as am2 USE INDEX (categoria_index) ON `am1`.`cat` = `am2`.`id` 
    INNER JOIN `m_subcategoria` as am3 USE INDEX (subcategoria_index) ON `am1`.`subcat` = `am3`.`id` AND `am1`.`cat` = `am3`.`cat`
    WHERE am1.id IN ($media_id)
    GROUP BY `am1`.`id`,`am1`.`subcat`
    ORDER BY am2.modulo 
    ");
php mysqli

2 answers

3


Based on the example of documentation:

Values ENUM are classified based on their index numbers, which depend on the order in which the enumeration members were listed in the column specification. For example, types "B" before "a" to ENUM ('b', 'a'). Empty string types before non-empty strings, and NULL values come before all other enumeration values.

To avoid unexpected results when using the ORDER BY clause in an ENUM column, use one of the following techniques:

  • Specify the ENUM list in alphabetical order.

  • Make sure that the column is sorted alphabetically and not by ORDER BY CAST (col AS CHAR) or ORDER BY CONCAT (col).

For a query using the CAST, would then be:

SELECT enum_col FROM tbl_name ORDER BY CAST(enum_col AS CHAR);

In your case:

ORDER BY CAST(am2.modulo AS CHAR);

2

For numerical data, whereas media is 1, ova is 2 and film is 3:

  1. am2.module -> 1 (average)
  2. am2.module -> 2 (move)

  3. am2.module -> 3 (film)

    ORDER BY am2.module ASC

For char or varchar data, should create a table to indicate the order.

Example

Table modulo_position

Columns:

modulo_id
modulo_position

Finally, everything depends on modeling.

I recommend using the second approach, creating a table where you will have the ordering references.

Browser other questions tagged php mysqli

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