<?php // (2017.12.30, 차재복, Cha Jae Bok, cjbword@gmailcom)
function f_query($m_search,$limit='',$m_edit_level='') {
# 검색어 조정 => (Validation 필요)
// $number = preg_replace("/[^0-9]*/s", "", $m_search); // 숫자 빼고 문자열 만 추출
// $m_search = str_replace($number,' '.$number.' ',$m_search); // 숫자 문자열 분리
$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); // 연속 공백 제거
# 한글 (1),영어 (0) 구분
$m_lang = ( preg_match("/[\xA1-\xFE][\xA1-\xFE]/", $m_search) ? 1 : 0 );
// echo "<br>".$m_lang."<br>";
# 검색어 길이
if ( mb_strlen($m_search,'euc-kr') < 2 and $m_lang == 0) {
echo "검색어가 2자 미만 !!!";
exit;
}
# [super] 현재 검색어는?
/*
if ($m_edit_level=='super') {
if (!empty($m_search)) {
echo $m_search.' => '.($m_lang==1 ? '한글' : '영문');
echo " (<a href='".($m_mobile==true ? 'search_first.php' : 'abbr_view.php')."?opt=search&m_search=".$m_search."'>기존 검색</a>)";
echo "<hr>";
}
}
*/
# 검색어 요소별 정돈
$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;
}
}
}
$query = "
SELECT a.word,a.wordtype,b.yako,b.full_name,b.korean,b.extra_keyword,b.synopsis,a.no,b.abbr
,(
# 영어든 한글이든 똑 같으면 또는 [] 있을 때도 = 210점
SUM(IF(UPPER(a.word)=UPPER('$m_search') || UPPER(TRIM(SUBSTRING_INDEX(a.word,'[',1)))=UPPER('$m_search'),210,0))
# 빈칸 없앤 한글 검색어와 같으면 = 200점
+ SUM(IF($m_lang=1 AND REPLACE(a.word,' ','')='$m_search',200,0))
+ SUM(IF(BINARY a.word = '$m_search',20,0))
+ SUM(IF(CONCAT(' ','$m_search',' ') LIKE CONCAT('% ',a.word,' %'),30,0))
+ SUM(IF(CONCAT(' ',a.word,' ') LIKE CONCAT('% ','$m_search',' %'),30,0))
+ SUM(IF(REPLACE(a.word,'-',' ') LIKE CONCAT('%','$m_search','%'),6,0))
+ SUM(IF(CHAR_LENGTH(a.word)>=3,1,0))
) AS weight
# , (SUBSTRING_INDEX(SUBSTRING_INDEX(a.word,' ',2),' ',-1)) AS temp_word
FROM dict_word_list a LEFT JOIN cjb_dict b ON a.no=b.no
WHERE
# (word를 search로 검색) 한글 (1) 이면, => 빈칸 없이 검색
IF($m_lang=1,REPLACE(a.word,' ',''),a.word) LIKE IF($m_lang=1,CONCAT('%','$m_search','%'),NULL)
# (word를 search로 검색) 한글 (1) 이면, => 양쪽 모두 붙여서도 검색, 단, 맨앞 일치해야,
|| CONCAT(' ',IF($m_lang=1,REPLACE(a.word,' ',''),a.word),' ') LIKE CONCAT('% ',REPLACE('$m_search',' ',''),'%')
# (search를 word로 검색) 영어 이면, => 단어 마다 정확 일치 (단, 복수형 s 포함)
|| CONCAT(' ','$m_search',' ') LIKE CONCAT('% ',a.word,'s %')
# (search를 word로 검색) 한글어 검색이면 => 단어 붙여서도 일치
|| CONCAT(' ','$m_search',' ') LIKE CONCAT(IF($m_lang=1,'%','% '),IF($m_lang=1,REPLACE(a.word,' ',''),a.word),IF($m_lang=1,'%',' %'))
# (search에서 word를 검색)
|| IF(CHAR_LENGTH(a.word)>2,CONCAT(' ','$m_search',' ') LIKE CONCAT('%',a.word,'%'),0)
# (search에서 word를 검색) => [ ] 검출 => 한글이면,
|| ( CONCAT(' ','$kr_deli_search',' ') LIKE CONCAT( '% ', IF(LOCATE('[',a.word)=0, a.word, TRIM(SUBSTRING(a.word,1,LOCATE('[',a.word)-1))), ' %' ) )
# - 영어 => 단어 마다 정확 일치, 한글 => 붙여서도 일치 (단, 좌 끝 OR 우 끝 일치)
|| ( CONCAT(' ','$m_search',' ') LIKE CONCAT(IF($m_lang=1,'% ','% '), IF(LOCATE('[',a.word)=0, a.word, TRIM(SUBSTRING(a.word,1,LOCATE('[',a.word)-1))), IF($m_lang=1,'%',' %') ) )
OR
( CONCAT(' ','$m_search',' ') LIKE CONCAT(IF($m_lang=1,'%','% '),IF(LOCATE('[',a.word)=0,a.word,TRIM(SUBSTRING(a.word,1,LOCATE('[',a.word)-1))),IF($m_lang=1,' %',' %') ) )
# 대쉬 => 공백 변환 후 검색
|| REPLACE(a.word,'-',' ') LIKE CONCAT('%','$m_search','%')
# word 빈 칸 구분, ('-'=>' ')은 포함 ('/'=>' ')은 제외
|| ( CASE (LENGTH(REPLACE(a.word,'-',' '))-LENGTH(REPLACE(REPLACE(a.word,'-',' '),' ','')))/LENGTH(' ')
WHEN 1 THEN ('$m_search' LIKE CONCAT('%',SUBSTRING_INDEX(REPLACE(a.word,'-',' '),' ',1),'%')) AND ('$m_search' LIKE CONCAT('%',SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(a.word,'-',' '),' ',2),' ',-1),'%'))
WHEN 2 THEN ('$m_search' LIKE CONCAT('%',SUBSTRING_INDEX(REPLACE(a.word,'-',' '),' ',1),'%')) AND ('$m_search' LIKE CONCAT('%',SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(a.word,'-',' '),' ',2),' ',-1),'%')) AND ('$m_search' LIKE CONCAT('%',SUBSTRING_INDEX(REPLACE(a.word,'-',' '),' ',-1),'%'))
END
)
# m_search 빈 칸 구분, ('-'=>' ')은 포함
|| ( CASE (LENGTH(REPLACE('$m_search','-',' '))-LENGTH(REPLACE(REPLACE('$m_search','-',' '),' ','')))/LENGTH(' ')
WHEN 1 THEN (a.word LIKE CONCAT('%',SUBSTRING_INDEX(REPLACE('$m_search','-',' '),' ',1),'%')) AND (a.word LIKE CONCAT('%',SUBSTRING_INDEX(REPLACE('$m_search','-',' '),' ',-1),'%'))
WHEN 2 THEN (a.word LIKE CONCAT('%',SUBSTRING_INDEX(REPLACE('$m_search','-',' '),' ',1),'%')) AND (a.word LIKE CONCAT('%',SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE('$m_search','-',' '),' ',2),' ',-1),'%')) AND (a.word LIKE CONCAT('%',SUBSTRING_INDEX(REPLACE('$m_search','-',' '),' ',-1),'%'))
END
)
# 3자 이상 약어 word
|| IF(CHAR_LENGTH(a.word)>=3 AND a.wordtype='y','$m_search' LIKE CONCAT('%',a.word,'%'),0)
# 1 단어(빈 칸 없음),길이 차이 3자 미만
|| IF(ABS(CHAR_LENGTH(a.word)-CHAR_LENGTH('$m_search'))<3 AND NOT ('$m_search' LIKE '% %' AND NOT a.wordtype='y'), '$m_search' LIKE CONCAT('%',a.word,'%'), 0)
GROUP BY a.no
ORDER BY weight DESC
$limit
";
/*
|| CASE (LENGTH(a.word)-LENGTH(REPLACE(a.word,' ','')))/LENGTH(' ')
WHEN 1 THEN (SUBSTRING_INDEX(word,' ',1) AND SUBSTRING_INDEX(SUBSTRING_INDEX(word,' ',2),' ',-1))
ELSE 0
END
REPLACE(REPLACE(a.word,'-',' '),'/',' ')
*/
return $query;
}
?>