// разбивка строки на массив токенов
function search_get_tokens(&$query,&$match) {
$query = (get_magic_quotes_gpc()) ? $query=StripSlashes(trim($query)) : trim($query);
$match=intval($match);
$tokNum = 0;
$tokens = array();
if ($query!='') {
if($match!=3){
//Build Tokens. There's a gawd awful
//way of doing this with a regex, but it's messy
$InQuotedString = 0;
$params = explode(" ", $query);
$tokens[$tokNum] = "";
for($i=0; $i<sizeof($params); $i++){
if(!isset($tokens[$tokNum])){
$tokens[$tokNum] = "";
}
$param = $params[$i];
if(strpos($param,'"')===0 || preg_match('/^[+-]"/', $param)){
$InQuotedString = 1;
}
if($InQuotedString == 1){
$tokens[$tokNum] .= str_replace('"', '', $param) . " ";
}else{
$tokens[$tokNum++] = $param;
}
if(preg_match('/"$/', $param)){
$InQuotedString = 0;
$tokens[$tokNum] = chop($tokens[$tokNum]);
if($tokens[$tokNum]==""){
unset($tokens[$tokNum]);
}else{
$tokNum++;
}
}
}
}else{
$tokens[$tokNum] = str_replace('"', '', chop($query));
}
if(count($tokens)==0) unset($tokens);
}
return $tokens;
}
// генерация RELEVANCE части sql запроса по массиву токенов
function search_get_sql_relevance($tokens,$match,$fields) {
$good_words='';
$bad_words='';
$max_relevance=1;
for($i=0; $i<count($tokens); $i++){
if (strlen($tokens[$i])>2) {
$token = trim($tokens[$i]);
if(strstr($token,'+')==$token){
$token=str_replace('"','',preg_replace('/^\+/','',$token));
$good_words.=$token.' ';
for($x=0; $x<count($fields); $x++){
$relevance[]="IF($fields[$x] LIKE '%$token%', 9, 0)";
$max_relevance+=9;
}
}elseif(strstr($token,'-')==$token){
$token=str_replace('"','',preg_replace('/^\-/','',$token));
$bad_words.=$token.' ';
for($x=0; $x<count($fields); $x++){
$relevance[]="IF($fields[$x] NOT LIKE '%$token%', 9, 0)";
$max_relevance+=9;
}
}elseif(trim($token)!=""){
$token=str_replace('"','',$token);
$good_words.=$token.' ';
for($x=0; $x<count($fields); $x++){
$relevance[]="IF($fields[$x] LIKE '%$token%', 9, 0)";
$max_relevance+=9;
}
}
}
}
//совпадение полной строки
for($x=0; $x<count($fields); $x++){
$relevance[]="IF($fields[$x] LIKE '%$good_words%', ".strval(substr_count($good_words, ' ')*10).", 0)";
$max_relevance+=10*substr_count($good_words, ' ');
}
for($x=0; $x<count($fields); $x++){
$relevance[]="IF($fields[$x] NOT LIKE '%$bad_words%', ".strval(substr_count($bad_words, ' ')*10).", 0)";
$max_relevance+=10*substr_count($bad_words, ' ');
}
if (@is_array($relevance)) {
return ' (0+('.implode('+',$relevance).'))*100/'.$max_relevance.' AS relevance ';
}
return '';
}
// генерация WHERE части sql запроса по массиву токенов
function search_get_sql_where($tokens,$match,$fields) {
$SQLquery=' (';
for($i=0; $i<count($tokens); $i++){
if (strlen($tokens[$i])>2) {
for($x=0; $x<count($fields); $x++){
$token = trim($tokens[$i]);
if(strstr($token,'+')==$token){
$token = preg_replace('/^\+/', '', $token);
$SQLquery .= "$fields[$x] LIKE '%$token%'";
if($x<count($fields)-1){
$SQLquery .= " OR ";
}
}elseif(strstr($token,'-')==$token){
$token = preg_replace('/^\-/', "", $token);
$SQLquery .= "$fields[$x] NOT LIKE '%$token%'";
if($x<count($fields)-1){
if($match==1){
$SQLquery .= ') AND (';
}else{
$SQLquery .= ') OR (';
}
}
}elseif(trim($token)!=""){
$SQLquery .= "$fields[$x] LIKE '%$token%'";
if($x<count($fields)-1){
$SQLquery .= ' OR ';
}
}
}
}
if($i<count($tokens)-1){
if($match==1){
$SQLquery .= ') AND (';
}else{
$SQLquery .= ') OR (';
}
}else{
$SQLquery .= ') ';
}
}
return $SQLquery;
}
//пользуюсь примерно следующим образом:
if (!isset($query)) $query='';
$query = (get_magic_quotes_gpc()) ? $query=StripSlashes(trim($query)) : trim($query);
if ($query!='') {
$tokens=search_get_tokens($query,$match);
if(@is_array($tokens)){
$fields = array();
switch($type) {
case 'full':
$fields[] = "t1.title";
$fields[] = "t1.info";
$fields[] = "t1.keywords";
$fields[] = "t1.text";
$SQLquery="SELECT t1.url AS url, DATE_FORMAT(t1.m_time,'%d %M %Y %H:%i') AS artikles_time, t1.divs_path, t1.divs_title, t1.title, t1.info, t1.keywords, ";
$relevance=search_get_sql_relevance($tokens,$match,$fields);
$SQLquery.=$relevance;
$SQLquery.=" FROM search_index AS t1 WHERE (";
break;
case 'quick':
default:
$fields[] = "t2.title";
$fields[] = "t2.info";
$fields[] = "t1.title";
$fields[] = "t1.info";
$fields[] = "t1.keywords";
$SQLquery="SELECT t1.id AS id, DATE_FORMAT(t1.c_time,'%d %M %Y %H:%i') AS artikles_time, CONCAT('$cfg[virtualhost]$cfg[rootdir]',t3.path) AS divs_path, t2.title AS divs_title, t1.title AS title, t1.info AS info, t1.keywords AS keywords FROM artikles AS t1 LEFT JOIN divs_info AS t2 ON (t2.divs_id=t1.divs_id AND t2.lang_id=$lang_id) LEFT JOIN divs AS t3 ON (t3.id=t1.divs_id) WHERE (";
break;
}
$SQLquery.=search_get_sql_where($tokens,$match,$fields);
switch($type) {
case 'full':
if ($relevance) $SQLquery .= ') ORDER BY relevance DESC, t1.m_time DESC';
else $SQLquery .= ") ORDER BY t1.m_time DESC";
break;
case 'quick':
default:
$SQLquery .= ") ORDER BY t1.m_time DESC";
break;
}
} // if is_array($tokens)
} // end if $query!=''
echo $SQLQuery
$r=$db->Execute($SQLquery);
}
в те далекие времена когда пользование FULLTEXT было физически невозможно (3.22.x), а потом оно приводило выпаданию 3.23.x в корку