Public Member Functions | |
CDBTree (&$DB, $tableName, $itemId, $fieldNames=array()) | |
getElementInfo ($ID) | |
getNodeInfo ($ID) | |
clear ($data=array()) | |
update ($ID, $data) | |
insert ($ID, $data) | |
insertNear ($ID, $data) | |
moveAll ($ID, $newParentId) | |
delete ($ID) | |
deleteAll ($ID) | |
enumChildrenAll ($ID) | |
enumChildren ($ID, $start_level=1, $end_level=1) | |
enumPath ($ID, $showRoot=false) | |
getParent ($ID, $level=1) | |
sqlReset () | |
sqlSetReset ($resetMode) | |
sqlParams ($param='') | |
sqlFields ($param='') | |
sqlSelect ($param='') | |
sqlTables ($param='') | |
sqlFrom ($param='') | |
sqlWhere ($param='') | |
sqlGroupBy ($param='') | |
sqlHaving ($param='') | |
sqlOrderBy ($param='') | |
sqlLimit ($param='') | |
sqlComposeSelect ($arSql) | |
Public Attributes | |
$db | |
$table | |
$id | |
$left = 'cat_left' | |
$right = 'cat_right' | |
$level = 'cat_level' | |
$qryParams = '' | |
$qryFields = '' | |
$qryTables = '' | |
$qryWhere = '' | |
$qryGroupBy = '' | |
$qryHaving = '' | |
$qryOrderBy = '' | |
$qryLimit = '' | |
$sqlNeedReset = true | |
$sql |
Definition at line 77 of file test.php.
Definition at line 111 of file test.php.
00111 { 00112 if(empty($tableName)) trigger_error("phpDbTree: Unknown table", E_USER_ERROR); 00113 if(empty($itemId)) trigger_error("phpDbTree: Unknown ID column", E_USER_ERROR); 00114 $this->db = $DB; 00115 $this->table = $tableName; 00116 $this->id = $itemId; 00117 if(is_array($fieldNames) && sizeof($fieldNames)) 00118 foreach($fieldNames as $k => $v) 00119 $this->$k = $v; 00120 }
clear | ( | $ | data = array() |
) |
Definition at line 136 of file test.php.
00136 { 00137 // clearing table 00138 if((!$this->db->query('TRUNCATE '.$this->table)) && (!$this->db->query('DELETE FROM '.$this->table))) trigger_error("phpDbTree error: ".$this->db->error(), E_USER_ERROR); 00139 00140 // preparing data to be inserted 00141 if(sizeof($data)) { 00142 $fld_names = implode(',', array_keys($data)).','; 00143 if(sizeof($data)) $fld_values = '\''.implode('\',\'', array_values($data)).'\','; 00144 } 00145 $fld_names .= $this->left.','.$this->right.','.$this->level; 00146 $fld_values .= '1,2,0'; 00147 00148 // inserting new record 00149 $this->sql = 'INSERT INTO '.$this->table.'('.$fld_names.') VALUES('.$fld_values.')'; 00150 if(!($this->db->query($this->sql))) trigger_error("phpDbTree error: ".$this->db->error(), E_USER_ERROR); 00151 00152 return $this->db->insert_id(); 00153 }
delete | ( | $ | ID | ) |
Definition at line 286 of file test.php.
00286 { 00287 if(!(list($leftId, $rightId, $level) = $this->getNodeInfo($ID))) trigger_error("phpDbTree error: ".$this->db->error(), E_USER_ERROR); 00288 00289 // Deleting record 00290 $this->sql = 'DELETE FROM '.$this->table.' WHERE '.$this->id.'=\''.$ID.'\''; 00291 if(!$this->db->query($this->sql)) trigger_error("phpDbTree error: ".$this->db->error(), E_USER_ERROR); 00292 00293 // Clearing blank spaces in a tree 00294 $this->sql = 'UPDATE '.$this->table.' SET ' 00295 . $this->left.'=IF('.$this->left.' BETWEEN '.$leftId.' AND '.$rightId.','.$this->left.'-1,'.$this->left.'),' 00296 . $this->right.'=IF('.$this->right.' BETWEEN '.$leftId.' AND '.$rightId.','.$this->right.'-1,'.$this->right.'),' 00297 . $this->level.'=IF('.$this->left.' BETWEEN '.$leftId.' AND '.$rightId.','.$this->level.'-1,'.$this->level.'),' 00298 . $this->left.'=IF('.$this->left.'>'.$rightId.','.$this->left.'-2,'.$this->left.'),' 00299 . $this->right.'=IF('.$this->right.'>'.$rightId.','.$this->right.'-2,'.$this->right.') ' 00300 . 'WHERE '.$this->right.'>'.$leftId 00301 ; 00302 if(!$this->db->query($this->sql)) trigger_error("phpDbTree error: ".$this->db->error(), E_USER_ERROR); 00303 00304 return true; 00305 }
deleteAll | ( | $ | ID | ) |
Definition at line 311 of file test.php.
00311 { 00312 if(!(list($leftId, $rightId, $level) = $this->getNodeInfo($ID))) trigger_error("phpDbTree error: ".$this->db->error(), E_USER_ERROR); 00313 00314 // Deleteing record(s) 00315 $this->sql = 'DELETE FROM '.$this->table.' WHERE '.$this->left.' BETWEEN '.$leftId.' AND '.$rightId; 00316 if(!$this->db->query($this->sql)) trigger_error("phpDbTree error: ".$this->db->error(), E_USER_ERROR); 00317 00318 // Clearing blank spaces in a tree 00319 $deltaId = ($rightId - $leftId)+1; 00320 $this->sql = 'UPDATE '.$this->table.' SET ' 00321 . $this->left.'=IF('.$this->left.'>'.$leftId.','.$this->left.'-'.$deltaId.','.$this->left.'),' 00322 . $this->right.'=IF('.$this->right.'>'.$leftId.','.$this->right.'-'.$deltaId.','.$this->right.') ' 00323 . 'WHERE '.$this->right.'>'.$rightId 00324 ; 00325 if(!$this->db->query($this->sql)) trigger_error("phpDbTree error: ".$this->db->error(), E_USER_ERROR); 00326 00327 return true; 00328 }
enumChildren | ( | $ | ID, | |
$ | start_level = 1 , |
|||
$ | end_level = 1 | |||
) |
Definition at line 341 of file test.php.
00341 { 00342 if($start_level < 0) trigger_error("phpDbTree error: ".$this->db->error(), E_USER_ERROR); 00343 00344 // We could use sprintf() here, but it'd be too slow 00345 $whereSql1 = ' AND '.$this->table.'.'.$this->level; 00346 $whereSql2 = '_'.$this->table.'.'.$this->level.'+'; 00347 00348 if(!$end_level) $whereSql = $whereSql1.'>='.$whereSql2.(int)$start_level; 00349 else { 00350 $whereSql = ($end_level <= $start_level) 00351 ? $whereSql1.'='.$whereSql2.(int)$start_level 00352 : ' AND '.$this->table.'.'.$this->level.' BETWEEN _'.$this->table.'.'.$this->level.'+'.(int)$start_level 00353 .' AND _'.$this->table.'.'.$this->level.'+'.(int)$end_level; 00354 } 00355 00356 $this->sql = $this->sqlComposeSelect(array( 00357 '', // Params 00358 '', // Fields 00359 $this->table.' _'.$this->table.', '.$this->table, // Tables 00360 '_'.$this->table.'.'.$this->id.'=\''.$ID.'\'' 00361 .' AND '.$this->table.'.'.$this->left.' BETWEEN _'.$this->table.'.'.$this->left.' AND _'.$this->table.'.'.$this->right 00362 .$whereSql 00363 )); 00364 00365 return $this->db->query($this->sql); 00366 }
enumChildrenAll | ( | $ | ID | ) |
enumPath | ( | $ | ID, | |
$ | showRoot = false | |||
) |
Definition at line 373 of file test.php.
00373 { 00374 $this->sql = $this->sqlComposeSelect(array( 00375 '', // Params 00376 '', // Fields 00377 $this->table.' _'.$this->table.', '.$this->table, // Tables 00378 '_'.$this->table.'.'.$this->id.'=\''.$ID.'\'' 00379 .' AND _'.$this->table.'.'.$this->left.' BETWEEN '.$this->table.'.'.$this->left.' AND '.$this->table.'.'.$this->right 00380 .(($showRoot) ? '' : ' AND '.$this->table.'.'.$this->level.'>0'), // Where 00381 '', // GroupBy 00382 '', // Having 00383 $this->table.'.'.$this->left // OrderBy 00384 )); 00385 00386 return $this->db->query($this->sql); 00387 }
getElementInfo | ( | $ | ID | ) |
getNodeInfo | ( | $ | ID | ) |
Definition at line 126 of file test.php.
00126 { 00127 $this->sql = 'SELECT '.$this->left.','.$this->right.','.$this->level.' FROM '.$this->table.' WHERE '.$this->id.'=\''.$ID.'\''; 00128 if(($query=$this->db->query($this->sql)) && ($this->db->num_rows($query) == 1) && ($Data = $this->db->fetch_array($query))) 00129 return array((int)$Data[$this->left], (int)$Data[$this->right], (int)$Data[$this->level]); 00130 else trigger_error("phpDbTree error: ".$this->db->error(), E_USER_ERROR); 00131 }
getParent | ( | $ | ID, | |
$ | level = 1 | |||
) |
Definition at line 394 of file test.php.
00394 { 00395 if($level < 1) trigger_error("phpDbTree error: ".$this->db->error(), E_USER_ERROR); 00396 00397 $this->sql = $this->sqlComposeSelect(array( 00398 '', // Params 00399 '', // Fields 00400 $this->table.' _'.$this->table.', '.$this->table, // Tables 00401 '_'.$this->table.'.'.$this->id.'=\''.$ID.'\'' 00402 .' AND _'.$this->table.'.'.$this->left.' BETWEEN '.$this->table.'.'.$this->left.' AND '.$this->table.'.'.$this->right 00403 .' AND '.$this->table.'.'.$this->level.'=_'.$this->table.'.'.$this->level.'-'.(int)$level // Where 00404 )); 00405 00406 return $this->db->query($this->sql); 00407 }
insert | ( | $ | ID, | |
$ | data | |||
) |
Definition at line 170 of file test.php.
00170 { 00171 if(!(list($leftId, $rightId, $level) = $this->getNodeInfo($ID))) trigger_error("phpDbTree error: ".$this->db->error(), E_USER_ERROR); 00172 00173 // preparing data to be inserted 00174 if(sizeof($data)) { 00175 $fld_names = implode(',', array_keys($data)).','; 00176 $fld_values = '\''.implode('\',\'', array_values($data)).'\','; 00177 } 00178 $fld_names .= $this->left.','.$this->right.','.$this->level; 00179 $fld_values .= ($rightId).','.($rightId+1).','.($level+1); 00180 00181 // creating a place for the record being inserted 00182 if($ID) { 00183 $this->sql = 'UPDATE '.$this->table.' SET ' 00184 . $this->left.'=IF('.$this->left.'>'.$rightId.','.$this->left.'+2,'.$this->left.'),' 00185 . $this->right.'=IF('.$this->right.'>='.$rightId.','.$this->right.'+2,'.$this->right.')' 00186 . 'WHERE '.$this->right.'>='.$rightId; 00187 if(!($this->db->query($this->sql))) trigger_error("phpDbTree error: ".$this->db->error(), E_USER_ERROR); 00188 } 00189 00190 // inserting new record 00191 $this->sql = 'INSERT INTO '.$this->table.'('.$fld_names.') VALUES('.$fld_values.')'; 00192 if(!($this->db->query($this->sql))) trigger_error("phpDbTree error: ".$this->db->error(), E_USER_ERROR); 00193 00194 return $this->db->insert_id(); 00195 }
insertNear | ( | $ | ID, | |
$ | data | |||
) |
Definition at line 203 of file test.php.
00203 { 00204 if(!(list($leftId, $rightId, $level) = $this->getNodeInfo($ID))) 00205 trigger_error("phpDbTree error: ".$this->db->error(), E_USER_ERROR); 00206 00207 // preparing data to be inserted 00208 if(sizeof($data)) { 00209 $fld_names = implode(',', array_keys($data)).','; 00210 $fld_values = '\''.implode('\',\'', array_values($data)).'\','; 00211 } 00212 $fld_names .= $this->left.','.$this->right.','.$this->level; 00213 $fld_values .= ($rightId+1).','.($rightId+2).','.($level); 00214 00215 // creating a place for the record being inserted 00216 if($ID) { 00217 $this->sql = 'UPDATE '.$this->table.' SET ' 00218 .$this->left.'=IF('.$this->left.'>'.$rightId.','.$this->left.'+2,'.$this->left.'),' 00219 .$this->right.'=IF('.$this->right.'>'.$rightId.','.$this->right.'+2,'.$this->right.')' 00220 . 'WHERE '.$this->right.'>'.$rightId; 00221 if(!($this->db->query($this->sql))) trigger_error("phpDbTree error:".$this->db->error(), E_USER_ERROR); 00222 } 00223 00224 // inserting new record 00225 $this->sql = 'INSERT INTO '.$this->table.'('.$fld_names.') VALUES('.$fld_values.')'; 00226 if(!($this->db->query($this->sql))) trigger_error("phpDbTree error:".$this->db->error(), E_USER_ERROR); 00227 00228 return $this->db->insert_id(); 00229 }
moveAll | ( | $ | ID, | |
$ | newParentId | |||
) |
Definition at line 237 of file test.php.
00237 { 00238 if(!(list($leftId, $rightId, $level) = $this->getNodeInfo($ID))) trigger_error("phpDbTree error: ".$this->db->error(), E_USER_ERROR); 00239 if(!(list($leftIdP, $rightIdP, $levelP) = $this->getNodeInfo($newParentId))) trigger_error("phpDbTree error: ".$this->db->error(), E_USER_ERROR); 00240 if($ID == $newParentId || $leftId == $leftIdP || ($leftIdP >= $leftId && $leftIdP <= $rightId)) return false; 00241 00242 // whether it is being moved upwards along the path 00243 if ($leftIdP < $leftId && $rightIdP > $rightId && $levelP < $level - 1 ) { 00244 $this->sql = 'UPDATE '.$this->table.' SET ' 00245 . $this->level.'=IF('.$this->left.' BETWEEN '.$leftId.' AND '.$rightId.', '.$this->level.sprintf('%+d', -($level-1)+$levelP).', '.$this->level.'), ' 00246 . $this->right.'=IF('.$this->right.' BETWEEN '.($rightId+1).' AND '.($rightIdP-1).', '.$this->right.'-'.($rightId-$leftId+1).', ' 00247 .'IF('.$this->left.' BETWEEN '.($leftId).' AND '.($rightId).', '.$this->right.'+'.((($rightIdP-$rightId-$level+$levelP)/2)*2 + $level - $levelP - 1).', '.$this->right.')), ' 00248 . $this->left.'=IF('.$this->left.' BETWEEN '.($rightId+1).' AND '.($rightIdP-1).', '.$this->left.'-'.($rightId-$leftId+1).', ' 00249 .'IF('.$this->left.' BETWEEN '.$leftId.' AND '.($rightId).', '.$this->left.'+'.((($rightIdP-$rightId-$level+$levelP)/2)*2 + $level - $levelP - 1).', '.$this->left. ')) ' 00250 . 'WHERE '.$this->left.' BETWEEN '.($leftIdP+1).' AND '.($rightIdP-1) 00251 ; 00252 } elseif($leftIdP < $leftId) { 00253 $this->sql = 'UPDATE '.$this->table.' SET ' 00254 . $this->level.'=IF('.$this->left.' BETWEEN '.$leftId.' AND '.$rightId.', '.$this->level.sprintf('%+d', -($level-1)+$levelP).', '.$this->level.'), ' 00255 . $this->left.'=IF('.$this->left.' BETWEEN '.$rightIdP.' AND '.($leftId-1).', '.$this->left.'+'.($rightId-$leftId+1).', ' 00256 . 'IF('.$this->left.' BETWEEN '.$leftId.' AND '.$rightId.', '.$this->left.'-'.($leftId-$rightIdP).', '.$this->left.') ' 00257 . '), ' 00258 . $this->right.'=IF('.$this->right.' BETWEEN '.$rightIdP.' AND '.$leftId.', '.$this->right.'+'.($rightId-$leftId+1).', ' 00259 . 'IF('.$this->right.' BETWEEN '.$leftId.' AND '.$rightId.', '.$this->right.'-'.($leftId-$rightIdP).', '.$this->right.') ' 00260 . ') ' 00261 . 'WHERE '.$this->left.' BETWEEN '.$leftIdP.' AND '.$rightId 00262 // !!! added this line (Maxim Matyukhin) 00263 .' OR '.$this->right.' BETWEEN '.$leftIdP.' AND '.$rightId 00264 ; 00265 } else { 00266 $this->sql = 'UPDATE '.$this->table.' SET ' 00267 . $this->level.'=IF('.$this->left.' BETWEEN '.$leftId.' AND '.$rightId.', '.$this->level.sprintf('%+d', -($level-1)+$levelP).', '.$this->level.'), ' 00268 . $this->left.'=IF('.$this->left.' BETWEEN '.$rightId.' AND '.$rightIdP.', '.$this->left.'-'.($rightId-$leftId+1).', ' 00269 . 'IF('.$this->left.' BETWEEN '.$leftId.' AND '.$rightId.', '.$this->left.'+'.($rightIdP-1-$rightId).', '.$this->left.')' 00270 . '), ' 00271 . $this->right.'=IF('.$this->right.' BETWEEN '.($rightId+1).' AND '.($rightIdP-1).', '.$this->right.'-'.($rightId-$leftId+1).', ' 00272 . 'IF('.$this->right.' BETWEEN '.$leftId.' AND '.$rightId.', '.$this->right.'+'.($rightIdP-1-$rightId).', '.$this->right.') ' 00273 . ') ' 00274 . 'WHERE '.$this->left.' BETWEEN '.$leftId.' AND '.$rightIdP 00275 // !!! added this line (Maxim Matyukhin) 00276 . ' OR '.$this->right.' BETWEEN '.$leftId.' AND '.$rightIdP 00277 ; 00278 } 00279 return $this->db->query($this->sql) or trigger_error("phpDbTree error: ".$this->db->error(), E_USER_ERROR); 00280 }
sqlComposeSelect | ( | $ | arSql | ) |
Definition at line 433 of file test.php.
00433 { 00434 $joinTypes = array('join'=>1, 'cross'=>1, 'inner'=>1, 'straight'=>1, 'left'=>1, 'natural'=>1, 'right'=>1); 00435 00436 $this->sql = 'SELECT '.$arSql[0].' '; 00437 if(!empty($this->qryParams)) $this->sql .= $this->sqlParams.' '; 00438 00439 if(empty($arSql[1]) && empty($this->qryFields)) $this->sql .= $this->table.'.'.$this->id; 00440 else { 00441 if(!empty($arSql[1])) $this->sql .= $arSql[1]; 00442 if(!empty($this->qryFields)) $this->sql .= ((empty($arSql[1])) ? '' : ',') . $this->qryFields; 00443 } 00444 $this->sql .= ' FROM '; 00445 $isJoin = ($tblAr=explode(' ',trim($this->qryTables))) && ($joinTypes[strtolower($tblAr[0])]); 00446 if(empty($arSql[2]) && empty($this->qryTables)) $this->sql .= $this->table; 00447 else { 00448 if(!empty($arSql[2])) $this->sql .= $arSql[2]; 00449 if(!empty($this->qryTables)) { 00450 if(!empty($arSql[2])) $this->sql .= (($isJoin)?' ':','); 00451 elseif($isJoin) $this->sql .= $this->table.' '; 00452 $this->sql .= $this->qryTables; 00453 } 00454 } 00455 if((!empty($arSql[3])) || (!empty($this->qryWhere))) { 00456 $this->sql .= ' WHERE ' . $arSql[3] . ' '; 00457 if(!empty($this->qryWhere)) $this->sql .= (empty($arSql[3])) ? $this->qryWhere : 'AND('.$this->qryWhere.')'; 00458 } 00459 if((!empty($arSql[4])) || (!empty($this->qryGroupBy))) { 00460 $this->sql .= ' GROUP BY ' . $arSql[4] . ' '; 00461 if(!empty($this->qryGroupBy)) $this->sql .= (empty($arSql[4])) ? $this->qryGroupBy : ','.$this->qryGroupBy; 00462 } 00463 if((!empty($arSql[5])) || (!empty($this->qryHaving))) { 00464 $this->sql .= ' HAVING ' . $arSql[5] . ' '; 00465 if(!empty($this->qryHaving)) $this->sql .= (empty($arSql[5])) ? $this->qryHaving : 'AND('.$this->qryHaving.')'; 00466 } 00467 if((!empty($arSql[6])) || (!empty($this->qryOrderBy))) { 00468 $this->sql .= ' ORDER BY ' . $arSql[6] . ' '; 00469 if(!empty($this->qryOrderBy)) $this->sql .= (empty($arSql[6])) ? $this->qryOrderBy : ','.$this->qryOrderBy; 00470 } 00471 if(!empty($arSql[7])) $this->sql .= ' LIMIT '.$arSql[7]; 00472 elseif(!empty($this->qryLimit)) $this->sql .= ' LIMIT '.$this->qryLimit; 00473 00474 if($this->sqlNeedReset) $this->sqlReset(); 00475 00476 return $this->sql; 00477 }
sqlFields | ( | $ | param = '' |
) |
sqlFrom | ( | $ | param = '' |
) |
sqlGroupBy | ( | $ | param = '' |
) |
sqlHaving | ( | $ | param = '' |
) |
sqlLimit | ( | $ | param = '' |
) |
sqlOrderBy | ( | $ | param = '' |
) |
sqlParams | ( | $ | param = '' |
) |
sqlReset | ( | ) |
sqlSelect | ( | $ | param = '' |
) |
sqlSetReset | ( | $ | resetMode | ) |
sqlTables | ( | $ | param = '' |
) |
sqlWhere | ( | $ | param = '' |
) |
update | ( | $ | ID, | |
$ | data | |||
) |