The original code only searched the first word of the product_name. The code below searches for all occurances of the search word within product_name.

Change line 18 from

$product_query = tep_db_query("select pd.products_name, p.products_id, p.products_price, p.products_quantity, p.products_model, p.products_weight from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and pd.products_name like '".$_REQUEST['prod_search']."%'");

to

$product_query = tep_db_query("select pd.products_name, p.products_id, p.products_price, p.products_quantity, p.products_model, p.products_weight from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and pd.products_name like '"."%".$_REQUEST['prod_search']."%'");


More...