FUDforum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » FUDforum Development » Plugins and Code Hacks » searching for substrings in words in message-search
Show: Today's Messages :: Unread Messages :: Show Polls :: Message Navigator
| Subscribe to topic | Bookmark topic 
Switch to threaded view of this topic Create a new topic Submit Reply
searching for substrings in words in message-search [message #159383] Thu, 28 May 2009 01:15 Go to next message
Peter Vendike is currently offline  Peter Vendike   Denmark
Messages: 65
Registered: February 2009
Location: Denmark
Karma: 0
Member
Translator
add to buddy list
ignore all messages by this user
It took me more than a week just to understand partly how search.php.t works when searching messages, had to learn some mysql, but still a pure beginner. Got this idea by googling.

I want to use the sql LIKE method to search in words like:
SELECT * FROM TABLE x WHERE word LIKE '%sub-word%'

The original code is very dense - and clever - doing all the combined and/or searching on the word index in only one query.

So I split up that query in several smaller ones, and this code works as the original, I think, from line 74:


-		q("INSERT IGNORE INTO {SQL_TABLE_PREFIX}search_cache (srch_query, query_type, expiry, msg_id, n_match) SELECT '".$qry_lck."', ".$qt.", ".__request_timestamp__.", msg_id, count(*) as word_count FROM {SQL_TABLE_PREFIX}search s INNER JOIN {SQL_TABLE_PREFIX}".$tbl." i ON i.word_id=s.id WHERE word IN(".$qr.") GROUP BY msg_id ORDER BY word_count DESC LIMIT 500");

+		q("CREATE TEMPORARY TABLE si (idsi int, id int(11), word varchar(50), word_id int(11), msg_id int(11))");
+		foreach($wa as $w) {
+			$keyw = $w; // $keyw = "%".$w."%";
+			q("INSERT INTO si SELECT * FROM {SQL_TABLE_PREFIX}search s INNER JOIN {SQL_TABLE_PREFIX}".$tbl." i ON i.word_id=s.id WHERE 'word' LIKE ".$keyw);
+			}
+		q("INSERT IGNORE INTO {SQL_TABLE_PREFIX}search_cache (srch_query, query_type, expiry, msg_id, n_match) SELECT '".$qry_lck."', ".$qt.", ".__request_timestamp__.", msg_id, count(*) as word_count FROM si GROUP BY msg_id ORDER BY word_count DESC LIMIT 500");
+		q("DROP TABLE si");



But I'm stuck now because I tried dozens of combinations to concatenate 'word' with '%' but allways got erroneous quotes inside the combined word like: '%'word'%', which mysql doesn't accept, should be '%word%'

This is i PHP problem, hope to find that solution also.

Re: searching for substrings in words in message-search [message #159405 is a reply to message #159383] Sun, 31 May 2009 05:58 Go to previous message
Peter Vendike is currently offline  Peter Vendike   Denmark
Messages: 65
Registered: February 2009
Location: Denmark
Karma: 0
Member
Translator
add to buddy list
ignore all messages by this user
Got it to work like this:

(Concatanation in Mysql works, couldn't get php to make the concat right)



-		q("INSERT IGNORE INTO {SQL_TABLE_PREFIX}search_cache (srch_query, query_type, expiry, msg_id, n_match) SELECT '".$qry_lck."', ".$qt.", ".__request_timestamp__.", msg_id, count(*) as word_count FROM {SQL_TABLE_PREFIX}search s INNER JOIN {SQL_TABLE_PREFIX}".$tbl." i ON i.word_id=s.id WHERE word IN(".$qr.") GROUP BY msg_id ORDER BY word_count DESC LIMIT 500");

+		q("CREATE TEMPORARY TABLE si (idsi int, id int(11), word varchar(50), word_id int(11), msg_id int(11))");
+		foreach($wa as $w) {
+			q("INSERT INTO si SELECT * FROM {SQL_TABLE_PREFIX}search s INNER JOIN {SQL_TABLE_PREFIX}".$tbl." i ON i.word_id=s.id WHERE word LIKE CONCAT(".$w.", '%')");
+			}
+		q("INSERT IGNORE INTO {SQL_TABLE_PREFIX}search_cache (srch_query, query_type, expiry, msg_id, n_match) SELECT '".$qry_lck."', ".$qt.", ".__request_timestamp__.", msg_id, count(*) as word_count FROM si GROUP BY msg_id ORDER BY word_count DESC LIMIT 500");



Next thing could be to make it selectable, wich type of search you want.


Peter
Quick Reply
Formatting Tools:   
  Switch to threaded view of this topic Create a new topic
Previous Topic: Hacks for files in /src directory must get compiled before run!
Next Topic: custom tags display area and forum rank
Goto Forum:
  

-=] Back to Top [=-
[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Dec 16 21:34:42 EST 2017

Total time taken to generate the page: 0.00662 seconds