Fulltext boolean search v.2.2 for vB

Hello all!

Moving here from beta forum
https://vborg.vbsupport.ru/showthread.php?t=62218

This hack makes nearly same for vB3 as
[vB 2.2.x] - Mysql 4 Search hack
https://vborg.vbsupport.ru/showthread.php?t=51716
for vB2

You will need MySQL server v4.0.1 or better (but sometimes it may work on 3.23.xx). After installing you will be able to search with empty native vB index (word and postindex tables) and using modifiers.
Allowed modifiers + are ,-, * and "
All modifiers except * should be used only once for one word (in the beginning and without space).
* it should be used at the end of a word.
For example:
windows unix -> will find messages containing at least one these words.
+windows +unix -> will find messages with both this words.
windows* -> will find "windows", "windowss", "windowssauce" or "windowst".
*indows will NOT find "windows"
"some words" -> will find "some words of wisdom", but will not find "some extra words".
Search phrase length limitations replaced with results number limitation.
Value of old "Search Index Maximum Word Length" used to limit number of posts in the result returned by fulltext search (control panel/Message Searching Options)
Supposed that it must run faster then native vB search

History:
v.2.2 [5 Apr 2004]
- search words relevance (when sort by relevance) added at last
but little different then native vB (it may not work when searching with * modifiers)
- attempt to fix incompatibility with other hacks
=to upgrade replace code block #5 in search.php with latest one

v.2.1 [4 Apr 2004]
- Excluding from search forums with "Index New Posts in Search Engine" option set to "No"

v.2.0 [30 Mar 2004]
-"Similar Threads" now must start working (to move from 1.x to 2 just change one more script - functions_search.php)

v.1.9 [29 Mar 2004]
-checking if $query string is not empty before running fill text sql

v.1.8 [20 Mar 2004]
- line numbers and higlight code changed for VB3 Gold
- more tests and error explanations

v.1.7 [9 Mar 2004]
- MySQL error for administrators bug fixed
checking is $not_forumid string exixts before adding it to query

v.1.6 [9 Mar 2004]
- national letters bug fixed
preg_replace("~[^\w\"\-+\* ]~i", "", $query);
was replaced by
preg_replace("~[^\w\xC0-\xFF\"\-+\* ]~i", "", $query);

v.1.5 [8 Mar 2004]
- TABLE_PREFIX bug fixed
- slightly optimised SQL requests

v.1.4 [8 Mar 2004]
- delete_post_index function turned off
- more tests and error explanations

v.1.3 [7 Mar 2004]
- less code because of using native vB $postQueryLogic and $threadQueryLogic conditions
- more tests and error explanations

v.1.2 [7 Mar 2004]
- boolean mode can be turned off in AdminCP ("Allow Search Wild Cards" setting)
- "titles only" search fixed
- limiting number of matches retunned by fulltext search AFTER applying search conditions

v.1.1 [7 Mar 2004]
- HighLight support added

[dslteam]

Hello!

Great Hack! We use vb3 gold with about 320.000 posts and I have just installed it. On our Dual AMD MP 2.5 Ghz - the installation (with these DB queries) took about 23 seconds.

The search performance is indeed much better now.

Thanks!

[JohnWoo]

Yes.. You are right msimplay. I missed that thing.
Uploading updated zip and to upgrage just change in search.php

//fast list of forums visible and searchable for user
	foreach ($bbuserinfo['forumpermissions'] AS $forumid => $fperms) {
		if (!($fperms & CANVIEW) OR !($fperms & CANSEARCH) OR !verify_forum_password($forumid, $forum['password'], false)) {
			$not_forumid .= $forumid." ";
		}
	}

to

//fast list of forums not visible or searchable for user
	foreach ($bbuserinfo['forumpermissions'] AS $forumid => $fperms) {
		if (!($fperms & CANVIEW) OR !($fperms & CANSEARCH) OR !verify_forum_password($forumid, $forum['password'], false) OR !($forumcache[$forumid]["options"] & $_FORUMOPTIONS["indexposts"])) {
			$not_forumid .= $forumid." ";
		}
	}

[msimplay]

Originally Posted by JohnWoo

Yes.. You are right msimplay. I missed that thing.
Uploading updated zip and to upgrage just change in search.php

//fast list of forums visible and searchable for user
  	foreach ($bbuserinfo['forumpermissions'] AS $forumid => $fperms) {
 		if (!($fperms & CANVIEW) OR !($fperms & CANSEARCH) OR !verify_forum_password($forumid, $forum['password'], false)) {
  			$not_forumid .= $forumid." ";
  		}
  	}

to

//fast list of forums not visible or searchable for user
  	foreach ($bbuserinfo['forumpermissions'] AS $forumid => $fperms) {
 		if (!($fperms & CANVIEW) OR !($fperms & CANSEARCH) OR !verify_forum_password($forumid, $forum['password'], false) OR !($forumcache[$forumid]["options"] & $_FORUMOPTIONS["indexposts"])) {
  			$not_forumid .= $forumid." ";
  		}
  	}

that doesn't seem to work i can still search those forums
i tested with a another account thinking maybe it only applies to not admins
but no that doesn't seem to work

[JohnWoo]

hmm..
You are able to search in that forums typing "Key Words" in field on search page or you can see that forums after clicking some link (like unanswered post hack have)?

[msimplay]

i have a staff forum which has a post called reported in there
i used the nav drop down
and typed in report*

but it seemed to work on showthread
but again not with unanswered posts

obviosly normal registered members can't see it due to permissions
coz thats a staff forum

but what about forums that are not protected by permissions and are still viewable but just don't want them to be searchable
for example a test forum where people test signatures avatars bbcode etc

just tested again your hack works on
showthread
advanced search
forumdisplay

not navbar
not unanswered

[JohnWoo]

Think that I need to think a little
But can you please try to replace 2 strings

unset ($postQueryLogic);
$postQueryLogic = array('post.postid IN(' . $postids . ')');

with one

$postQueryLogic[] ='post.postid IN(' . $postids . ')';

and say how it will go?

PS using unset because I still trying to give less job to database - sorry

[msimplay]

hey sorry for bugging you
i have found a new bug
when you search this thread it returns results from other threads

[msimplay]

Originally Posted by JohnWoo

Think that I need to think a little
But can you please try to replace 2 strings

unset ($postQueryLogic);
$postQueryLogic = array('post.postid IN(' . $postids . ')');

with one

$postQueryLogic[] ='post.postid IN(' . $postids . ')';

and say how it will go?

PS using unset because I still trying to give less job to database - sorry

this doesnt seem to work :disappointed:

[JohnWoo]

and how it is now? after v.2.2 changes ?

[jb605]

I just installed this hack on vB 3.0.0 gold version. I am running a chinese website. Chinese is multibyte language, and the words are not delimited by space. I thougt by using fulltext search, the searched keyword is matched against the fulltext index directly, so there is no need to build word index on post. Am I correct?

But now I got no search result at all. Can anybody give any hints?

Thanks a lot.