Mysql-re-examine this question: how to re-Sort subcategories of php unlimited Classification

Step 1. Create an unlimited classification table. {Code...} Step 2. Use the following query statement {code...} Step 3. See orderbyfullpath at the end of the preceding statement. In this case, the general order is correct. However, the value of the DishCategory_Sort field is not sorted. Because orderb...

Step 1. Create an unlimited classification table.

CREATE TABLE IF NOT EXISTS `chi_category` (    `id` int(11) NOT NULL AUTO_INCREMENT,    `DishCategory_Path` varchar(255) DEFAULT NULL,    DishCategory_Sort    int(11),    `DishCategory_Name` varchar(255) DEFAULT NULL,    PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

Step 2. Use the following query statement

SELECT `id`,`DishCategory_Path`,`DishCategory_Name`,`DishCategory_Sort`,concat(`DishCategory_Path`,'-',id) as fullpath FROM `chi_category` order by fullpath

Step 3. At the end of the preceding statementorder by fullpath. In this case, the general order is correct.
HoweverDishCategory_SortThe values of this field are not sorted.
Becauseorder byWhen sorting by multiple columns, the second column is used only when the first column is the same. However, the first groupfullpathIt cannot be the same. So I don't knowDishCategory_SortHow to sort the values of this field.

What I want to achieve is to pressfullpathSort, and then sort sub-categories such as Sichuan food and halogen food under the parent category (EAT). Then, the halogen food should be placed before Sichuan food.
So what should we do now? Great gods

Reply content:

Step 1. Create an unlimited classification table.

CREATE TABLE IF NOT EXISTS `chi_category` (    `id` int(11) NOT NULL AUTO_INCREMENT,    `DishCategory_Path` varchar(255) DEFAULT NULL,    DishCategory_Sort    int(11),    `DishCategory_Name` varchar(255) DEFAULT NULL,    PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

Step 2. Use the following query statement

SELECT `id`,`DishCategory_Path`,`DishCategory_Name`,`DishCategory_Sort`,concat(`DishCategory_Path`,'-',id) as fullpath FROM `chi_category` order by fullpath

Step 3. At the end of the preceding statementorder by fullpath. In this case, the general order is correct.
HoweverDishCategory_SortThe values of this field are not sorted.
Becauseorder byWhen sorting by multiple columns, the second column is used only when the first column is the same. However, the first groupfullpathIt cannot be the same. So I don't knowDishCategory_SortHow to sort the values of this field.

What I want to achieve is to pressfullpathSort, and then sort sub-categories such as Sichuan food and halogen food under the parent category (EAT). Then, the halogen food should be placed before Sichuan food.
So what should we do now? Great gods

Put Dishcategory_sort into fullpath and DishCategory_Path

mysql> insert into chi_category values(11, '0-0', 0), (12, '0-0', 0), (13, '0-0-0-11', 2), (14, '0-0-0-11', 1);Query OK, 4 rows affected (0.00 sec)Records: 4  Duplicates: 0  Warnings: 0mysql> SELECT `id`,`DishCategory_Path`,`DishCategory_Sort`,concat(`DishCategory_Path`,'-',Dishcategory_sort, '-', id) as fullpath FROM chi_category order by fullpath;+----+-------------------+-------------------+---------------+| id | DishCategory_Path | DishCategory_Sort | fullpath      |+----+-------------------+-------------------+---------------+| 11 | 0-0               |                 0 | 0-0-0-11      || 14 | 0-0-0-11          |                 1 | 0-0-0-11-1-14 || 13 | 0-0-0-11          |                 2 | 0-0-0-11-2-13 || 12 | 0-0               |                 0 | 0-0-0-12      |+----+-------------------+-------------------+---------------+4 rows in set (0.00 sec)