Help make set minimum character for OSCommerce search, example set for 4 character

[basketmen]

Hi guys, below is default OSCommerce complete search file, the file name is advanced_search_result.php

oscommerce is very free famous script, but..

ironically this file is searching all words that typed by user, even just a single word like "A" or "An"
it is searching in mysql, so its make very very high load for the server, imagine its need to search for all words that contain "A", how if you have 100.000 products/items in your site :rolleyes:


maybe the best option to reduce the load server is to set the minimum character to search, example to search only minimum 4 or more character, there is no setting for doing this in oscommerce setting, so its need to change some code in a file, i had asking this in oscommerce forum, but they cant do this http://forums.oscommerce.com/index.php?showtopic=340368




i think the part need to edit is in bold below, please help guys to make it only search 4 or more character words






here is advanced_search_result.php file content :

<?php
/*
$Id: advanced_search_result.php,v 1.72 2003/06/23 06:50:11 project3000 Exp $

E-Commerce Solutions

Copyright (c) 2005 www.flash-template-design.com

Released under the GNU General Public License
*/

require('includes/application_top.php');

require(DIR_WS_LANGUAGES . $language . '/' . FILENAME_ADVANCED_SEARCH);

$error = false;

if ( (isset($HTTP_GET_VARS['keywords']) && empty($HTTP_GET_VARS['keywords'])) &&
(isset($HTTP_GET_VARS['dfrom']) && (empty($HTTP_GET_VARS['dfrom']) || ($HTTP_GET_VARS['dfrom'] == DOB_FORMAT_STRING))) &&
(isset($HTTP_GET_VARS['dto']) && (empty($HTTP_GET_VARS['dto']) || ($HTTP_GET_VARS['dto'] == DOB_FORMAT_STRING))) &&
(isset($HTTP_GET_VARS['pfrom']) && !is_numeric($HTTP_GET_VARS['pfrom'])) &&
(isset($HTTP_GET_VARS['pto']) && !is_numeric($HTTP_GET_VARS['pto'])) ) {
$error = true;

$messageStack->add_session('search', ERROR_AT_LEAST_ONE_INPUT);
} else {
$dfrom = '';
$dto = '';
$pfrom = '';
$pto = '';
$keywords = '';

if (isset($HTTP_GET_VARS['dfrom'])) {
$dfrom = (($HTTP_GET_VARS['dfrom'] == DOB_FORMAT_STRING) ? '' : $HTTP_GET_VARS['dfrom']);
}

if (isset($HTTP_GET_VARS['dto'])) {
$dto = (($HTTP_GET_VARS['dto'] == DOB_FORMAT_STRING) ? '' : $HTTP_GET_VARS['dto']);
}

if (isset($HTTP_GET_VARS['pfrom'])) {
$pfrom = $HTTP_GET_VARS['pfrom'];
}

if (isset($HTTP_GET_VARS['pto'])) {
$pto = $HTTP_GET_VARS['pto'];
}

if (isset($HTTP_GET_VARS['keywords'])) {
$keywords = $HTTP_GET_VARS['keywords'];
}

$date_check_error = false;
if (tep_not_null($dfrom)) {
if (!tep_checkdate($dfrom, DOB_FORMAT_STRING, $dfrom_array)) {
$error = true;
$date_check_error = true;

$messageStack->add_session('search', ERROR_INVALID_FROM_DATE);
}
}

if (tep_not_null($dto)) {
if (!tep_checkdate($dto, DOB_FORMAT_STRING, $dto_array)) {
$error = true;
$date_check_error = true;

$messageStack->add_session('search', ERROR_INVALID_TO_DATE);
}
}

if (($date_check_error == false) && tep_not_null($dfrom) && tep_not_null($dto)) {
if (mktime(0, 0, 0, $dfrom_array[1], $dfrom_array[2], $dfrom_array[0]) > mktime(0, 0, 0, $dto_array[1], $dto_array[2], $dto_array[0])) {
$error = true;

$messageStack->add_session('search', ERROR_TO_DATE_LESS_THAN_FROM_DATE);
}
}

$price_check_error = false;
if (tep_not_null($pfrom)) {
if (!settype($pfrom, 'double')) {
$error = true;
$price_check_error = true;

$messageStack->add_session('search', ERROR_PRICE_FROM_MUST_BE_NUM);
}
}

if (tep_not_null($pto)) {
if (!settype($pto, 'double')) {
$error = true;
$price_check_error = true;

$messageStack->add_session('search', ERROR_PRICE_TO_MUST_BE_NUM);
}
}

if (($price_check_error == false) && is_float($pfrom) && is_float($pto)) {
if ($pfrom >= $pto) {
$error = true;

$messageStack->add_session('search', ERROR_PRICE_TO_LESS_THAN_PRICE_FROM);
}
}

if (tep_not_null($keywords)) {
if (!tep_parse_search_string($keywords, $search_keywords)) {
$error = true;

$messageStack->add_session('search', ERROR_INVALID_KEYWORDS);
}
}
}

if (empty($dfrom) && empty($dto) && empty($pfrom) && empty($pto) && empty($keywords)) {
$error = true;

$messageStack->add_session('search', ERROR_AT_LEAST_ONE_INPUT);
}

if ($error == true) {
tep_redirect(tep_href_link(FILENAME_ADVANCED_SEARC H, tep_get_all_get_params(), 'NONSSL', true, false));
}

$breadcrumb->add(NAVBAR_TITLE_1, tep_href_link(FILENAME_ADVANCED_SEARCH));
$breadcrumb->add(NAVBAR_TITLE_2, tep_href_link(FILENAME_ADVANCED_SEARCH_RESULT, tep_get_all_get_params(), 'NONSSL', true, false));
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html <?php echo HTML_PARAMS; ?>>
<head>
<link rel="stylesheet" type="text/css" href="style.css">
</head>
<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0">


<?php
// create column list
$define_list = array('PRODUCT_LIST_MODEL' => PRODUCT_LIST_MODEL,
'PRODUCT_LIST_NAME' => PRODUCT_LIST_NAME,
'PRODUCT_LIST_MANUFACTURER' => PRODUCT_LIST_MANUFACTURER,
'PRODUCT_LIST_PRICE' => PRODUCT_LIST_PRICE,
'PRODUCT_LIST_QUANTITY' => PRODUCT_LIST_QUANTITY,
'PRODUCT_LIST_WEIGHT' => PRODUCT_LIST_WEIGHT,
'PRODUCT_LIST_IMAGE' => PRODUCT_LIST_IMAGE);

asort($define_list);

$column_list = array();
reset($define_list);
while (list($key, $value) = each($define_list)) {
if ($value > 0) $column_list[] = $key;
}

$select_column_list = '';

for ($i=0, $n=sizeof($column_list); $i<$n; $i++) {
switch ($column_list[$i]) {
case 'PRODUCT_LIST_MODEL':
$select_column_list .= 'p.products_model, ';
break;
case 'PRODUCT_LIST_MANUFACTURER':
$select_column_list .= 'm.manufacturers_name, ';
break;
case 'PRODUCT_LIST_QUANTITY':
$select_column_list .= 'p.products_quantity, ';
break;
case 'PRODUCT_LIST_IMAGE':
$select_column_list .= 'p.products_image, ';
break;
case 'PRODUCT_LIST_WEIGHT':
$select_column_list .= 'p.products_weight, ';
break;
}
}

$select_str = "select distinct " . $select_column_list . " m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price ";

if ( (DISPLAY_PRICE_WITH_TAX == 'true') && (tep_not_null($pfrom) || tep_not_null($pto)) ) {
$select_str .= ", SUM(tr.tax_rate) as tax_rate ";
}

$from_str = "from ((" . TABLE_PRODUCTS . " p) left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";
if ( (DISPLAY_PRICE_WITH_TAX == 'true') && (tep_not_null($pfrom) || tep_not_null($pto)) ) {
if (!tep_session_is_registered('customer_country_id') ) {
$customer_country_id = STORE_COUNTRY;
$customer_zone_id = STORE_ZONE;
}
$from_str .= " left join " . TABLE_TAX_RATES . " tr on p.products_tax_class_id = tr.tax_class_id left join " . TABLE_ZONES_TO_GEO_ZONES . " gz on tr.tax_zone_id = gz.geo_zone_id and (gz.zone_country_id is null or gz.zone_country_id = '0' or gz.zone_country_id = '" . (int)$customer_country_id . "') and (gz.zone_id is null or gz.zone_id = '0' or gz.zone_id = '" . (int)$customer_zone_id . "')";
}

$where_str = " where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id ";

if (isset($HTTP_GET_VARS['categories_id']) && tep_not_null($HTTP_GET_VARS['categories_id'])) {
if (isset($HTTP_GET_VARS['inc_subcat']) && ($HTTP_GET_VARS['inc_subcat'] == '1')) {
$subcategories_array = array();
tep_get_subcategories($subcategories_array, $HTTP_GET_VARS['categories_id']);

$where_str .= " and p2c.products_id = p.products_id and p2c.products_id = pd.products_id and (p2c.categories_id = '" . (int)$HTTP_GET_VARS['categories_id'] . "'";

for ($i=0, $n=sizeof($subcategories_array); $i<$n; $i++ ) {
$where_str .= " or p2c.categories_id = '" . (int)$subcategories_array[$i] . "'";
}

$where_str .= ")";
} else {
$where_str .= " and p2c.products_id = p.products_id and p2c.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['categories_id'] . "'";
}
}

if (isset($HTTP_GET_VARS['manufacturers_id']) && tep_not_null($HTTP_GET_VARS['manufacturers_id'])) {
$where_str .= " and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";
}

if (isset($search_keywords) && (sizeof($search_keywords) > 0)) {
$where_str .= " and (";
for ($i=0, $n=sizeof($search_keywords); $i<$n; $i++ ) {
switch ($search_keywords[$i]) {
case '(':
case ')':
case 'and':
case 'or':
$where_str .= " " . $search_keywords[$i] . " ";
break;
default:
$keyword = tep_db_prepare_input($search_keywords[$i]);
$where_str .= "(pd.products_name like '%" . tep_db_input($keyword) . "%'";
$where_str .= ')';
break;
}
}
$where_str .= " )";
}
if (tep_not_null($dfrom)) {
$where_str .= " and p.products_date_added >= '" . tep_date_raw($dfrom) . "'";
}

if (tep_not_null($dto)) {
$where_str .= " and p.products_date_added <= '" . tep_date_raw($dto) . "'";
}

if (tep_not_null($pfrom)) {
if ($currencies->is_set($currency)) {
$rate = $currencies->get_value($currency);

$pfrom = $pfrom / $rate;
}
}

if (tep_not_null($pto)) {
if (isset($rate)) {
$pto = $pto / $rate;
}
}

if (DISPLAY_PRICE_WITH_TAX == 'true') {
if ($pfrom > 0) $where_str .= " and (IF(s.status, s.specials_new_products_price, p.products_price) * if(gz.geo_zone_id is null, 1, 1 + (tr.tax_rate / 100) ) >= " . (double)$pfrom . ")";
if ($pto > 0) $where_str .= " and (IF(s.status, s.specials_new_products_price, p.products_price) * if(gz.geo_zone_id is null, 1, 1 + (tr.tax_rate / 100) ) <= " . (double)$pto . ")";
} else {
if ($pfrom > 0) $where_str .= " and (IF(s.status, s.specials_new_products_price, p.products_price) >= " . (double)$pfrom . ")";
if ($pto > 0) $where_str .= " and (IF(s.status, s.specials_new_products_price, p.products_price) <= " . (double)$pto . ")";
}

if ( (DISPLAY_PRICE_WITH_TAX == 'true') && (tep_not_null($pfrom) || tep_not_null($pto)) ) {
$where_str .= " group by p.products_id, tr.tax_priority";
}

if ( (!isset($HTTP_GET_VARS['sort'])) || (!ereg('[1-8][ad]', $HTTP_GET_VARS['sort'])) || (substr($HTTP_GET_VARS['sort'], 0, 1) > sizeof($column_list)) ) {
for ($i=0, $n=sizeof($column_list); $i<$n; $i++) {
if ($column_list[$i] == 'PRODUCT_LIST_NAME') {
$HTTP_GET_VARS['sort'] = $i+1 . 'a';
$order_str = ' order by pd.products_name';
break;
}
}
} else {
$sort_col = substr($HTTP_GET_VARS['sort'], 0 , 1);
$sort_order = substr($HTTP_GET_VARS['sort'], 1);
$order_str = ' order by ';
switch ($column_list[$sort_col-1]) {
case 'PRODUCT_LIST_MODEL':
$order_str .= "p.products_model " . ($sort_order == 'd' ? "desc" : "") . ", pd.products_name";
break;
case 'PRODUCT_LIST_NAME':
$order_str .= "pd.products_name " . ($sort_order == 'd' ? "desc" : "");
break;
case 'PRODUCT_LIST_MANUFACTURER':
$order_str .= "m.manufacturers_name " . ($sort_order == 'd' ? "desc" : "") . ", pd.products_name";
break;
case 'PRODUCT_LIST_QUANTITY':
$order_str .= "p.products_quantity " . ($sort_order == 'd' ? "desc" : "") . ", pd.products_name";
break;
case 'PRODUCT_LIST_IMAGE':
$order_str .= "pd.products_name";
break;
case 'PRODUCT_LIST_WEIGHT':
$order_str .= "p.products_weight " . ($sort_order == 'd' ? "desc" : "") . ", pd.products_name";
break;
case 'PRODUCT_LIST_PRICE':
$order_str .= "final_price " . ($sort_order == 'd' ? "desc" : "") . ", pd.products_name";
break;
}
}

$listing_sql = $select_str . $from_str . $where_str . $order_str;

require('includes/modules/product_listing2.php');

?>




</body>
</html>

I had try change to red below, but no one working

if (isset($search_keywords) && (sizeof($search_keywords) > 4)) {
$where_str .= " and (";
for ($i=0, $n=sizeof($search_keywords); $i<$n; $i++ ) {
switch ($search_keywords[$i]) {
case '(':
case ')':
case 'and':
case 'or':
$where_str .= " " . $search_keywords[$i] . " ";
break;
default:
$keyword = tep_db_prepare_input($search_keywords[$i]);
$where_str .= "(pd.products_name like '%" . tep_db_input($keyword) . "%'";
$where_str .= ')';
break;
}
}

or

if (isset($search_keywords) && (sizeof($search_keywords) > 0)) {
$where_str .= " and (";
for ($i=4, $n=sizeof($search_keywords); $i<$n; $i++ ) {
switch ($search_keywords[$i]) {
case '(':
case ')':
case 'and':
case 'or':
$where_str .= " " . $search_keywords[$i] . " ";
break;
default:
$keyword = tep_db_prepare_input($search_keywords[$i]);
$where_str .= "(pd.products_name like '%" . tep_db_input($keyword) . "%'";
$where_str .= ')';
break;
}
}

or

if (isset($search_keywords) && (sizeof($search_keywords) > 0)) {
$where_str .= " and (";
for ($i=0, $n=sizeof($search_keywords); $i>$n; $i++ ) {
switch ($search_keywords[$i]) {
case '(':
case ')':
case 'and':
case 'or':
$where_str .= " " . $search_keywords[$i] . " ";
break;
default:
$keyword = tep_db_prepare_input($search_keywords[$i]);
$where_str .= "(pd.products_name like '%" . tep_db_input($keyword) . "%'";
$where_str .= ')';
break;
}
}

[Hades]

You had a good idea of the piece of code that needs to be modified. But you made a small mistake

Changing the $i from 0 to 4 does not change the length of the keywords, that counter is for the number of keywords processed, you might want to do this:

PHP Code:

if (isset($search_keywords) && (sizeof($search_keywords) > 4)) {
$where_str .= " and (";
for ($i=0, $n=sizeof($search_keywords); $i<$n; $i++ ) {

/*MAGIC*/
if (strlen($search_keywords[$i]) < 5){ continue; }
/*/MAGIC*/

switch ($search_keywords[$i]) {
case '(':
case ')':
case 'and':
case 'or':
$where_str .= " " . $search_keywords[$i] . " ";
break;
default:
$keyword = tep_db_prepare_input($search_keywords[$i]);
$where_str .= "(pd.products_name like '%" . tep_db_input($keyword) . "%'";
$where_str .= ')';
break;
}
} 


I only introduced one line, which is between /*MAGIC*/ and /*/MAGIC*/
That will make it skip the keywords that are less than 5 chars long.

Though I'm not sure if that will reduce the load of the database, you might also want to create indexes on your DB for the keywords fields and such.

[Blackpool]

I tried the above code from Hades and it works much better than the original posters one, brilliant! will save me time.

[basketmen]

so is it hades code working?