Search problem

[ChilliNr1]

Hi,

I`ve got the following problem when I enter some keywords in the search field.

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 'from products p left join manufacturers m using(manufacturers_id) left join spec' at line 1

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_id = pd.products_id and pd.language_id = '2' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%blume%' or p.products_model like '%blume%' or m.manufacturers_name like '%blume%') ) order by pd.products_name limit 0, 20

[TEP STOP]

What does it mean? I`m not that deep into SQL.

I tried to recover an old backup, but I`ve to go back to the beginning of the shop to make it work...

If you want to try, goto my page!

Type in any keyword like: skyline for example...

Thank you for your attention!

[ChilliNr1]

Hi,

I`ve found out, if you find ONE product like "flora" for example,

it normally would show you directly the product_info page,

but the only thing that shows up is the error!

If someone has an idea, please let me know!

If you need more information, files or anything else, also!

Big thanks!

[ELnew]

What version of osC are you using?

[ChilliNr1]

What version of osC are you using?

Hi,

I`m using the following settings and versions:

Store Version: osCommerce 2.2-MS2

Server Host: w29.goneo.de (82.100.220.59)

Server OS: FreeBSD 6.3-RELEASE

Datenbank: MySQL 5.0.37

HTTP Server: Apache/2.0.63

PHP Version: 4.4.9 (Zend: 1.3.0)

Do you have an idea?

Thanks!

[ChilliNr1]

Hi,

here is the code which causes the problems.

Maybe this helps a little...

<?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 (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));
 }

 // Show Product If Only Result Mod
 // 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;
}
 }

 /** show-soldout-v1.0 **/
if(strpos($select_column_list, "p.products_quantity")==false)
$select_column_list .= 'p.products_quantity, ';
$select_column_list .= 'p.products_status, '; 
/** end show-soldout-v1.0 **/

 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";

 /** original **///$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 ";
/** show-soldout-v1.0 **/ $where_str = " where 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) . "%' 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'])) || (!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;

 $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 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; ?>">
<base href="<?php echo (($request_type == 'SSL') ? HTTPS_SERVER : HTTP_SERVER) . DIR_WS_CATALOG; ?>">
<title><?php echo TITLE; ?></title>
<link rel="stylesheet" type="text/css" href="stylesheet.css">
<?php include(DIR_WS_MODULES . FILENAME_COOLIRIS_HEADER); ?>
</head>
<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0">
<!-- 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"><table border="0" width="100%" cellspacing="0" cellpadding="0">
  <tr>
	<td><table border="0" width="100%" cellspacing="0" cellpadding="0">
	  <tr>
		<td class="pageHeading"><?php echo HEADING_TITLE_2; ?></td>
		<td class="pageHeading" align="right"><?php echo tep_image(DIR_WS_IMAGES . 'table_background_browse.gif', HEADING_TITLE_2, HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>
	  </tr>
	</table></td>
  </tr>
  <tr>
	<td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>
  </tr>
  <tr>
	<td>
<?php
 /*
Show Product If Only Result Mod, code block moved
// create column list
..
$listing_sql = $select_str . $from_str . $where_str . $order_str;
 */

 require(DIR_WS_MODULES . FILENAME_PRODUCT_LISTING);
?>
	</td>
  </tr>
  <tr>
	<td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>
  </tr>
  <tr>
	<td class="main"><?php echo '<a href="' . tep_href_link(FILENAME_ADVANCED_SEARCH, tep_get_all_get_params(array('sort', 'page')), 'NONSSL', true, false) . '">' . tep_image_button('button_back.gif', IMAGE_BUTTON_BACK) . '</a>'; ?></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 //-->
<br>
</body>
</html>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>

Greetings!

[ChilliNr1]

No idea anyone? :blink:

[♥geoffreywalton]

From http://dev.mysql.com/doc/refman/5.0/en/lef...timization.html

 

User Comments

Posted by Peter Warnock on January 12 2005 10:46pm [Delete] [Edit]

 

When using more than one left join, the first occurrence of NULL will cause latter fields to be reported NULL with the USING(field) clause.

 

instead of :

t1 LEFT JOIN t2 USING (id) LEFT JOIN t3 USING (id)

 

use:

t1 LEFT JOIN t2 ON (t1.id = t2.id) LEFT JOIN t3 ON (t1.id=t3.id)

[ChilliNr1]

Hi,

thanks for your answer,

but can you exactly tell me what to change?

Sorry, I`ve found the part to change, but I don`t know how... :rolleyes:

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

Thank you!

[ChilliNr1]

Hi,

is here anyone who can change the code into a working one?! PLEASE!!! :blink:

This is so important for me to get the search function back to work!

THANKS!

[ChilliNr1]

Hi there,

sorry for pushing, but this is still a big problem for me!

I need to get the search run again! :(

Can anyone help me fix the code?

Big thanks!

[ChilliNr1]

Hi,

looks like I deleted one line: [code $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 ";

][/code]

That causes the error!

Now it`s done!

Greetings!