Uzupełnienie kodu, czyli ostateczny wygląd klasy

Nadszedł czas na wstawienie kodu i zapytań do schematu klasy, który napisaliśmy wczesniej.

class trees{

var $maxNest = 5; //liczone od zera()

function trees(){

$this->db =& new db;

return true;
}

function getAll( $cluster , $depth = null ){

$depth = (is_null($depth))?$this->maxNest:$depth;

return $this->db->getArray("
SELECT *, INSTR(level,'0')-1 as depth
FROM groups
WHERE cluster = ".$cluster." AND
INSTR(level,'0')-1 <= ".$depth."
ORDER BY level
");

}

function getPart( $parent_id, $depth = null ){

$depth = (is_null($depth))?$this->maxNest:$depth;

$r = $this->db->getRow("
SELECT cluster,SUBSTRING(level, 1, INSTR(level,'0')-1) as cutLevel, INSTR(level,'0')-1 as depth
FROM groups
WHERE id=".$parent_id
);

if(!$r){return false;}

return $this->db->getArray("
SELECT *, INSTR(level,'0')-1 as depth, INSTR(level,'0')-1 - ".$r['depth']." as relativeDepth
FROM groups
WHERE cluster = ".$r['cluster']." AND
level like '".$r['cutLevel']."%' AND
INSTR(level,'0')-1 <= (".$depth."+".$r['depth'].")
ORDER BY level
");

}

function newGroup( $name ){

$levelNum = $this->maxNest+2;

return $this->db->execute("
INSERT INTO groups ( cluster, name, level )
SELECT MAX(cluster)+1,'".$name."', RPAD('0',".$levelNum.",'0') FROM groups
");

}

function newChild( $name, $parentId ){

$levelNum = $this->maxNest+2;

$parent = $this->db->getRow("
SELECT cluster, SUBSTRING(level, 1, INSTR(level,'0')-1) as cutLevel, INSTR(level,'0') - 1 as depth FROM groups
WHERE id=".$parentId
);

if(!$parent){return false;}

return $this->db->execute("
INSERT INTO groups (cluster, name, level) select ".$parent['cluster'].", '".$name."',RPAD(SUBSTRING(g.level, 1, ".$parent['depth']." + 1)+1 ,".$levelNum.",'0')
FROM groups AS g
LEFT JOIN groups AS p ON CONCAT(g.cluster,'|',RPAD(SUBSTRING(g.level, 1, ".$parent['depth']." + 1)+1 ,".$levelNum.",'0')) = CONCAT(p.cluster,'|',p.level)
WHERE g.cluster = ".$parent['cluster']." and g.level like CONCAT( '".$parent['cutLevel']."','%') and ".$parent['depth']." + 1 <= ".$levelNum."-1 and p.level is null limit 1
");
}

function delete( $id ){

$r = $this->db->getRow("
SELECT cluster, SUBSTRING(level, 1, INSTR(level,'0')-1) as cutLevel
FROM groups
WHERE id=".$id
);

if(!$r){return false;}

return $this->db->execute("
DELETE
FROM groups
WHERE level like '".$r['cutLevel']."%' AND
cluster =".$r['cluster']);
}
}

Przykład wykorzystania klasy:

$t =& new trees;

$arr = $t->getPart(47);

foreach($arr as $val){

print str_repeat("____", $val['depth']).' '.$val['id'].' '.$val['name']."<br />";

}