php mysql递归查询获取所有下级_php递归函数查询mysql数据，获取所有上下级

/*

* 获取所有上级

* @param $id String 待查找的id

* @return String | NULL 失败返回null

*/

public function getSup($id,$n = 0)

{

$sql = "SELECT `pid` from `aaa` where `id` =" . $id;

$res = $this->db->getone($sql); //获取字段值，如15

if ($res) {

if($n){

$ids .= "," . $res;

}else{

$ids = $res;

}

$n++;

$ids .= $this->test($res,$n);

}

return $ids;

}

/*

* 获取所有下级

* @param $id String 待查找的id

* @return String | NULL 失败返回null

*/

public function getSub($id)

{

$isComma = strstr($id,',');

if($isComma){

$sql = "SELECT `id` from `aaa` where `pid` in (" . $id.")";

}else{

$sql = "SELECT `id` from `aaa` where `pid`=" . $id;

}

$res = $this->sql($sql); //获取二维数组，如Array ( [0] => Array ( [id] => 12 ) [1] => Array ( [id] => 13 ) )

if ($res) {

$id = '';

foreach ($res as $k=>$v) {

if($v['id']>0){

if($k == 0){

$id = $v['id'];

}else{

$id .= ','.$v['id'];

}

}

}

if($isComma){

$ids .= "," . $id;

}else{

$ids .= $id;

}

$ids .= $this->test2($id);

}

return $ids;

}

数据库

-- ----------------------------

-- Table structure for aaa

-- ----------------------------

DROP TABLE IF EXISTS `aaa`;

CREATE TABLE `aaa` (

`id` int(11) NOT NULL,

`nodename` varchar(20) DEFAULT NULL,

`pid` int(11) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------

-- Records of aaa

-- ----------------------------

INSERT INTO `aaa` VALUES ('1', 'A', '0');

INSERT INTO `aaa` VALUES ('2', 'B', '1');

INSERT INTO `aaa` VALUES ('3', 'C', '1');

INSERT INTO `aaa` VALUES ('4', 'D', '2');

INSERT INTO `aaa` VALUES ('5', 'E', '2');

INSERT INTO `aaa` VALUES ('6', 'F', '3');

INSERT INTO `aaa` VALUES ('7', 'G', '6');

INSERT INTO `aaa` VALUES ('8', 'H', '0');

INSERT INTO `aaa` VALUES ('9', 'I', '8');

INSERT INTO `aaa` VALUES ('10', 'J', '8');

INSERT INTO `aaa` VALUES ('11', 'K', '8');

INSERT INTO `aaa` VALUES ('12', 'L', '9');

INSERT INTO `aaa` VALUES ('13', 'M', '9');

INSERT INTO `aaa` VALUES ('14', 'N', '12');

INSERT INTO `aaa` VALUES ('15', 'O', '12');

INSERT INTO `aaa` VALUES ('16', 'P', '15');

INSERT INTO `aaa` VALUES ('17', 'Q', '0');

mysql with 查询上下级