Magento/PHP - Modifying the MySQL Queries Underlying the Front-End Product Search Box -

Magento/PHP - Modifying the MySQL Queries Underlying the Front-End Product Search Box -

currently, mysql database queries supply results product search field on front end end seem utilize "or" linking criteria in clause of queries.

the reason assume using "or" because if search "green , reddish plaid shirt", every product "red" (including "bored", "stored", etc), every product "green", every product "plaid", , every product "shirt".

now if can find out in code queries beingness constructed, should able alter "and" , end queries this:

select `product_id` `products` `search_index` '%red%' , `search_index` '%green%' , `search_index` '%plaid%' , `search_index` '%shirt%';

i haven't been able find info searching google or magento's forums. i've been poking around app/code/core/mage/catalogsearch/ have not found mother lode yet. know there zend interface should mess haven't found yet.

thanks in advance

update

the below reply not seem work magento 1.7+, since they've changed of search code. i'm working on solution , update later.

i'm going reply own question. thanks, anton s clues there located key files myself , able implement changes wanted.

here key file: app/code/core/mage/catalogsearch/model/mysql4/fulltext.php

you re-create core construction leads file local structure, , re-create core file there well, so: app/code/local/mage/catalogsearch/model/mysql4/fulltext.php

then create changes local file, leaving core file alone.

look bit of code around line 315, within function prepareresult($object, $querytext, $query):

foreach($words $word) { $like[ ] = '`s`.`data_index` :likew' . $likei; $bind[':likew' . $likei] = '%' . $word . '%'; $likei ++; } if ($like) { $likecond = '(' . join(' or ', $like). ')'; }

that ' or ' there giving me thousands of useless results. example, search "green , reddish plaid shirt" end showing me things green, red, and/or plaid (including shirts, skirts, blimps, rabbits), every single shirt in store. user wants find product contains search terms. noted above, find results "bored" , "stored" because contain "red."

to solve of problem, have alter ' or ' ' , '. note alter applies "like" type searches, not "fulltext" type. fulltext doesn't work in magento because excludes way many results. method outlined below much better.

to create changes:

save file alter above. go admin, system->catalog->catalog search , create sure search type "like". save configuration

in admin, go system->index management, , check box next catalog search index , reindex (or reindex all). (or command line in magento root type:

php shell/indexer.php --reindex catalogsearch_fulltext

)

if want exclude words "bored" when searching "red", might want implement 2nd alter in same file.

there section of code within reads:

$bind[':likew' . $likei] = '%' . $word . '%';

the % @ front end means "bored" %red%. can't remove 1st % right effect because of way index constructed. instead create these 2 changes:

change above line of code to:

$bind[':likew' . $likei] = '% ' . $word . '%';

note space after first % before closing quote. find words start $word (e.g. red, redding, reddy, rediculous match '% red%'), have ensure words have spaces before them.

near top of file, under class mage_catalogsearch_model_mysql4_fulltext, around line 48 should find this:

protected $_separator = '|';

i changed this:

protected $_separator = ' | ';

putting spaces on both sides of pipe. when reindex, there spaces before , after every word. search "kit" still give results "kitchen", @ to the lowest degree won't results "skit".

finally, 1 lastly alter made ensure plural searches homecoming same results singular searches, @ to the lowest degree plurals ending in 's'. added line of code indicated:

foreach($words $word) { $word = rtrim($word, 's'); //this line added $like[ ] = '`s`.`data_index` :likew' . $likei; $bind[':likew' . $likei] = '%' . $word . '%'; $likei ++; }

it chops 's' off end of every word, "red , greenish plaid shirts" returns same results "reds ands greens plaids shirt".

my next project may create more changes string parsing improve results multi-word searches. i'm looking @ file, fyi: app/code/core/mage/core/helper/string.php

function splitwords

,which used in fulltext.php file string parsing.

note: create alter upgrade-safe, duplicate folder construction past app/code/core within app/code/local, this: app/code/local/mage/catalogsearch/model/mysql4/fulltext.php

just re-create core file there, , create changes there.

php mysql zend-framework magento