001002003004005006007008009010011012013014015016017018019020021022023024025026027028029030031032033034035036037038039040041042043044045046047048049050051052053054055056057058059060061062063064065066067068069070071072073074075076077078079080081082083084085086087088
<?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]; } ?>