<?php // (2022.5.7, 차재복, Cha Jae Bok, http://www.ktword.co.kr)
// function f_query($m_search, $limit='', $m_edit_level='') {
function f_query($m_search, $arr_vars='') {
if ( !empty($arr_vars) ) {
$limit = ( empty($arr_vars['limit']) ? '' : $arr_vars['limit']);
$m_edit_level = ( empty($arr_vars['m_edit_level']) ? '' : $arr_vars['m_edit_level']);
$is_1st_char = ( empty($arr_vars['is_1st_char']) ? 0 : 1);
}
# 검색어 조정 => (Validation 필요)
// $number = preg_replace("/[^0-9]*/s", "", $m_search); // 숫자 빼고 문자열 만 추출
// $m_search = str_replace($number,' '.$number.' ',$m_search); // 숫자 문자열 분리
if (strtolower($m_search) !== 'c++')
$m_search=urldecode($m_search); // url decode
$m_search=substr(trim($m_search),0,55); // 좌 우 공백 제거 및 글자수 제한
$m_search=str_replace('-',' ',$m_search); // 대쉬 제거
$m_search=str_replace(' ',' ',$m_search); // 연속 공백 제거
$m_search=str_replace('\\','',$m_search); // \ 공백 제거
# 한글 (1),영어 (0) 구분
// $m_lang = ( preg_match("/[\xA1-\xFE][\xA1-\xFE]/", $m_search) ? 1 : 0 );
$m_lang = ( preg_match("/[\x{ac00}-\x{d7a3}][\x{ac00}-\x{d7a3}]/u", $m_search) ? 1 : 0 );
# 첫 글자 검색인 경우
if ($is_1st_char == 1) {
$query = "
SELECT a.word,a.wordtype,b.yako,b.full_name,b.korean,b.extra_keyword,b.synopsis,a.no,b.abbr
FROM dict_word_list a LEFT JOIN cjb_dict b ON a.no=b.no
WHERE a.word like '{$m_search}%'
GROUP BY a.no
ORDER BY a.word DESC
$limit
";
return $query;
}
/*
# 검색어 길이 체킹
if ( mb_strlen($m_search,'euc-kr') < 2 and $m_lang == 0 ) { // and $is_1st_char != 1
echo "검색어가 2자 미만 !!!";
exit;
}
*/
# 검색어 요소별 정돈
$m_search=str_replace('-',' ',$m_search); // $m_search2 대쉬 '-' 제거
$m_pieces=explode(" ",$m_search_org,3); // 빈 칸 구분 요소별 분리
# 한글 의미어 분리 // (개선 테스트 중 ...)
if ($m_lang == 1) {
$kr_main_arr = array('결합','모드','분산','상관','추정');
foreach ($kr_main_arr as $key => $value) {
if (strpos($m_search,$value)!==FALSE) {
$kr_deli_search = str_replace($value,' '.$value.' ',$m_search);
$kr_deli_search = str_replace(' ',' ',$kr_deli_search);
// echo $kr_deli_search; // 테스트 결과 보이기
break;
}
}
}
# matche되는 것 만 쿼리 (더욱더 query optimization이 가능할 듯)
$match_query = "
SELECT no,word,wordtype
,(
# 영어든 한글이든 똑 같으면 또는 [] 있을 때도 = 210점
SUM(IF(UPPER(word)=UPPER('$m_search') || UPPER(TRIM(SUBSTRING_INDEX(word,'[',1)))=UPPER('$m_search'),210,0))
# 빈칸 없앤 한글 검색어와 같으면 = 200점
+ SUM(IF($m_lang=1 AND REPLACE(word,' ','')='$m_search',200,0))
+ SUM(IF(BINARY word = '$m_search',20,0))
+ SUM(IF(CONCAT(' ','$m_search',' ') LIKE CONCAT('% ',word,' %'),30,0))
+ SUM(IF(CONCAT(' ',word,' ') LIKE CONCAT('% ','$m_search',' %'),30,0))
+ SUM(IF(REPLACE(word,'-',' ') LIKE CONCAT('%','$m_search','%'),6,0))
+ SUM(IF(CHAR_LENGTH(word)>=3,1,0))
) AS weight
FROM dict_word_list
WHERE
# (word를 search로 검색) 한글 (1) 이면, => 빈칸 없이 검색
IF($m_lang=1,REPLACE(word,' ',''),word) LIKE IF($m_lang=1,CONCAT('%','$m_search','%'),NULL)
# (word를 search로 검색) 한글 (1) 이면, => 양쪽 모두 붙여서도 검색, 단, 맨앞 일치해야,
|| CONCAT(' ',IF($m_lang=1,REPLACE(word,' ',''),word),' ') LIKE CONCAT('% ',REPLACE('$m_search',' ',''),'%')
# (search를 word로 검색) 영어 이면, => 단어 마다 정확 일치 (단, 복수형 s 포함)
|| CONCAT(' ','$m_search',' ') LIKE CONCAT('% ',word,'s %')
# (search를 word로 검색) 한글어 검색이면 => 단어 붙여서도 일치
|| CONCAT(' ','$m_search',' ') LIKE CONCAT(IF($m_lang=1,'%','% '),IF($m_lang=1,REPLACE(word,' ',''),word),IF($m_lang=1,'%',' %'))
# (search에서 word를 검색)
|| IF(CHAR_LENGTH(word)>2,CONCAT(' ','$m_search',' ') LIKE CONCAT('%',word,'%'),0)
# (search에서 word를 검색) => [ ] 검출 => 한글이면,
|| ( CONCAT(' ','$kr_deli_search',' ') LIKE CONCAT( '% ', IF(LOCATE('[',word)=0, word, TRIM(SUBSTRING(word,1,LOCATE('[',word)-1))), ' %' ) )
# - 영어 => 단어 마다 정확 일치, 한글 => 붙여서도 일치 (단, 좌 끝 OR 우 끝 일치)
|| ( CONCAT(' ','$m_search',' ') LIKE CONCAT(IF($m_lang=1,'% ','% '), IF(LOCATE('[',word)=0, word, TRIM(SUBSTRING(word,1,LOCATE('[',word)-1))), IF($m_lang=1,'%',' %') ) )
OR
( CONCAT(' ','$m_search',' ') LIKE CONCAT(IF($m_lang=1,'%','% '),IF(LOCATE('[',word)=0,word,TRIM(SUBSTRING(word,1,LOCATE('[',word)-1))),IF($m_lang=1,' %',' %') ) )
# 대쉬 => 공백 변환 후 검색
|| REPLACE(word,'-',' ') LIKE CONCAT('%','$m_search','%')
# word 빈 칸 구분, ('-'=>' ')은 포함 ('/'=>' ')은 제외
|| ( CASE (LENGTH(REPLACE(word,'-',' '))-LENGTH(REPLACE(REPLACE(word,'-',' '),' ','')))/LENGTH(' ')
WHEN 1 THEN ('$m_search' LIKE CONCAT('%',SUBSTRING_INDEX(REPLACE(word,'-',' '),' ',1),'%')) AND ('$m_search' LIKE CONCAT('%',SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(word,'-',' '),' ',2),' ',-1),'%'))
WHEN 2 THEN ('$m_search' LIKE CONCAT('%',SUBSTRING_INDEX(REPLACE(word,'-',' '),' ',1),'%')) AND ('$m_search' LIKE CONCAT('%',SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(word,'-',' '),' ',2),' ',-1),'%')) AND ('$m_search' LIKE CONCAT('%',SUBSTRING_INDEX(REPLACE(word,'-',' '),' ',-1),'%'))
END
)
# m_search 빈 칸 구분, ('-'=>' ')은 포함
|| ( CASE (LENGTH(REPLACE('$m_search','-',' '))-LENGTH(REPLACE(REPLACE('$m_search','-',' '),' ','')))/LENGTH(' ')
WHEN 1 THEN (word LIKE CONCAT('%',SUBSTRING_INDEX(REPLACE('$m_search','-',' '),' ',1),'%')) AND (word LIKE CONCAT('%',SUBSTRING_INDEX(REPLACE('$m_search','-',' '),' ',-1),'%'))
WHEN 2 THEN (word LIKE CONCAT('%',SUBSTRING_INDEX(REPLACE('$m_search','-',' '),' ',1),'%')) AND (word LIKE CONCAT('%',SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE('$m_search','-',' '),' ',2),' ',-1),'%')) AND (word LIKE CONCAT('%',SUBSTRING_INDEX(REPLACE('$m_search','-',' '),' ',-1),'%'))
END
)
# 3자 이상 약어 word
|| IF(CHAR_LENGTH(word)>=3 AND wordtype='y','$m_search' LIKE CONCAT('%',word,'%'),0)
# 1 단어(빈 칸 없음),길이 차이 3자 미만
|| IF(ABS(CHAR_LENGTH(word)-CHAR_LENGTH('$m_search'))<3 AND NOT ('$m_search' LIKE '% %' AND NOT wordtype='y'), '$m_search' LIKE CONCAT('%',word,'%'), 0)
GROUP BY no
ORDER BY weight DESC
";
# serach 후의 결과셋 만을 join 시켜 부하를 줄여줌 (더욱더 query optimization이 가능할 듯)
$query_new = "
SELECT a.no,a.word,a.wordtype,a.weight,
b.yako,b.full_name,b.korean,b.extra_keyword,b.synopsis,b.abbr
FROM
(
# match 쿼리 문
{$match_query}
/*
SELECT no,word,wordtype
,(
# 영어든 한글이든 똑 같으면 또는 [] 있을 때도 = 210점
SUM(IF(UPPER(word)=UPPER('$m_search') || UPPER(TRIM(SUBSTRING_INDEX(word,'[',1)))=UPPER('$m_search'),210,0))
# 빈칸 없앤 한글 검색어와 같으면 = 200점
+ SUM(IF($m_lang=1 AND REPLACE(word,' ','')='$m_search',200,0))
+ SUM(IF(BINARY word = '$m_search',20,0))
+ SUM(IF(CONCAT(' ','$m_search',' ') LIKE CONCAT('% ',word,' %'),30,0))
+ SUM(IF(CONCAT(' ',word,' ') LIKE CONCAT('% ','$m_search',' %'),30,0))
+ SUM(IF(REPLACE(word,'-',' ') LIKE CONCAT('%','$m_search','%'),6,0))
+ SUM(IF(CHAR_LENGTH(word)>=3,1,0))
) AS weight
FROM dict_word_list
WHERE
# (word를 search로 검색) 한글 (1) 이면, => 빈칸 없이 검색
IF($m_lang=1,REPLACE(word,' ',''),word) LIKE IF($m_lang=1,CONCAT('%','$m_search','%'),NULL)
# (word를 search로 검색) 한글 (1) 이면, => 양쪽 모두 붙여서도 검색, 단, 맨앞 일치해야,
|| CONCAT(' ',IF($m_lang=1,REPLACE(word,' ',''),word),' ') LIKE CONCAT('% ',REPLACE('$m_search',' ',''),'%')
# (search를 word로 검색) 영어 이면, => 단어 마다 정확 일치 (단, 복수형 s 포함)
|| CONCAT(' ','$m_search',' ') LIKE CONCAT('% ',word,'s %')
# (search를 word로 검색) 한글어 검색이면 => 단어 붙여서도 일치
|| CONCAT(' ','$m_search',' ') LIKE CONCAT(IF($m_lang=1,'%','% '),IF($m_lang=1,REPLACE(word,' ',''),word),IF($m_lang=1,'%',' %'))
# (search에서 word를 검색)
|| IF(CHAR_LENGTH(word)>2,CONCAT(' ','$m_search',' ') LIKE CONCAT('%',word,'%'),0)
# (search에서 word를 검색) => [ ] 검출 => 한글이면,
|| ( CONCAT(' ','$kr_deli_search',' ') LIKE CONCAT( '% ', IF(LOCATE('[',word)=0, word, TRIM(SUBSTRING(word,1,LOCATE('[',word)-1))), ' %' ) )
# - 영어 => 단어 마다 정확 일치, 한글 => 붙여서도 일치 (단, 좌 끝 OR 우 끝 일치)
|| ( CONCAT(' ','$m_search',' ') LIKE CONCAT(IF($m_lang=1,'% ','% '), IF(LOCATE('[',word)=0, word, TRIM(SUBSTRING(word,1,LOCATE('[',word)-1))), IF($m_lang=1,'%',' %') ) )
OR
( CONCAT(' ','$m_search',' ') LIKE CONCAT(IF($m_lang=1,'%','% '),IF(LOCATE('[',word)=0,word,TRIM(SUBSTRING(word,1,LOCATE('[',word)-1))),IF($m_lang=1,' %',' %') ) )
# 대쉬 => 공백 변환 후 검색
|| REPLACE(word,'-',' ') LIKE CONCAT('%','$m_search','%')
# word 빈 칸 구분, ('-'=>' ')은 포함 ('/'=>' ')은 제외
|| ( CASE (LENGTH(REPLACE(word,'-',' '))-LENGTH(REPLACE(REPLACE(word,'-',' '),' ','')))/LENGTH(' ')
WHEN 1 THEN ('$m_search' LIKE CONCAT('%',SUBSTRING_INDEX(REPLACE(word,'-',' '),' ',1),'%')) AND ('$m_search' LIKE CONCAT('%',SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(word,'-',' '),' ',2),' ',-1),'%'))
WHEN 2 THEN ('$m_search' LIKE CONCAT('%',SUBSTRING_INDEX(REPLACE(word,'-',' '),' ',1),'%')) AND ('$m_search' LIKE CONCAT('%',SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(word,'-',' '),' ',2),' ',-1),'%')) AND ('$m_search' LIKE CONCAT('%',SUBSTRING_INDEX(REPLACE(word,'-',' '),' ',-1),'%'))
END
)
# m_search 빈 칸 구분, ('-'=>' ')은 포함
|| ( CASE (LENGTH(REPLACE('$m_search','-',' '))-LENGTH(REPLACE(REPLACE('$m_search','-',' '),' ','')))/LENGTH(' ')
WHEN 1 THEN (word LIKE CONCAT('%',SUBSTRING_INDEX(REPLACE('$m_search','-',' '),' ',1),'%')) AND (word LIKE CONCAT('%',SUBSTRING_INDEX(REPLACE('$m_search','-',' '),' ',-1),'%'))
WHEN 2 THEN (word LIKE CONCAT('%',SUBSTRING_INDEX(REPLACE('$m_search','-',' '),' ',1),'%')) AND (word LIKE CONCAT('%',SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE('$m_search','-',' '),' ',2),' ',-1),'%')) AND (word LIKE CONCAT('%',SUBSTRING_INDEX(REPLACE('$m_search','-',' '),' ',-1),'%'))
END
)
# 3자 이상 약어 word
|| IF(CHAR_LENGTH(word)>=3 AND wordtype='y','$m_search' LIKE CONCAT('%',word,'%'),0)
# 1 단어(빈 칸 없음),길이 차이 3자 미만
|| IF(ABS(CHAR_LENGTH(word)-CHAR_LENGTH('$m_search'))<3 AND NOT ('$m_search' LIKE '% %' AND NOT wordtype='y'), '$m_search' LIKE CONCAT('%',word,'%'), 0)
GROUP BY no
ORDER BY weight DESC
*/
) a
LEFT JOIN
cjb_dict b
ON a.no=b.no
$limit
";
// return $query;
if($arr_vars['query_type']=='count') return $match_query;
else return $query_new;
}
?>
<?php
# 알파벳 순 및 우리말 순 보이기
function alphabet_order_show($pre_link)
{
echo "알파벳 검색 ";
for ($i=65; $i <= 90; $i++) // chr(65)=A, ... chr(90)=Z
{
echo $pre_link."m_first_letter=1&m_search=".chr($i).">".chr($i)."</a> ";
}
echo "<br><br>";
echo "우리말 검색 ";
$m_korean="가나다라마바사아자차카타파하";
for ($i=0; $i < mb_strlen($m_korean); $i++)
{
echo
$pre_link."m_first_letter=1&m_search=".mb_substr($m_korean,$i,1,'utf-8').">".mb_substr($m_korean,$i,1,'utf-8')."</a> ";
}
}
// 분류 보이기
function srch_bunryu ($no,$dbi) {
// 분류 검색 쿼리
$query = "select
c.id as parent_id, c.titlename, c.path2node_v2 as parent_path, d.id as child_id, d.name, d.path2node_v2 as child_path
from
(select a.titlename,b.id,b.name,b.path2node_v2 from book_idx a left join gubun_tree_v2 b on a.tree_id=b.id where a.no=$no) c
left join
gubun_tree_v2 d
on find_in_set(d.id,c.path2node_v2)
order by parent_path,child_path";
$result=mysqli_query($dbi,$query);
if (mysqli_errno($dbi)) {echo mysqli_errno($dbi)." : ".mysqli_error($dbi)."\n";}
$line_flag='';
$add_para='';
while ( $matched=mysqli_fetch_assoc($result) ) {
/*
if ($line_flag != $matched[parent_path])
$line_flag = $matched[parent_path];
}
*/
// 줄바꿈 여부
if ($line_flag != $matched[parent_path]) echo ( $line_flag == '' ? "" : "<br>" );
// 말단 노드 이면,
if ( $matched[parent_path] == $matched[child_path] ) $add_para = "&no=".$no;
echo "<a href='../view/view.php?nav=2&id={$matched[child_id]}{$add_para}'>";
echo ( $matched[child_id] == 0 ? 'Top' : $matched[name] );
echo "</a>";
// 말단 노드 아니면,
if ( $matched[parent_path] != $matched[child_path] ) echo ">";
$line_flag = $matched[parent_path];
}
echo "<br>";
}
?>