Jump to content
dev osCommerce
Forum
  • Checkout
  • Login
  • Contact Us
  • Get in touch

osCommerce

The e-commerce.

New Demo
Our Partners

Searching and use of MySQL keywords in search text

ianric

By ianric
in General Support

Recommended Posts

ianric

ianric

Posted
Posted

Hi

 

Hopefully this is easy for someone to answer. I'm adding more stock to my music site and testing offline (RC2.2a) as I go. If I search for a record titles that start with "AND" or "OR", as in "And I Ran" or "Or Lose Me" (no quotes) I get the classic sql 1064 error

 

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'or (pd.products_name like '%lose%' or p.products_model like '%lose%' or p.produc' at line 1

 

My Live site is MS2.2 and that displays an OSC "invalid keywords" error. Putting them in quotes works but not all customers will know that.

 

How can I make the search function accept these 2 legit titles with no errors

 

Many thanks

 

Ian

Link to comment
Share on other sites
MrPhil

MrPhil

Posted
Posted

The closest code I can find in 2.2 MS2 or 2.2 RC2a to what you show is in advanced_search_result.php:

       default:
         $keyword = tep_db_prepare_input($search_keywords[$i]);
         $where_str .= "(pd.products_name like '%" . tep_db_input($keyword) . "%' or p.products_model like '%" . tep_db_input($keyword) . "%' or m.manufacturers_name like '%" . tep_db_input($keyword) . "%'";
         if (isset($HTTP_GET_VARS['search_in_description']) && ($HTTP_GET_VARS['search_in_description'] == '1')) $where_str .= " or pd.products_description like '%" . tep_db_input($keyword) . "%'";
         $where_str .= ')';
         break;

You must have an add-on that modified this section of code. Anyway, my guess would be that you've got a search term that you're looking for, that is not enclosed in quotes. Please list the section of code in your copy of advanced_search_result.php so we can see how it differs, and give the complete error message including the file name and line number, to confirm we're looking at the right place.

Link to comment
Share on other sites
ianric

ianric

Posted
  • Author
Posted

Hi

 

Thanks for the reply. You are right, there is something in my advanced_search_result.php file stopping it. I replaced it with the stock version and got the "invalid keywords" error then the rest of the page displaying price to and from, date to and from etc.

 

I've added so that if one item is found, it displays that item in product_info instead of the list then you pick on it. I've also added my own database fields to the search and also the "nay header tags" contrib for a more thorough search. Also, a tweak so that if no text is in the search box, display all products

 

 

You're right again, I'm trying to search for "or lose me" WITHOUT the quotes

 

The URL to the page is

 

http://localhost/advanced_search_result.php?keywords=or+lose+me&osCsid=jqduaaj39heo3rcfnlqi17epg6&x=45&y=13

 

Works fine for every other product and the error is

 

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'or (pd.products_name like '%lose%' or p.products_model like '%lose%' or p.produc' at line 1

select count(distinct p.products_id) as total from products p left join manufacturers m using(manufacturers_id) left join specials s on p.products_id = s.products_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 ( or (pd.products_name like '%lose%' or p.products_model like '%lose%' or p.products_title like '%lose%' or p.products_label like '%lose%' or pd.products_hdescription like '%lose%' or pd.products_hkeywords like '%lose%') and (pd.products_name like '%me%' or p.products_model like '%me%' or p.products_title like '%me%' or p.products_label like '%me%' or pd.products_hdescription like '%me%' or pd.products_hkeywords like '%me%') )

[TEP STOP]

 

My advanced_search_result.php is below

 

Many thanks

 

Ian

 

<?php
/*
 $Id: advanced_search_result.php 1739 2007-12-20 00:52:16Z hpdl $

 osCommerce, Open Source E-Commerce Solutions
 http://www.oscommerce.com

 Copyright (c) 2003 osCommerce

 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 no text is in the search box, display all products
 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_SEARCH, tep_get_all_get_params(), 'NONSSL', true, false));
 }
*/
// 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,
                       'PRODUCT_LIST_BUY_NOW' => PRODUCT_LIST_BUY_NOW);

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_date_added, p.products_status, p.products_id, pd.products_name, pd.products_title, m.manufacturers_name, p.products_price, p.products_media, p.products_label, p.products_catno, p.products_model, p.products_quickfind, p.products_category, 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) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id";

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 . "')";
}

$from_str .= ", " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";

$hiddenlist = "";
if (!empty($hiddencats)) {
$hiddenlist = "and (not p2c.categories_id in (" . implode(',', $hiddencats) . ")) ";
}

$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 " . $hiddenlist;

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) . "%' or p.products_model like '%" . tep_db_input($keyword) . "%' or p.products_title like '%" . addslashes($search_keywords[$i]) . "%' or p.products_label like '%" . addslashes($search_keywords[$i]) . "%' or pd.products_hdescription like  '%" . addslashes($search_keywords[$i]) . "%' or pd.products_hkeywords like  '%" . addslashes($search_keywords[$i]) . "%'";
				if (isset($HTTP_GET_VARS['search_in_description']) && ($HTTP_GET_VARS['search_in_description'] == '1')) $where_str .= " or pd.products_description like '%" . tep_db_input($keyword) . "%' or p.products_title like '%" . addslashes($search_keywords[$i]) . "%' or p.products_label like '%" . addslashes($search_keywords[$i]) . "%' or pd.products_hdescription like  '%" . addslashes($search_keywords[$i]) . "%' or pd.products_hkeywords like  '%" . addslashes($search_keywords[$i]) . "%'";

//					$where_str .= "(pd.products_name like '%" . tep_db_input($keyword) . "%' or p.products_model like '%" . tep_db_input($keyword) . "%' or m.manufacturers_name like '%" . tep_db_input($keyword) . "%'";
//					if (isset($HTTP_GET_VARS['search_in_description']) && ($HTTP_GET_VARS['search_in_description'] == '1')) $where_str .= " or pd.products_description 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'])) || (!preg_match('/[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, pd.products_title';
			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, pd.products_title " . ($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;

// Show Product If Only 1 Result Mod
$listing_split = new splitPageResults($listing_sql, MAX_DISPLAY_SEARCH_RESULTS, 'p.products_id');

if($listing_split->number_of_rows == 1) {
	$product_query = tep_db_query($listing_split->sql_query);
	$product = tep_db_fetch_array($product_query);

	tep_redirect(tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $product['products_id']));
}
// Show Product If Only 1 Result Mod
 $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 HTML 4.01 Transitional//EN">
<html <?php echo HTML_PARAMS; ?>>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">
<meta name="robots" content="none">
<base href="<?php echo (($request_type == 'SSL') ? HTTPS_SERVER : HTTP_SERVER) . DIR_WS_CATALOG; ?>">
<title><?php echo TITLE; ?> <?php echo substr(strip_tags($breadcrumb->trail(' » ')),4); ?></title>
<link rel="stylesheet" type="text/css" href="stylesheet.css">
</head>
<body>
<!-- header //-->
<?php require(DIR_WS_INCLUDES . 'header.php'); ?>
<!-- header_eof //-->

<!-- body //-->
<table border="0" width="100%" cellspacing="3" cellpadding="3">
 <tr>
   <td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="0" cellpadding="2">
<!-- left_navigation //-->
<?php require(DIR_WS_INCLUDES . 'column_left.php'); ?>
<!-- left_navigation_eof //-->
   </table></td>
<!-- body_text //-->
   <td width="100%" valign="top" class="main-content"><table border="0" width="100%" cellspacing="0" cellpadding="0">
     <tr>
       <td><table border="0" width="100%" cellspacing="0" cellpadding="2">
             <tr>
       <td class="main"><table align="center" width="100%" border="0" cellspacing="0" cellpadding="0">
               <tr>
<td class="headerYouarehere">You are here: <?php echo $breadcrumb->trail(' <img border="0" src="images/right_arr.gif" width="9" height="8"> '); ?></td>
</tr>
     <tr>
       <td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>
     </tr>
                     <tr height="24">
               <td class="main" bgcolor="#ebedf1"><?php echo tep_draw_separator('pixel_trans.gif', '2', '0'); ?><b><?php

               if (!stripslashes($HTTP_GET_VARS['keywords'])) {
               	echo TEXT_NO_SEARCH_RESULTS;
               } else { echo HEADING_TITLE_1 . stripslashes($HTTP_GET_VARS['keywords']);
               	}
?>

		</b></td>
             </tr>
             <tr>
               <td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>
             </tr>
             </table>

         <tr>
           <td class="pageHeading"><?php //echo HEADING_TITLE_2; ?></td>

         </tr>

       </table></td>
     </tr>
       <td>
<?php


 require(DIR_WS_MODULES . FILENAME_PRODUCT_LISTING);
?>
       </td>
     </tr>
     <tr>
       <td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>
     </tr>
   </table></td>
<!-- body_text_eof //-->
   <td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="0" cellpadding="2">
<!-- right_navigation //-->
<?php require(DIR_WS_INCLUDES . 'column_right.php'); ?>
<!-- right_navigation_eof //-->
   </table></td>
 </tr>
</table>
<!-- body_eof //-->

<!-- footer //-->
<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>
<!-- footer_eof //-->

</body>
</html>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>

Link to comment
Share on other sites
MrPhil

MrPhil

Posted
Posted

( ) OR AND are keywords that advanced search permits.

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

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

This creates $search_keywords from $keywords.

 

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) . "%' or p.products_model like '%" . tep_db_input($keyword) . "%' or p.products_title like '%" . addslashes($search_keywords[$i]) . "%' or p.products_label like '%" . addslashes($search_keywords[$i]) . "%' or pd.products_hdescription like  '%" . addslashes($search_keywords[$i]) . "%' or pd.products_hkeywords like  '%" . addslashes($search_keywords[$i]) . "%'";
                                       if (isset($HTTP_GET_VARS['search_in_description']) && ($HTTP_GET_VARS['search_in_description'] == '1')) $where_str .= " or pd.products_description like '%" . tep_db_input($keyword) . "%' or p.products_title like '%" . addslashes($search_keywords[$i]) . "%' or p.products_label like '%" . addslashes($search_keywords[$i]) . "%' or pd.products_hdescription like  '%" . addslashes($search_keywords[$i]) . "%' or pd.products_hkeywords like  '%" . addslashes($search_keywords[$i]) . "%'";

//                                      $where_str .= "(pd.products_name like '%" . tep_db_input($keyword) . "%' or p.products_model like '%" . tep_db_input($keyword) . "%' or m.manufacturers_name like '%" . tep_db_input($keyword) . "%'";
//                                      if (isset($HTTP_GET_VARS['search_in_description']) && ($HTTP_GET_VARS['search_in_description'] == '1')) $where_str .= " or pd.products_description like '%" . tep_db_input($keyword) . "%'";
                                       $where_str .= ')';
                                       break;
                       }
               }
               $where_str .= " )";
       }

This puts parentheses, AND, and OR directly into the query. That apparently is what happened to you, to create

= c.categories_id and ( or (pd.products_name like

It would be missing the search term before "or".

 

If your users will never be using advanced search capability (AND, OR, parentheses), you might disable that part of the code:

                             /* case '(':
                               case ')':
                               case 'and':
                               case 'or':
                                       $where_str .= " " . $search_keywords[$i] . " ";
                                       break; */

I think that will at least keep OR and AND from being treated specially. Or, you could use | and & instead (requires code changes).

 

Now, it appears that it will still break up your search terms at spaces and make them LIKE matches, separated by "or". So Or Lose Me will look for LIKE "%Or%" OR LIKE "%Lose%" OR LIKE "%Me%". So even if OR is not treated specially, without quotes it's not going to look for LIKE "%Or Lose Me%". All in all, your users are simply going to have to learn to use quotation marks around multi-word terms, regardless of whether special words like OR and AND were in there. I suppose you could change OR and AND to | and &, and glue other blank-separated keywords into single strings (or don't split them apart in the first place: see tep_parse_search_string() ).

 

So, it can probably be dealt with.

Link to comment
Share on other sites
ianric

ianric

Posted
  • Author
Posted

( ) OR AND are keywords that advanced search permits.

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

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

This creates $search_keywords from $keywords.

 

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) . "%' or p.products_model like '%" . tep_db_input($keyword) . "%' or p.products_title like '%" . addslashes($search_keywords[$i]) . "%' or p.products_label like '%" . addslashes($search_keywords[$i]) . "%' or pd.products_hdescription like  '%" . addslashes($search_keywords[$i]) . "%' or pd.products_hkeywords like  '%" . addslashes($search_keywords[$i]) . "%'";
                                       if (isset($HTTP_GET_VARS['search_in_description']) && ($HTTP_GET_VARS['search_in_description'] == '1')) $where_str .= " or pd.products_description like '%" . tep_db_input($keyword) . "%' or p.products_title like '%" . addslashes($search_keywords[$i]) . "%' or p.products_label like '%" . addslashes($search_keywords[$i]) . "%' or pd.products_hdescription like  '%" . addslashes($search_keywords[$i]) . "%' or pd.products_hkeywords like  '%" . addslashes($search_keywords[$i]) . "%'";

//                                      $where_str .= "(pd.products_name like '%" . tep_db_input($keyword) . "%' or p.products_model like '%" . tep_db_input($keyword) . "%' or m.manufacturers_name like '%" . tep_db_input($keyword) . "%'";
//                                      if (isset($HTTP_GET_VARS['search_in_description']) && ($HTTP_GET_VARS['search_in_description'] == '1')) $where_str .= " or pd.products_description like '%" . tep_db_input($keyword) . "%'";
                                       $where_str .= ')';
                                       break;
                       }
               }
               $where_str .= " )";
       }

This puts parentheses, AND, and OR directly into the query. That apparently is what happened to you, to create

= c.categories_id and ( or (pd.products_name like

It would be missing the search term before "or".

 

If your users will never be using advanced search capability (AND, OR, parentheses), you might disable that part of the code:

                             /* case '(':
                               case ')':
                               case 'and':
                               case 'or':
                                       $where_str .= " " . $search_keywords[$i] . " ";
                                       break; */

I think that will at least keep OR and AND from being treated specially. Or, you could use | and & instead (requires code changes).

 

Now, it appears that it will still break up your search terms at spaces and make them LIKE matches, separated by "or". So Or Lose Me will look for LIKE "%Or%" OR LIKE "%Lose%" OR LIKE "%Me%". So even if OR is not treated specially, without quotes it's not going to look for LIKE "%Or Lose Me%". All in all, your users are simply going to have to learn to use quotation marks around multi-word terms, regardless of whether special words like OR and AND were in there. I suppose you could change OR and AND to | and &, and glue other blank-separated keywords into single strings (or don't split them apart in the first place: see tep_parse_search_string() ).

 

So, it can probably be dealt with.

 

Wow!! How mind blowing is that?? Sorry Phil, I couldn't understand it but...

 

I fixed it. My search file was all over the place. What messed it up was the code to view the product if only one search item is found contrib. I must have done it wrong and it's only showed up now. It was bypassing code which is why the SQL error showed. I used the search file in the contrib and re-applied some mods I had made and now it works. It now shows the "invalid keywords" error when AND or OR are at the beginning of the search and I've added text to say search with quotes, the usual search help

 

Thanks for your help

 

Ian

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...