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

[Erwin]

Even without the LEFT JOIN, certain phrases do make the query go into my slow_query log, but it's only around 40 seconds now. Much better.

Another question for those with this hack - when I use the Advanced Search page, and look for all posts by username, it works.

But when I look for all threads started by username (from the dropdown menu under the box where you put the username) only really old threads come up.

Does this happen to you too? Any ideas?

[Erwin]

Sorry to ask another question - does Search Thread work for you guys? My members are saying it searches the whole site instead of just the thread.

[msimplay]

Originally Posted by Erwin

Sorry to ask another question - does Search Thread work for you guys? My members are saying it searches the whole site instead of just the thread.

i can confirm this because i did post about it earlier
but nothing has been done as of yet

[cerebro]

Kmike, can you post that templates?

and JohnWoo, maybe make a new release whit Template MOD too, and left join together.

Please this project dont have to die, is really nice...come on

[Erwin]

Originally Posted by cerebro

Kmike, can you post that templates?

and JohnWoo, maybe make a new release whit Template MOD too, and left join together.

Please this project dont have to die, is really nice...come on

This is the best thing that's come out for large vB sites.

[cerebro]

yeah...i know!
i have a 3 Gigabyte Database...insall this hack and reduce to 800 Mg

But something i have to close search for a few day, becauso all forum goind down. I think that the hack need a little more work, but i dont know where. :P

[Erwin]

As some may know, Search Thread does NOT work with this hack installed.

Here is my fix:

Open search.php:

[Removed code - I've made a new piece of code to replace this one that also makes Search Individual Forums work too - refer to post below.]

[Erwin]

Originally Posted by 7thgenCivic.Com

u deleted ur post! argh!

The post is back! I made it better! I decided to share my optimization!

[ImportPassion]

problem somewhere

Database error in vBulletin 3.0.1:

Invalid SQL: SELECT postid FROM post WHERE MATCH (post.pagetext) AGAINST ('hood) LIMIT 0, 500 mysql error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ''hood) LIMIT 0, 500' at line 1

mysql error number: 1064

[Erwin]

Originally Posted by 7thgenCivic.Com

problem somewhere

Database error in vBulletin 3.0.1:

Invalid SQL: SELECT postid FROM post WHERE MATCH (post.pagetext) AGAINST ('hood) LIMIT 0, 500 mysql error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ''hood) LIMIT 0, 500' at line 1

mysql error number: 1064

Are you sure you copied that line as it is? I just cut and pasted that line EXACTLY AS IT IS POSTED THERE just to test it, and it works on my site.