Wasimasif's Blog

CRE Loaded provides a basic search where a product can be searched by product name, model and manufacturer. If selected search is also performed in product description. This search uses SQL “LIKE” syntax to search in text fields. For example is keyword “test” is search the query will be

select distinct p.products_image ... final_price from ( products p left join specials s using(products_id) ) left join manufacturers m on p.manufacturers_id = m.manufacturers_id , products_description pd, categories c, products_to_categories p2c where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%test%' or p.products_model like '%test%' or m.manufacturers_name like '%test%' or pd.products_description like '%test%') ) order by pd.products_name

The LIKE search can be very slow with large amount of products and specially when searching in description as well. To avoid this we can make this search to use fulltext searching feature of MySQL. The fulltext search will make searching in description much faster then normal like query. So lets start on fulltext search for CRE Loaded but before we start backup your files and database before you make any changes in case if any thing goes wrong, secondly changes mentioned worked for CRE Loaded pro 6.2 version but it should work with other versions as well.

First we need to create fulltext index on products table by following query.

ALTER TABLE products_description ADD FULLTEXT INDEX idx_name_desc (products_name, products_description);

Now to enable or disable fulltext search we need to add configuration so admin can do it easily. The query to add configuration is following.

INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id

, sort_order, last_modified, date_added, set_function)

VALUES ('Fulltext search', 'ADVANCED_SEARCH_FULLTEXT', 'false', 'Enable/Disable fulltext search', '1', '999', NOW(), NOW(), 'tep_cfg_select_option(array(\'true\',\'false\'),');

Now we need to add code for fulltext searching which will be added in file /templates/content/advanced_search_result.tpl.php. To do this open the above mentioned file and find

if (isset($search_keywords) && (sizeof($search_keywords) > 0)) {

$where_str .= " and (";

and add following code after it.

if(ADVANCED_SEARCH_FULLTEXT == 'true')

{

$ft_keyword = tep_db_input($keywords);

$where_str .= "MATCH(pd.products_name, pd.products_description) AGAINST('".$ft_keyword."' IN BOOLEAN MODE) OR p.products_model like '%".tep_db_input($keywords)."%'";

}

else

{

now find

$where_str .= " )";

and add code

}

before the code found.

Now the query should look like

select distinct p.products_image, ... final_price from ( products p left join specials s using(products_id) ) left join manufacturers m on p.manufacturers_id = m.manufacturers_id , products_description pd, categories c, products_to_categories p2c where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and (MATCH(pd.products_name, pd.products_description) AGAINST('test' IN BOOLEAN MODE) OR p.products_model like '%test%' )

In above query fulltext search will be performed in product name and description, the model will still be searched in LIKE mode, and manufacturer name will not be searched. With little effort model and manufacturer name can also be added to fulltext search.