phpBB2Refugees.com Logo
Not affiliated with or endorsed by the phpBB Group

Register •  Login 

Continue the legacy...

Welcome to all phpBB2 Refugees!Wave Smilie

This site is intended to continue support for the legacy 2.x line of the phpBB2 bulletin board package. If you are a fan of phpBB2, please, by all means register, post, and help us out by offering your suggestions. We are primarily a community and support network. Our secondary goal is to provide a phpBB2 MOD Author and Styles area.

"Search" using the POSTS_TEXT_TABLE


 
Search this topic... | Search MOD Development... | Search Box
Register or Login to Post    Index » MOD Development  Previous TopicPrint TopicNext Topic
Author Message
sbourdon
Board Member



Joined: 26 Mar 2009

Posts: 8


flag
PostPosted: Sat Nov 07, 2009 8:42 am 
Post subject: "Search" using the POSTS_TEXT_TABLE

Hello,

After reading this post, I've decided to give the mysql fulltext search MOD a try.

While looking at the installation instructions for the search.php file, I came accross this little piece of code:

Code:
$multibyte_charset = 'utf-8, big5, shift_jis, euc-kr, gb2312';

                  if ( !strstr($multibyte_charset, $lang['ENCODING']) )
                  {
                     $match_word = str_replace('*', '%', $split_search[$i]);
                     $sql = "SELECT m.post_id
                        FROM " . SEARCH_WORD_TABLE . " w, " . SEARCH_MATCH_TABLE . " m, " . POSTS_TABLE . " p
                        WHERE p.post_id = m.post_id
                           AND p.validate = 0
                           AND w.word_text LIKE '$match_word'
                           AND m.word_id = w.word_id
                           AND w.word_common <> 1
                           $search_msg_only";
                  }
                  else
                  {
                     $match_word =  addslashes('%' . str_replace('*', '', $split_search[$i]) . '%');
                     $search_msg_only = ( $search_fields ) ? "OR post_subject LIKE '$match_word'" : '';
                     $sql = "SELECT post_id
                        FROM " . POSTS_TEXT_TABLE . "
                        WHERE post_text LIKE '$match_word'
                        $search_msg_only";
                  }


I'm no php expert, but if I understand this code correctly, phpBB will search the POSTS_TEXT_TABLE instead of the usual SEARCH_WORD and SEARCH_MATCH tables if language files are either utf-8, big5, shift_jis, euc-kr or gb2312, right?

If my files are ISO-8859-1, could I also force phpBB to use the POSTS_TEXT_TABLE? I could the simply drop the SEARCH_WORD and SEARCH_MATCH tables, which use more than 50% of my DB size...

I was thinking about something like this:

Code:

/*                  if ( !strstr($multibyte_charset, $lang['ENCODING']) )
                  {
                     $match_word = str_replace('*', '%', $split_search[$i]);
                     $sql = "SELECT m.post_id
                        FROM " . SEARCH_WORD_TABLE . " w, " . SEARCH_MATCH_TABLE . " m, " . POSTS_TABLE . " p
                        WHERE p.post_id = m.post_id
                           AND p.validate = 0
                           AND w.word_text LIKE '$match_word'
                           AND m.word_id = w.word_id
                           AND w.word_common <> 1
                           $search_msg_only";
                  }
                  else
*/                  {
                     $match_word =  addslashes('%' . str_replace('*', '', $split_search[$i]) . '%');
                     $search_msg_only = ( $search_fields ) ? "OR post_subject LIKE '$match_word'" : '';
                     $sql = "SELECT post_id
                        FROM " . POSTS_TEXT_TABLE . "
                        WHERE post_text LIKE '$match_word'
                        $search_msg_only";
                  }


After what I would apply these modifications, from the mysql fulltext search installation instructions:

Code:
#
#-----[ OPEN ]------------------------------------------
#
includes/functions_search.php
#
#-----[ FIND ]------------------------------------------
#
function add_search_words($mode, $post_id, $post_text, $post_title = '')
{
#
#-----[ AFTER, ADD ]------------------------------------------
#
   return;
#
#-----[ FIND ]------------------------------------------
#
function remove_common($mode, $fraction, $word_id_list = array())
{
#
#-----[ AFTER, ADD ]------------------------------------------
#
   return;
#
#-----[ FIND ]------------------------------------------
#
function remove_search_post($post_id_sql)
{
#
#-----[ AFTER, ADD ]------------------------------------------
#
   return;


Would that be a good idea?

Thanks for all your suggestions! icon_smile.gif
Back to top
Merri
Board Member



Joined: 02 Feb 2009

Posts: 63
Location: Kanta-Häme


flag
PostPosted: Sat Nov 07, 2009 1:33 pm 
Post subject: Re: "Search" using the POSTS_TEXT_TABLE

You didn't look at it carefully enough: that line is never executed. If you take a look to where it is located, you notice for($i = 0; false; $i++) or similar, meaning the for loop is never executed. Instead the line " where match (post_subject, post_text) against('$phrase' in boolean mode) limit $max_retrieve_results"; further above does the searching part.
_________________
Comboa Twitter
Back to top
sbourdon
Board Member



Joined: 26 Mar 2009

Posts: 8


flag
PostPosted: Sat Nov 07, 2009 5:53 pm 
Post subject: Re: "Search" using the POSTS_TEXT_TABLE

Thanks for these explanations.

But if I do not install the mysql fulltext search MOD, and do the modification mentionned previously in the search.php file, will the Search function really use the POSTS_TEXT_TABLE instead of the usual SEARCH_WORD and SEARCH_MATCH tables?

If so, would it be OK even if my files are all encoded using ISO-8859-1?

If it is, I would then apply the modifications to functions_search.php, since it wouldn't be necessary to update those 2 tables anymore...

My goal is simply to get rid of those tables, using more than 50% of my database size...


Many thanks!
Back to top
Merri
Board Member



Joined: 02 Feb 2009

Posts: 63
Location: Kanta-Häme


flag
PostPosted: Sat Nov 07, 2009 5:58 pm 
Post subject: Re: "Search" using the POSTS_TEXT_TABLE

You're probably better off simply installing the mod, because it makes searching much more convenient for your users. The way phpBB's regular search works... well, it is cumbersome. The users of my forum really liked the ability to have a sense-making search functionality instead of the old system that never quite worked the way anyone wanted. Being slow isn't that big of a problem.
_________________
Comboa Twitter
Back to top
sbourdon
Board Member



Joined: 26 Mar 2009

Posts: 8


flag
PostPosted: Sat Nov 07, 2009 6:11 pm 
Post subject: Re: "Search" using the POSTS_TEXT_TABLE

Ok, then!
Thank you once again!

P.S.: Is there any chance we could have a link to your website so we could see the MOD in action? icon_wink.gif
Back to top
Merri
Board Member



Joined: 02 Feb 2009

Posts: 63
Location: Kanta-Häme


flag
PostPosted: Sat Nov 07, 2009 11:10 pm 
Post subject: Re: "Search" using the POSTS_TEXT_TABLE

My site is in Finnish so you can't do that much there, and it is also in the process of getting a new design, but http://keskustelu.kontu.info/ will do. The search box in the top right corner works, write Tolkien, +Peter +Jackson or something in the lines of that subject and you ought to get some search results.
_________________
Comboa Twitter
Back to top
Display posts from previous:   
Register or Login to Post    Index » MOD Development  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 4 Hours
 
Jump to:  

Index • About • FAQ • Rules • Privacy • Search •  Register •  Login 
Not affiliated with or endorsed by the phpBB Group
Powered by phpBB2 © phpBB Group
Generated in 0.0568 seconds using 17 queries. (SQL 0.0143 Parse 0.0007 Other 0.0419)
phpBB Customizations by the phpBBDoctor.com
Template Design by DeLFlo and MomentsOfLight.com Moments of Light Logo