<?php // (2022.9.18, 차재복, Cha Jae Bok, http://www.ktword.co.kr)
// mysql prepare statement 수행 함수
function prepare_sql($dbi, $query, $types, $parms_arr) {
$stmt = mysqli_prepare($dbi, $query);
mysqli_stmt_bind_param($stmt, $types, ...$parms_arr);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
// mysqli_stmt_close($stmt);
return array('stmt'=>$stmt,'result'=>$result);
}
// path2node_v2, depth, child, linked_num 업데이트 함수
function treeNodeCheck($idList, $dbi, $send=true) {
$query = '';
$return = '';
if(is_array($idList)) {
// 다수 노드에서(노드 이동 후 그 자신 및 산하 자식들에 대해), path2node_v2, depth 업데이트
$idListComma = implode(',',$idList);
$length = count($idList);
$query = "update
gubun_tree_v2
set
path2node=concat(';0;;',replace(substring_index(getpath_v2(id),'|',-1),'::',';;'),';'),
path2node_v2=concat('0,',replace(substring_index(getpath_v2(id),'|',-1),'::',',')),
depth=round( ( length(path2node) - length(replace(path2node,';;','')) )/length(';;') )
where id in ({$idListComma})
limit {$length}";
} else {
// 단일 노드에서, path2node_v2, depth, child, linked_num 업데이트
$id = $idList;
$query = "update
gubun_tree_v2 a
left join (select tree_id,count(*) as word_cnt from book_idx where tree_id={$id}) b on a.id=b.tree_id
left join (select parent,count(*) as tree_cnt from gubun_tree_v2 where parent={$id}) c on a.id=c.parent
set
path2node=concat(';0;;',replace(substring_index(getpath_v2(id),'|',-1),'::',';;'),';'),
path2node_v2=concat('0,',replace(substring_index(getpath_v2(id),'|',-1),'::',',')),
depth=round( ( length(path2node) - length(replace(path2node,';;','')) )/length(';;') ),
a.child=(ifnull(b.word_cnt,0) + ifnull(c.tree_cnt,0)),
a.linked_num=ifnull(b.word_cnt,0)
where a.id={$id}
limit 1";
}
$result = mysqli_query($dbi,$query);
if (mysqli_errno($dbi)) {
$err_msg .= mysqli_errno($dbi)." : ".mysqli_error($dbi)."\n";
$return = array('err_msg'=>$err_msg);
} else {
$affeted_rows = mysqli_affected_rows($dbi);
$return = array('result' => 'response : no error, affected rows : '.$affeted_rows,'err_msg'=>$err_msg);
}
// 송출
if($send) echo json_encode($return, JSON_UNESCAPED_UNICODE);
else return $return;
}
// 부모 노드의 path2node 등 추출
function getNodeDetail($id,$dbi) {
$query = "select parent,sub_seq,path2node_v2,child,linked_num,name,yoyak from gubun_tree_v2 where id=? limit 1";
$types = "i";
$parms_arr = array($id);
$resultArr = prepare_sql($dbi, $query, $types, $parms_arr);
$row = mysqli_fetch_assoc($resultArr['result']);
return array('parent'=>$row['parent'],'seq'=>$row['sub_seq'],'path2node'=>$row['path2node_v2']
,'child'=>$row['child'],'nodeCount'=>($row['child']-$row['linked_num'])
,'name'=>$row['name'],'yoyak'=>$row['yoyak']);
}
// 테이블 내 빈 id 찾아 리턴
function findEmptyId ($table_name, $dbi) {
$query = "select a.id + 1 as available
from {$table_name} a LEFT JOIN {$table_name} b ON b.id = (a.id + 1)
where b.id IS NULL
order by a.id
limit 0,1";
$result = mysqli_query($dbi, $query);
if (mysqli_errno($dbi)) { $err_msg .= mysqli_errno($dbi)." : ".mysqli_error($dbi)."\n"; }
$matched = mysqli_fetch_assoc($result);
return $matched[available];
}
?>