| searching for substrings in words in message-search [message #159383] |
Thu, 28 May 2009 05:15  |
Peter Vendike
Messages: 65
Registered: February 2009
Location: Denmark
Karma: 0
|
Member
Translator |
|
|
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 09:58  |
Peter Vendike
Messages: 65
Registered: February 2009
Location: Denmark
Karma: 0
|
Member
Translator |
|
|
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
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]