创建表：
/*
Navicat MySQL Data Transfer
Source Server : localhost_3306
Source Server Version : 50540
Source Host : localhost:3306
Source Database : test
Target Server Type : MYSQL
Target Server Version : 50540
File Encoding : 65001
Date: 2018-04-27 13:48:31
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for table1
-- ----------------------------
DROP TABLE IF EXISTS `table1`;
CREATE TABLE `table1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=gbk;
-- ----------------------------
-- Records of table1
-- ----------------------------
INSERT INTO `table1` VALUES ('1', 'Home', '0');
INSERT INTO `table1` VALUES ('2', 'About', '1');
INSERT INTO `table1` VALUES ('3', 'Contact', '2');
INSERT INTO `table1` VALUES ('4', 'Legal', '2');
INSERT INTO `table1` VALUES ('5', 'Privacy', '4');
INSERT INTO `table1` VALUES ('6', 'Products', '1');
INSERT INTO `table1` VALUES ('7', 'Support', '1');
查询
SELECT T2.id, T2.name
FROM (
SELECT
@r AS _id,
(SELECT @r := parent_id FROM table1 WHERE id = _id) AS parent_id,
@l := @l + 1 AS lvl
FROM
(SELECT @r := 5, @l := 0) vars,
table1 h
WHERE @r <> 0) T1
JOIN table1 T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC
在这里设置子类id
(SELECT @r := 5, @l := 0) vars,
在Php中使用
<?php
include_once("../libs/variable.php");
$parentsCate=$db->getAll("SELECT T2.id, T2.name
FROM (
SELECT
@r AS _id,
(SELECT @r := parent_id FROM table1 WHERE id = _id) AS parent_id,
@l := @l + 1 AS lvl
FROM
(SELECT @r := 5, @l := 0) vars,
table1 h
WHERE @r <> 0) T1
JOIN table1 T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC ");
foreach($parentsCate as $key=>$val){
echo $val['name'];
}
echo "<hr>";
$result=mysql_query("SELECT T2.id, T2.name
FROM (
SELECT
@r AS _id,
(SELECT @r := parent_id FROM table1 WHERE id = _id) AS parent_id,
@l := @l + 1 AS lvl
FROM
(SELECT @r := 5, @l := 0) vars,
table1 h
WHERE @r <> 0) T1
JOIN table1 T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC");
while($rs=mysql_fetch_array($result)){
echo $rs["name"];
}
?>