전체검색을 할때 생기는 오류.. 정보
그누보드 전체검색을 할때 생기는 오류..본문
select count(wr_id) from gb_write_listening a, gb_board b where (wr_datetime >= '1970-01-01 09:00:00') and ( (INSTR(LOWER(wr_all)), LOWER('2004')) > 0) ) and wr_secret = '0' and (b.bo_table='listening')
1064 : 'SQL 구문에 오류가 있습니다.' 에러 같읍니다. ('), LOWER('2004')) > 0) ) and wr_secret = '0' and (b.bo_table='listening') ' 명령어 라인 1)
오류입니다.
그리고 gnusearch.php
입니다.,
어디가 잘못되어서 그런지 도와주세요.
<?
if (!defined("_GNUBOARD_")) exit; // 개별 페이지 접근 불가
define("_DOCTYPE_", "DESIGN");
$html_title = "검색 : $stext";
// 그누검색 설정파일
include "./bbs/gnusearch.cfg.php";
include "./bbs/gblayer.php";
if (!$stext || strlen($stext) < 2) { alert("검색어는 최소 2자 이상 되어야 합니다."); }
if (!isset($soperator)) {
$soperator = $gnusearch[operator];
}
if (!isset($srch_rows)) {
$srch_rows = $gnusearch[rows];
}
if (!isset($srch_comment)) {
$srch_comment = $gnusearch[comment];
}
if (!isset($srch_days)) {
$srch_days = $gnusearch[days];
}
if ($srch_days) {
$intime = date("Y-m-d H:i:s", strtotime($now) - ($srch_days * 86400));
} else {
$intime = date("Y-m-d H:i:s", 0);
}
// 3.36
unset($gnusearch[tables]);
$sql = " select bo_table from $cfg[table_board]
where bo_use_search = '1'
and bo_list_level <= '$member[mb_level]'
and bo_read_level <= '$member[mb_level]' ";
if ($onetable) $sql .= " and bo_table = '$onetable' ";
$sql .= " order by bo_order_search ";
$result = sql_query($sql);
for ($i=0; $row=mysql_fetch_array($result); $i++) {
$gnusearch[tables][] = $row[bo_table];
}
$search_query = "sselect=$sselect&stext=$stext&soperator=$soperator&srch_rows=$srch_rows&srch_comment=$srch_comment&srch_days=$srch_days";
?>
<table align= width=800 cellpadding=2 cellspacing=0 width="565">
<form name=fgnusearch2 method=get action='<?="./"?>'>
<tr>
<td align=center width="561">
<input type='hidden' name='doc' value='<?="bbs/gnusearch.php"?>'>
<input type='hidden' name='srch_rows' value='<?=$srch_rows?>'>
<input type='hidden' name='srch_comment' value='<?=$srch_comment?>'>
<input type='hidden' name='srch_days' value='<?=$srch_days?>'>
<select name=sselect class=select>
<option value="wr_all)">전체</option>
<option value="concat(wr_subject,wr_content)">제목+내용</option>
<option value="wr_option)">년도_과목_학년_출판사</option>
<option value='wr_name'>이름</option>
<option value='wr_subject'>제목</option>
<option value='wr_content'>내용</option>
</select>
<script language='javascript'>
document.fgnusearch2.sselect.value = '<?=$sselect?>';
</script>
<input type=text name=stext size=40 class=input value='<?=hsc($stext)?>'>
<input type=submit value=' 검 색 '>
</td>
</tr>
<tr>
<td align=center width="561">
연산자 <input type='radio' name='soperator' value='0' <?=(!$soperator) ? "checked" : "";?>>OR <input type='radio' name='soperator' value='1' <?=($soperator) ? "checked" : "";?>>AND
</td>
</tr>
</form>
</table>
<p>
<p> </p>
<table cellpadding="0" cellspacing="0">
<tr>
<td width="800"><p>
<?
// 3.36
// 검색필드에 , 가 있다면 ,' ', 로 만든다 concat()
$sselect = preg_replace("/,/", ",' ',", $sselect);
$op = "";
$str = " and ( ";
// 검색어를 구분자로 나눈다. 여기서는 공백
$s = explode(" ", $stext);
for ($i=0; $i<count($s); $i++) {
// 3.36
/*
if ($sselect == "concat(wr_subject,wr_content)") {
$str .= $op . " (wr_subject like '%$s[$i]%' or wr_content like '%$s[$i]%') ";
} else if ($sselect == "wr_all") {
$str .= $op . " (wr_subject like '%$s[$i]%' or wr_content like '%$s[$i]%' or wr_1 like '%$s[$i]%' or wr_2 like '%$s[$i]%' or wr_3 like '%$s[$i]%' or wr_4 like '%$s[$i]%' or wr_5 like '%$s[$i]%') ";
} else {
$str .= $op . " ($sselect like '%$s[$i]%') ";
}
*/
//$str .= $op . " ($sselect like '%$s[$i]%') ";
$str .= $op . " (INSTR(LOWER($sselect), LOWER('$s[$i]')) > 0) ";
$op = ($soperator) ? " and " : " or ";
}
$str .= " ) ";
$sql_search = " where (wr_datetime >= '$intime') " . $str;
if (!$srch_comment) {
$sql_search .= " and (wr_comment = '0') ";
}
$sql_search .= " and wr_secret = '0' ";
$str_board_list = "";
$board_count = 0;
$total_count = 0;
for ($i=0; $i<count($gnusearch[tables]); $i++) {
$tbl = $cfg[write_table_prefix] . $gnusearch[tables][$i];
$sql = " select count(wr_id) from $tbl a, $cfg[table_board] b $sql_search and (b.bo_table='{$gnusearch[tables][$i]}') ";
// 권한별 검색기능
//$sql .= " and (b.bo_table='{$gnusearch[tables][$i]}' and b.bo_list_level <= '$member[mb_level]' and b.bo_read_level <= '$member[mb_level]') ";
$row = sql_fetch($sql);
$total_count += $row[0];
if ($row[0]) {
$board_count++;
$search_table[] = $gnusearch[tables][$i];
$search_table_count[] = $total_count;
$sql2 = " select bo_subject from $cfg[table_board] where bo_table = '{$gnusearch[tables][$i]}' ";
$row2 = sql_fetch($sql2);
$str_board_list .= "<li><a href='./?doc=$doc&$search_query&onetable={$gnusearch[tables][$i]}'>$row2[0]</a> ($row[0])";
}
}
$rows = $srch_rows;
$total_page = ceil($total_count / $rows); // 전체 페이지 계산
if ($page == "") { $page = 1; } // 페이지가 없으면 첫 페이지 (1 페이지)
$from_record = ($page - 1) * $rows; // 시작 열을 구함
echo "<ul type=circle><li><b>검색된 게시판 리스트</b> (<b>$board_count</b>개의 게시판, <b>".nf($total_count)."</b>개의 게시글, ".nf($page)."/<b>".nf($total_page)."</b> 페이지)</ul>";
if ($board_count) {
echo "<ul><ul type=square style='line-height:130%;'>";
if ($onetable) {
echo "<li><a href='./?doc=$doc&$search_query'>전체게시판 검색</a>";
}
echo $str_board_list;
echo "</ul></ul>";
} else {
echo "<ul style='line-height:130%;'><li>검색된 자료가 하나도 없습니다.</ul>";
}
for ($i=0; $i<count($search_table); $i++) {
if ($from_record < $search_table_count[$i]) {
$table_index = $i;
$from_record = $from_record - $search_table_count[$i-1];
break;
}
}
$k=0;
for ($idx=$table_index; $idx<count($search_table); $idx++) {
$sql = " select bo_subject from $cfg[table_board] where bo_table = '$search_table[$idx]' ";
$row = sql_fetch($sql);
echo "<ul type=circle><li><b><a href='./?doc=bbs/gnuboard.php&bo_table=$search_table[$idx]&$search_query'><u>$row[bo_subject]</u></a>에서의 검색결과</b></ul>";
$tbl = $cfg[write_table_prefix] . $search_table[$idx];
# 3.22 에서 필드 * 를 필드명 직접 기술
$sql = " select mb_id,
wr_id,
wr_parent_id,
wr_subject,
wr_content,
wr_name,
wr_email,
wr_homepage,
wr_comment,
wr_datetime
from $tbl
$sql_search
order by wr_id desc limit $from_record, $rows ";
$result = sql_query($sql);
for ($i=0; $row=mysql_fetch_array($result); $i++) {
echo "<ul><ul type=square><li style='line-height:130%;'>";
$link = "./?doc=bbs/gnuboard.php&bo_table=$search_table[$idx]&wr_id=$row[wr_parent_id]&$search_query";
if ($row[wr_comment]) {
$link .= "#c{$row[wr_id]}";
echo "<font color=999999>[코멘트]</font> ";
$sql2 = " select wr_subject, wr_secret from $tbl where wr_id = '$row[wr_parent_id]' ";
$row2 = sql_fetch($sql2);
$row[wr_subject] = $row2[wr_subject];
// 비밀글은 검색 불가
if ($row2[wr_secret]) {
$row[wr_content] = "********";
}
}
echo "<a href='$link'><u>";
$subject = $row[wr_subject];
$content = cut_str(get_text($row[wr_content]),300,"…");
if (preg_match("/wr_subject/", $sselect)) $subject = search_font($stext, $subject);
if (preg_match("/wr_content/", $sselect)) $content = search_font($stext, $content);
echo $subject;
echo "</u></a> [<a href='$link' target=_blank>새창</a>]<br>";
echo $content;
$tmp_name = gblayer($row[mb_id], $row[wr_name], $row[wr_email], $row[wr_homepage]);
echo "<br><font color=#999999>$row[wr_datetime]</font> $tmp_name<br>";
echo "</ul></ul>\n";
$k++;
if ($k >= $rows) { break; }
}
mysql_free_result($result);
if ($k >= $rows) { break; }
$from_record = 0;
}
$pagelist = get_paging($default[de_write_pages], $page, $total_page, "./?doc=$doc&$search_query&onetable=$onetable&page=");
if ($pagelist) {
echo "<table width=100% cellpadding=3 cellspacing=1><tr><td align=right>$pagelist</td></tr></table>\n";
}
?>
</td>
</tr>
</table>
지금상태에서 게시판에서는 전체 wr_all이 잘됩니다.
카테고리 빼고...
그런데 싸이트에 전체검색에서
wr_all을 선택시에 위와 같은 에러가 나타납니다.
어디를 수정을 해야되는지 좀 알려주십시오.
1064 : 'SQL 구문에 오류가 있습니다.' 에러 같읍니다. ('), LOWER('2004')) > 0) ) and wr_secret = '0' and (b.bo_table='listening') ' 명령어 라인 1)
오류입니다.
그리고 gnusearch.php
입니다.,
어디가 잘못되어서 그런지 도와주세요.
<?
if (!defined("_GNUBOARD_")) exit; // 개별 페이지 접근 불가
define("_DOCTYPE_", "DESIGN");
$html_title = "검색 : $stext";
// 그누검색 설정파일
include "./bbs/gnusearch.cfg.php";
include "./bbs/gblayer.php";
if (!$stext || strlen($stext) < 2) { alert("검색어는 최소 2자 이상 되어야 합니다."); }
if (!isset($soperator)) {
$soperator = $gnusearch[operator];
}
if (!isset($srch_rows)) {
$srch_rows = $gnusearch[rows];
}
if (!isset($srch_comment)) {
$srch_comment = $gnusearch[comment];
}
if (!isset($srch_days)) {
$srch_days = $gnusearch[days];
}
if ($srch_days) {
$intime = date("Y-m-d H:i:s", strtotime($now) - ($srch_days * 86400));
} else {
$intime = date("Y-m-d H:i:s", 0);
}
// 3.36
unset($gnusearch[tables]);
$sql = " select bo_table from $cfg[table_board]
where bo_use_search = '1'
and bo_list_level <= '$member[mb_level]'
and bo_read_level <= '$member[mb_level]' ";
if ($onetable) $sql .= " and bo_table = '$onetable' ";
$sql .= " order by bo_order_search ";
$result = sql_query($sql);
for ($i=0; $row=mysql_fetch_array($result); $i++) {
$gnusearch[tables][] = $row[bo_table];
}
$search_query = "sselect=$sselect&stext=$stext&soperator=$soperator&srch_rows=$srch_rows&srch_comment=$srch_comment&srch_days=$srch_days";
?>
<table align= width=800 cellpadding=2 cellspacing=0 width="565">
<form name=fgnusearch2 method=get action='<?="./"?>'>
<tr>
<td align=center width="561">
<input type='hidden' name='doc' value='<?="bbs/gnusearch.php"?>'>
<input type='hidden' name='srch_rows' value='<?=$srch_rows?>'>
<input type='hidden' name='srch_comment' value='<?=$srch_comment?>'>
<input type='hidden' name='srch_days' value='<?=$srch_days?>'>
<select name=sselect class=select>
<option value="wr_all)">전체</option>
<option value="concat(wr_subject,wr_content)">제목+내용</option>
<option value="wr_option)">년도_과목_학년_출판사</option>
<option value='wr_name'>이름</option>
<option value='wr_subject'>제목</option>
<option value='wr_content'>내용</option>
</select>
<script language='javascript'>
document.fgnusearch2.sselect.value = '<?=$sselect?>';
</script>
<input type=text name=stext size=40 class=input value='<?=hsc($stext)?>'>
<input type=submit value=' 검 색 '>
</td>
</tr>
<tr>
<td align=center width="561">
연산자 <input type='radio' name='soperator' value='0' <?=(!$soperator) ? "checked" : "";?>>OR <input type='radio' name='soperator' value='1' <?=($soperator) ? "checked" : "";?>>AND
</td>
</tr>
</form>
</table>
<p>
<p> </p>
<table cellpadding="0" cellspacing="0">
<tr>
<td width="800"><p>
<?
// 3.36
// 검색필드에 , 가 있다면 ,' ', 로 만든다 concat()
$sselect = preg_replace("/,/", ",' ',", $sselect);
$op = "";
$str = " and ( ";
// 검색어를 구분자로 나눈다. 여기서는 공백
$s = explode(" ", $stext);
for ($i=0; $i<count($s); $i++) {
// 3.36
/*
if ($sselect == "concat(wr_subject,wr_content)") {
$str .= $op . " (wr_subject like '%$s[$i]%' or wr_content like '%$s[$i]%') ";
} else if ($sselect == "wr_all") {
$str .= $op . " (wr_subject like '%$s[$i]%' or wr_content like '%$s[$i]%' or wr_1 like '%$s[$i]%' or wr_2 like '%$s[$i]%' or wr_3 like '%$s[$i]%' or wr_4 like '%$s[$i]%' or wr_5 like '%$s[$i]%') ";
} else {
$str .= $op . " ($sselect like '%$s[$i]%') ";
}
*/
//$str .= $op . " ($sselect like '%$s[$i]%') ";
$str .= $op . " (INSTR(LOWER($sselect), LOWER('$s[$i]')) > 0) ";
$op = ($soperator) ? " and " : " or ";
}
$str .= " ) ";
$sql_search = " where (wr_datetime >= '$intime') " . $str;
if (!$srch_comment) {
$sql_search .= " and (wr_comment = '0') ";
}
$sql_search .= " and wr_secret = '0' ";
$str_board_list = "";
$board_count = 0;
$total_count = 0;
for ($i=0; $i<count($gnusearch[tables]); $i++) {
$tbl = $cfg[write_table_prefix] . $gnusearch[tables][$i];
$sql = " select count(wr_id) from $tbl a, $cfg[table_board] b $sql_search and (b.bo_table='{$gnusearch[tables][$i]}') ";
// 권한별 검색기능
//$sql .= " and (b.bo_table='{$gnusearch[tables][$i]}' and b.bo_list_level <= '$member[mb_level]' and b.bo_read_level <= '$member[mb_level]') ";
$row = sql_fetch($sql);
$total_count += $row[0];
if ($row[0]) {
$board_count++;
$search_table[] = $gnusearch[tables][$i];
$search_table_count[] = $total_count;
$sql2 = " select bo_subject from $cfg[table_board] where bo_table = '{$gnusearch[tables][$i]}' ";
$row2 = sql_fetch($sql2);
$str_board_list .= "<li><a href='./?doc=$doc&$search_query&onetable={$gnusearch[tables][$i]}'>$row2[0]</a> ($row[0])";
}
}
$rows = $srch_rows;
$total_page = ceil($total_count / $rows); // 전체 페이지 계산
if ($page == "") { $page = 1; } // 페이지가 없으면 첫 페이지 (1 페이지)
$from_record = ($page - 1) * $rows; // 시작 열을 구함
echo "<ul type=circle><li><b>검색된 게시판 리스트</b> (<b>$board_count</b>개의 게시판, <b>".nf($total_count)."</b>개의 게시글, ".nf($page)."/<b>".nf($total_page)."</b> 페이지)</ul>";
if ($board_count) {
echo "<ul><ul type=square style='line-height:130%;'>";
if ($onetable) {
echo "<li><a href='./?doc=$doc&$search_query'>전체게시판 검색</a>";
}
echo $str_board_list;
echo "</ul></ul>";
} else {
echo "<ul style='line-height:130%;'><li>검색된 자료가 하나도 없습니다.</ul>";
}
for ($i=0; $i<count($search_table); $i++) {
if ($from_record < $search_table_count[$i]) {
$table_index = $i;
$from_record = $from_record - $search_table_count[$i-1];
break;
}
}
$k=0;
for ($idx=$table_index; $idx<count($search_table); $idx++) {
$sql = " select bo_subject from $cfg[table_board] where bo_table = '$search_table[$idx]' ";
$row = sql_fetch($sql);
echo "<ul type=circle><li><b><a href='./?doc=bbs/gnuboard.php&bo_table=$search_table[$idx]&$search_query'><u>$row[bo_subject]</u></a>에서의 검색결과</b></ul>";
$tbl = $cfg[write_table_prefix] . $search_table[$idx];
# 3.22 에서 필드 * 를 필드명 직접 기술
$sql = " select mb_id,
wr_id,
wr_parent_id,
wr_subject,
wr_content,
wr_name,
wr_email,
wr_homepage,
wr_comment,
wr_datetime
from $tbl
$sql_search
order by wr_id desc limit $from_record, $rows ";
$result = sql_query($sql);
for ($i=0; $row=mysql_fetch_array($result); $i++) {
echo "<ul><ul type=square><li style='line-height:130%;'>";
$link = "./?doc=bbs/gnuboard.php&bo_table=$search_table[$idx]&wr_id=$row[wr_parent_id]&$search_query";
if ($row[wr_comment]) {
$link .= "#c{$row[wr_id]}";
echo "<font color=999999>[코멘트]</font> ";
$sql2 = " select wr_subject, wr_secret from $tbl where wr_id = '$row[wr_parent_id]' ";
$row2 = sql_fetch($sql2);
$row[wr_subject] = $row2[wr_subject];
// 비밀글은 검색 불가
if ($row2[wr_secret]) {
$row[wr_content] = "********";
}
}
echo "<a href='$link'><u>";
$subject = $row[wr_subject];
$content = cut_str(get_text($row[wr_content]),300,"…");
if (preg_match("/wr_subject/", $sselect)) $subject = search_font($stext, $subject);
if (preg_match("/wr_content/", $sselect)) $content = search_font($stext, $content);
echo $subject;
echo "</u></a> [<a href='$link' target=_blank>새창</a>]<br>";
echo $content;
$tmp_name = gblayer($row[mb_id], $row[wr_name], $row[wr_email], $row[wr_homepage]);
echo "<br><font color=#999999>$row[wr_datetime]</font> $tmp_name<br>";
echo "</ul></ul>\n";
$k++;
if ($k >= $rows) { break; }
}
mysql_free_result($result);
if ($k >= $rows) { break; }
$from_record = 0;
}
$pagelist = get_paging($default[de_write_pages], $page, $total_page, "./?doc=$doc&$search_query&onetable=$onetable&page=");
if ($pagelist) {
echo "<table width=100% cellpadding=3 cellspacing=1><tr><td align=right>$pagelist</td></tr></table>\n";
}
?>
</td>
</tr>
</table>
지금상태에서 게시판에서는 전체 wr_all이 잘됩니다.
카테고리 빼고...
그런데 싸이트에 전체검색에서
wr_all을 선택시에 위와 같은 에러가 나타납니다.
어디를 수정을 해야되는지 좀 알려주십시오.
댓글 전체
자체 해결했습니다.
<option value='concat(concat(wr_subject,wr_content,wr_1,wr_2,wr_3,wr_4,wr_5))'>전체</option>
<option value='concat(concat(wr_subject,wr_content,wr_1,wr_2,wr_3,wr_4,wr_5))'>전체</option>
감사합니다. 저도 님 덕분에 해결했습니다. (__)