Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Error on Searches


roback

Recommended Posts

All, I am getting the following error:

 

1066 - Not unique table/alias: 'pd'

 

select count(distinct p.products_id) as total from (products p left join products_to_products_extra_fields p2pef on p.products_id=p2pef.products_id) left join manufacturers m on m.manufacturers_id=p.manufacturers_id, products_description pd left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c, 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 '%huggies%' or p.products_model like '%huggies%' or m.manufacturers_name like '%huggies%' or p2pef.products_extra_fields_value like '%huggies%') )

 

When I do a search through the Search Box Anywhere Contribution AND also through the Advanced Search Page.

 

I do not know enough about php or mysql to figure out why this is happening or where to start and fix it. I can assume it has something to do with the 'pd' mentioned in the first line?

 

Please help!

Link to comment
Share on other sites

  • 9 months later...

All, I am getting the following error:

 

1066 - Not unique table/alias: 'pd'

 

select count(distinct p.products_id) as total from (products p left join products_to_products_extra_fields p2pef on p.products_id=p2pef.products_id) left join manufacturers m on m.manufacturers_id=p.manufacturers_id, products_description pd left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c, 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 '%huggies%' or p.products_model like '%huggies%' or m.manufacturers_name like '%huggies%' or p2pef.products_extra_fields_value like '%huggies%') )

 

When I do a search through the Search Box Anywhere Contribution AND also through the Advanced Search Page.

 

I do not know enough about php or mysql to figure out why this is happening or where to start and fix it. I can assume it has something to do with the 'pd' mentioned in the first line?

 

Please help!

 

Hi, I have the same problem, on my catalog/catalog_products_with_images_full.php page.

Seems to have sometinh to do with the table products_description or with products_name ??

Would also appreciate a solution for this :-)

Thanks!

Best regards

Kjolebutikken

Link to comment
Share on other sites

select count(distinct p.products_id) as total from (products p left join products_to_products_extra_fields p2pef on p.products_id=p2pef.products_id) left join manufacturers m on m.manufacturers_id=p.manufacturers_id, products_description pd left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c, 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 '%huggies%' or p.products_model like '%huggies%' or m.manufacturers_name like '%huggies%' or p2pef.products_extra_fields_value like '%huggies%') )

You've got the "products_description" table in twice. The first one is unnecessary and should be removed (it's not involved with the JOIN process). Don't forget the comma before it. It looks like maybe someone who didn't know what they were doing was thrashing about trying to fix a MySQL 5 "table not found" incompatibility (read http://www.oscommerce.com/forums/topic/335136-osc-and-mysql-5-1054-errors to understand the theory and not just blindly change code by rote).

Link to comment
Share on other sites

You've got the "products_description" table in twice. The first one is unnecessary and should be removed (it's not involved with the JOIN process). Don't forget the comma before it. It looks like maybe someone who didn't know what they were doing was thrashing about trying to fix a MySQL 5 "table not found" incompatibility (read http://www.oscommerce.com/forums/topic/335136-osc-and-mysql-5-1054-errors to understand the theory and not just blindly change code by rote).

 

 

Hi, how do I find out in which file and which code?

 

I have error:

 

1066 - Not unique table/alias: 'pd'

 

select count(distinct p.products_id) as total from (products p left join products_to_products_extra_fields p2pef on p.products_id=p2pef.products_id) left join manufacturers m on m.manufacturers_id=p.manufacturers_id, products_description pd left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c,products_attributes pa, 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 = '4' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and pa.products_id = p.products_id and pa.options_values_id in (14)

 

It comes up on my advanced search.

Have search both advanced_search.php and advanced_search_result.php for the word products_description, but can't find it twice in same code.

 

Could the code error be in another file?

 

By the way, in every way I try to use the search function, a 500 error page (internal server error) comes up. Only time the 1066 error comes up, is when presing link for advanced search.

 

 

Thanks for your help :-)

Best regards

Kjolebutikken

Link to comment
Share on other sites

The PHP code will have a defined table name, such as TABLE_PRODUCT_DESCRIPTION, rather than the MySQL table name of "product_description". Or maybe you'll have one TABLE_PRODUCT_DESCRIPTION and one hardcoded "product_description".

Link to comment
Share on other sites

The PHP code will have a defined table name, such as TABLE_PRODUCT_DESCRIPTION, rather than the MySQL table name of "product_description". Or maybe you'll have one TABLE_PRODUCT_DESCRIPTION and one hardcoded "product_description".

 

 

Hi, just can't figure this out, so would appreciate much if you could take a quick peek at my advanced_search_result.php?

Here it is:

 

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

 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'];
   }
// BOF: Search price range
   if (isset($HTTP_GET_VARS['find_price'])) {
      $pfrom = ($price_range_values[$HTTP_GET_VARS['find_price']]['from'] >= 0.01)?$price_range_values[$HTTP_GET_VARS['find_price']]['from']:0 ;
      $pto = $price_range_values[$HTTP_GET_VARS['find_price']]['to'];
      $sub_title_search =  sprintf(HEADING_TITLE_3, $price_range_array[$HTTP_GET_VARS['find_price']]['text'] );
   }
// EOF: Search price range

   $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));
 } */

 $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 ucwords($keywords) . ' - '.TITLE;?></title>
<link rel="stylesheet" type="text/css" href="stylesheet.css">
</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><b><?php echo HEADING_TITLE_2; ?></b></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>
<!-- // BOF: Search price range -->
     <tr>
       <td class="pageHeading"><b><?php echo $sub_title_search; ?></b></td>
     </tr>
<!-- // EOF: Search price range -->
     <tr>
       <td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>
     </tr>
     <tr>
       <td>
<?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,
                      '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;
 }

// BOF: Search price range
   if (isset($HTTP_GET_VARS['find_price']) && !isset($HTTP_GET_VARS['sort'])) {
      $sort_price_result = array_keys( $column_list, "PRODUCT_LIST_PRICE" );
      $HTTP_GET_VARS['sort'] = ($sort_price_result[0] + 1).'a';
   }
// EOF: Search price range
 $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;
   }
 }
// p.products_date_added to query for New Product Icon contribution

//  $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 ";
$select_str = "select distinct " . $select_column_list . " m.manufacturers_id, p.products_date_added, p.products_id, pd.products_name, pd.products_description, 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 ";
 }

 // START: Extra Fields Contribution
//  $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";
$from_str = "from (" . TABLE_PRODUCTS . " p left join " . TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS . " p2pef on p.products_id=p2pef.products_id) left join " . TABLE_MANUFACTURERS . " m on m.manufacturers_id=p.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";
// END CONTRIBUTION - PRODUCTS EXTRA FIELDS V2.0J (06.29.09)
// END: Extra Fields Contribution
 $from_str .= "," . TABLE_PRODUCTS_ATTRIBUTES . ' pa';

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

 $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]);
// START: Extra Fields Contribution
//          $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) . "%'";
         $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) . "%' or p2pef.products_extra_fields_value like '%" . tep_db_input($keyword) . "%'  or pd.products_description like '%" . tep_db_input($keyword) . "%'";
// STOP: Extra Fields Contribution

         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 . ")";
 }
 // *********************************************************************************************************
 // *********************************************************************************************************
 // This is the accompanying code to the advanced_search.php that allows for products to be searched on their
 // attributes through a drop down list box, it was done by adam, if it helps you perhaps you can help me by donating through paypal, my
 // email address is: [email protected] so that I can make more contributions
 // If you have a look up near line 214 there is an additional from_str that includes product attributes in the from list
 $attributes_query = tep_db_query("select * from " . TABLE_PRODUCTS_OPTIONS . " where language_id = '" . (int)$languages_id . "'");
 $option_value_selected = false;
 $products_stock_attributes_array = array();
 while ($attributes = tep_db_fetch_array($attributes_query)) {
 	if (isset($HTTP_GET_VARS[$attributes["products_options_name"]]) && !empty($HTTP_GET_VARS[$attributes["products_options_name"]]))
     	$option_value_selected = true;
 }
 // the above while loop is to check to see if any values were selected
 if ($option_value_selected == true){
 	$attributes_query2 = tep_db_query("select * from " . TABLE_PRODUCTS_OPTIONS . " where language_id = '" . (int)$languages_id . "'");
 	while ($attributes2 = tep_db_fetch_array($attributes_query2)) {
 		if (isset($HTTP_GET_VARS[$attributes2["products_options_name"]]) && !empty($HTTP_GET_VARS[$attributes2["products_options_name"]])) {
		$str_selected_values = $str_selected_values . $HTTP_GET_VARS[$attributes2["products_options_name"]] . ','; 
   	}
 	}
 	$str_selected_values = substr($str_selected_values,0,strlen($str_selected_values) - 1);
 	$where_str .= "and pa.products_id = p.products_id and pa.options_values_id in (".$str_selected_values.")";
 	// end of accompanying code to advanced_search_result.php that accompanies advanced_search.php
 	// that will include all options_values_id that were selected
// *********************************************************************************************************
 	// *********************************************************************************************************
 }
 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 . 'd';
       $order_str = ' order by p.products_date_added desc';
       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(DIR_WS_MODULES . FILENAME_PRODUCT_LISTING);
?>
       </td>
     </tr>
     <tr>
       <td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '30'); ?></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'); ?>

 

 

I tried to remove the line 247:

 

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

 

But this must have been totally wrong, cause I then got a 1054 - Unknown column 'p.products_id' in 'on clause' error.

 

 

Thank you for your help :-)

Best regards

Kjolebutikken

Link to comment
Share on other sites

The code you listed has nothing to do with the query producing the error. It's in code modified by some add-on, and is not found in standard osC. You're going to have to look harder through your code to find it -- I would suggest searching for count and distinct together (grep "count(distinct" *.php). The error message should somewhere list the exact file and line producing the bad query.

Link to comment
Share on other sites

The code you listed has nothing to do with the query producing the error. It's in code modified by some add-on, and is not found in standard osC. You're going to have to look harder through your code to find it -- I would suggest searching for count and distinct together (grep "count(distinct" *.php). The error message should somewhere list the exact file and line producing the bad query.

 

 

Hmmm, have searched for two days now in whole catalog, no luck.

 

Searched for the following:

 

TABLE_PRODUCTS_DESCRIPTION

products_description

count

count distinct

distinct

count*distinct

*count*distinct*

count(distinct

"count(distinct" *

"count(distinct" *.php)

 

But cannot find a query with something twice. also no hits for most of my search criteria listed above. Not at all sure what to look for. Is it correct that products_description should be listed twice in the code?

Or should I look for a code exactly like the code in the error message? Or would the code look different from the error message? Feeling stupid here, but my php skills are limited :-)

 

I thought the error would be in the advanced_search_result.php

 

Could it be in any file?

Is there other search criteria I could try maybe?

 

I have used * in my searchs. Maybe this does not work with my application? I use notepad++

 

 

Thanks!

Best regards

Kjolebutikken

Link to comment
Share on other sites

I gave you a "grep" search: look for pattern "count(distinct" in all .php files. I don't know what kind of pattern searches you can do in Notepad++.

 

However the code creates the SQL query, it will have either TABLE_PRODUCTS_DESCRIPTION twice, products_description twice, or one of each in the PHP code. Can you get/hire someone to look over your shoulder and help you find the PHP code that generates the query?

Link to comment
Share on other sites

Downloaded windows grep 2.3.

Searched for

count(distinct

count(distinct p.products_id)

"count(distinct"

 

in htm, html, php and java files.

 

Unfortunately no result. Have I understood you correct about input in the grep search?

 

Thanks

Best regards

Kjolebutikken

Link to comment
Share on other sites

When I look closer to some of the codes in some of the files, I see that I many places have one TABLE_PRODUCTS_DESCRIPTION and one products_description in same code.

Like this one for instance:

 

$description_query = tep_db_query("select language_id, products_name, products_description, products_url from " . TABLE_PRODUCTS_DESCRIPTION . " where products_id = '" . (int)$products_id . "'");
           while ($description = tep_db_fetch_array($description_query)) {
             tep_db_query("insert into " . TABLE_PRODUCTS_DESCRIPTION . " (products_id, language_id, products_name, products_description, products_url, products_viewed) values ('" . (int)$dup_products_id . "', '" . (int)$description['language_id'] . "', '" . tep_db_input($description['products_name']) . "', '" . tep_db_input($description['products_description']) . "', '" . tep_db_input($description['products_url']) . "', '0')");
           }

 

 

Is this what you mean about both in same code? Is this bad code?

 

 

Thanks!

Best regards

Kjolebutikken

Link to comment
Share on other sites

OK, just tried something from another post in this forum:

 

In catalog/advanced_search_result.php, I replaced a code earlier changed by the contribution products extra fields:

 

 $from_str = "from (" . TABLE_PRODUCTS . " p left join " . TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS . " p2pef on p.products_id=p2pef.products_id) left join " . TABLE_MANUFACTURERS . " m on m.manufacturers_id=p.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";

 

 

with:

 

$from_str = "from (" . TABLE_PRODUCTS . " p left join " . TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS . " p2pef on p.products_id=p2pef.products_id) left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id";

 

This worked. Advanced search works now, except for searching for criteria price, then I get this error on catalog/advanced_search_result.php:

 

1054 - Unknown column 'p.products_tax_class_id' in 'on clause'

 

select count(distinct p.products_id) as total from (products p left join products_to_products_extra_fields p2pef on p.products_id=p2pef.products_id) left join manufacturers m using(manufacturers_id) left join specials s on p.products_id = s.products_id,products_attributes pa left join tax_rates tr on p.products_tax_class_id = tr.tax_class_id left join 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 = '160') and (gz.zone_id is null or gz.zone_id = '0' or gz.zone_id = '188'), 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 = '4' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id 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) ) >= 2000)

 

 

I will look in the forum for answers, but if you have an idea, please mention it :-)

 

Thanks!

Best regards

Kjolebutikken

Link to comment
Share on other sites

When I look closer to some of the codes in some of the files, I see that I many places have one TABLE_PRODUCTS_DESCRIPTION and one products_description in same code.

Like this one for instance:

 

$description_query = tep_db_query("select language_id, products_name, products_description, products_url from " . TABLE_PRODUCTS_DESCRIPTION . " where products_id = '" . (int)$products_id . "'");
           while ($description = tep_db_fetch_array($description_query)) {
             tep_db_query("insert into " . TABLE_PRODUCTS_DESCRIPTION . " (products_id, language_id, products_name, products_description, products_url, products_viewed) values ('" . (int)$dup_products_id . "', '" . (int)$description['language_id'] . "', '" . tep_db_input($description['products_name']) . "', '" . tep_db_input($description['products_description']) . "', '" . tep_db_input($description['products_url']) . "', '0')");
           }

 

 

Is this what you mean about both in same code? Is this bad code?

No, those are OK. TABLE_PRODUCTS_DESCRIPTION labels the table, while in this case, products_description is being used for the field name. It's unfortunate that the table and field in it were given the same name, but that's life.

Link to comment
Share on other sites

Advanced search works now, except for searching for criteria price, then I get this error on catalog/advanced_search_result.php:

 

Find the PHP code that produces this query, and add more parentheses:

select count(distinct p.products_id) as total from ((((products p left join products_to_products_extra_fields p2pef on (p.products_id=p2pef.products_id)) left join manufacturers m using(manufacturers_id)) left join specials s on (p.products_id = s.products_id),products_attributes pa) left join tax_rates tr on (p.products_tax_class_id = tr.tax_class_id)) left join 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 = '160') and (gz.zone_id is null or gz.zone_id = '0' or gz.zone_id = '188')), 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 = '4' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id 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) ) >= 2000)

I won't claim that all these parentheses are necessary, but those that aren't will help clarify the query structure when you're trying to read it. Definitely at least two of the first three are necessary, and it never hurts to have extra parentheses to clarify things.

Link to comment
Share on other sites

No, those are OK. TABLE_PRODUCTS_DESCRIPTION labels the table, while in this case, products_description is being used for the field name. It's unfortunate that the table and field in it were given the same name, but that's life.

 

OK, I understand, got me a bit confused there, but it seemed strange at the same time, since I found it in so many codes :-)

 

Can't find anything specific on the 1054 - Unknown column 'p.products_tax_class_id' in 'on clause' error.

But I will read through the other 1054 posts and see if I get any out of it :-)

 

Thanks!

Best regards

Kjolebutikken

Link to comment
Share on other sites

OK, I understand, got me a bit confused there, but it seemed strange at the same time, since I found it in so many codes :-)

 

Can't find anything specific on the 1054 - Unknown column 'p.products_tax_class_id' in 'on clause' error.

But I will read through the other 1054 posts and see if I get any out of it :-)

 

Thanks!

 

 

OK, I got rid of the 1054 unknown column error :-)

 

But something is seriously wrong with my catalog/advanced_search result.php file. When I fix one error a new one shows up.

Now I have this one:

 

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 'where p.products_status = '1' and p.products_id = pd.products_id and pd.language' at line 1

 

select count(distinct p.products_id) as total from ((((products p left join products_to_products_extra_fields p2pef on (p.products_id=p2pef.products_id)) left join manufacturers m using(manufacturers_id)) left join specials s on (p.products_id = s.products_id),products_attributes pa), 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 = '4' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_idand pa.products_id = p.products_id and pa.options_values_id in (14)

 

 

This is my catalog/advanced_search_result.php file:

 

<?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'];
   }
// BOF: Search price range
   if (isset($HTTP_GET_VARS['find_price'])) {
      $pfrom = ($price_range_values[$HTTP_GET_VARS['find_price']]['from'] >= 0.01)?$price_range_values[$HTTP_GET_VARS['find_price']]['from']:0 ;
      $pto = $price_range_values[$HTTP_GET_VARS['find_price']]['to'];
      $sub_title_search =  sprintf(HEADING_TITLE_3, $price_range_array[$HTTP_GET_VARS['find_price']]['text'] );
   }
// EOF: Search price range

   $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));
 } */

 $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 ucwords($keywords) . ' - '.TITLE;?></title>
<link rel="stylesheet" type="text/css" href="stylesheet.css">
</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><b><?php echo HEADING_TITLE_2; ?></b></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>
<!-- // BOF: Search price range -->
     <tr>
       <td class="pageHeading"><b><?php echo $sub_title_search; ?></b></td>
     </tr>
<!-- // EOF: Search price range -->
     <tr>
       <td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>
     </tr>
     <tr>
       <td>
<?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,
                      '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;
 }

// BOF: Search price range
   if (isset($HTTP_GET_VARS['find_price']) && !isset($HTTP_GET_VARS['sort'])) {
      $sort_price_result = array_keys( $column_list, "PRODUCT_LIST_PRICE" );
      $HTTP_GET_VARS['sort'] = ($sort_price_result[0] + 1).'a';
   }
// EOF: Search price range
 $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;
   }
 }
// p.products_date_added to query for New Product Icon contribution

//  $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 ";
$select_str = "select distinct " . $select_column_list . " m.manufacturers_id, p.products_date_added, p.products_id, pd.products_name, pd.products_description, 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 ";
 }

 // START: Extra Fields Contribution
//  $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";
$from_str = "from ((((" . TABLE_PRODUCTS . " p left join " . TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS . " p2pef on (p.products_id=p2pef.products_id)) left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id)) left join " . TABLE_SPECIALS . " s on (p.products_id = s.products_id)";
// END CONTRIBUTION - PRODUCTS EXTRA FIELDS V2.0J (06.29.09)
// END: Extra Fields Contribution
 $from_str .= "," . TABLE_PRODUCTS_ATTRIBUTES . ' pa)';

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

 $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]);
// START: Extra Fields Contribution
//          $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) . "%'";
         $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) . "%' or p2pef.products_extra_fields_value like '%" . tep_db_input($keyword) . "%'  or pd.products_description like '%" . tep_db_input($keyword) . "%'";
// STOP: Extra Fields Contribution

         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 . ")";
 }
 // *********************************************************************************************************
 // *********************************************************************************************************
 // This is the accompanying code to the advanced_search.php that allows for products to be searched on their
 // attributes through a drop down list box, it was done by adam, if it helps you perhaps you can help me by donating through paypal, my
 // email address is: [email protected] so that I can make more contributions
 // If you have a look up near line 214 there is an additional from_str that includes product attributes in the from list
 $attributes_query = tep_db_query("select * from " . TABLE_PRODUCTS_OPTIONS . " where language_id = '" . (int)$languages_id . "'");
 $option_value_selected = false;
 $products_stock_attributes_array = array();
 while ($attributes = tep_db_fetch_array($attributes_query)) {
 	if (isset($HTTP_GET_VARS[$attributes["products_options_name"]]) && !empty($HTTP_GET_VARS[$attributes["products_options_name"]]))
     	$option_value_selected = true;
 }
 // the above while loop is to check to see if any values were selected
 if ($option_value_selected == true){
 	$attributes_query2 = tep_db_query("select * from " . TABLE_PRODUCTS_OPTIONS . " where language_id = '" . (int)$languages_id . "'");
 	while ($attributes2 = tep_db_fetch_array($attributes_query2)) {
 		if (isset($HTTP_GET_VARS[$attributes2["products_options_name"]]) && !empty($HTTP_GET_VARS[$attributes2["products_options_name"]])) {
		$str_selected_values = $str_selected_values . $HTTP_GET_VARS[$attributes2["products_options_name"]] . ','; 
   	}
 	}
 	$str_selected_values = substr($str_selected_values,0,strlen($str_selected_values) - 1);
 	$where_str .= "and pa.products_id = p.products_id and pa.options_values_id in (".$str_selected_values.")";
 	// end of accompanying code to advanced_search_result.php that accompanies advanced_search.php
 	// that will include all options_values_id that were selected
// *********************************************************************************************************
 	// *********************************************************************************************************
 }
 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 . 'd';
       $order_str = ' order by p.products_date_added desc';
       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(DIR_WS_MODULES . FILENAME_PRODUCT_LISTING);
?>
       </td>
     </tr>
     <tr>
       <td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '30'); ?></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'); ?>

 

 

If anyone can see what is wrong here, I would appreciate it. I think the problems lies somewhere around the queries, and have been looking at posts in the forum for days now and tried a lot of stuff, but cannot figure it out.

 

Thanks :-)

Best regards

Kjolebutikken

Link to comment
Share on other sites

This query is not from the code you gave, and appears to be different than what we were working on before. You put too many parentheses into this code. It should be

select count(distinct p.products_id) as total from ((products p left join products_to_products_extra_fields p2pef on (p.products_id=p2pef.products_id)) left join manufacturers m using(manufacturers_id)) left join specials s on (p.products_id = s.products_id), products_attributes pa, 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 = '4' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and pa.products_id = p.products_id and pa.options_values_id in (14)

There were also two terms run together due to bad editing: c.categories_idand, which would be a problem.

Link to comment
Share on other sites

This query is not from the code you gave, and appears to be different than what we were working on before. You put too many parentheses into this code. It should be

select count(distinct p.products_id) as total from ((products p left join products_to_products_extra_fields p2pef on (p.products_id=p2pef.products_id)) left join manufacturers m using(manufacturers_id)) left join specials s on (p.products_id = s.products_id), products_attributes pa, 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 = '4' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and pa.products_id = p.products_id and pa.options_values_id in (14)

There were also two terms run together due to bad editing: c.categories_idand, which would be a problem.

 

 

Thanks :-) Fixed it, but it seems I just go back and forth between sql syntax error and 1054 unknown column error no matter what I try to change in the code. Now I'm back to the 1054 error:

 

1054 - Unknown column 'p.products_tax_class_id' in 'on clause'

 

select count(distinct p.products_id) as total from ((products p left join products_to_products_extra_fields p2pef on (p.products_id=p2pef.products_id)) left join manufacturers m using(manufacturers_id)) left join specials s on (p.products_id = s.products_id), products_attributes pa left join tax_rates tr on (p.products_tax_class_id = tr.tax_class_id) left join 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 = '160') and (gz.zone_id is null or gz.zone_id = '0' or gz.zone_id = '188')), 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 = '4' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id 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) ) >= 1500) 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) ) <= 2000)

 

 

This is the code that is producing the error messages. It must be, because when I do changes in it, the errormesage also change.

The file is catalog/advanced_search.php

 

 // START: Extra Fields Contribution
//  $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";
$from_str = "from ((" . TABLE_PRODUCTS . " p left join " . TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS . " p2pef on (p.products_id=p2pef.products_id)) left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id)) left join " . TABLE_SPECIALS . " s on (p.products_id = s.products_id)";
// END CONTRIBUTION - PRODUCTS EXTRA FIELDS V2.0J (06.29.09)
// END: Extra Fields Contribution
 $from_str .= ", " . TABLE_PRODUCTS_ATTRIBUTES . ' pa';

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

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

 

Can you see from this what might be wrong in this code?

 

Thanks :-)

Best regards

Kjolebutikken

Link to comment
Share on other sites

This is a different query. You're changing the wrong code!

 

 

OK. Just so there are no further misunderstanding. Sorry about that :-)

I get the error on the catalog/advanced_search_result.php page, but the code I need to change will be in another file than that?

If so, is there any possibility to predict in which file?

 

 

Thanks.

Best regards

Kjolebutikken

Link to comment
Share on other sites

I would scan/search all your .php files for the string count(distinct p.products_id). It's very likely exactly like that. It's not in "vanilla" osC -- it's probably in an add-on file. It's possible that even that small piece is assembled from smaller pieces -- in that case, look for p.products_id. If you get multiple matches, you have to look at the rest of the query -- it won't be a character-for-character match, as table names are defined macro names (TABLE_PRODUCTS instead of products).

 

I recommend having a copy of all your files on your PC, for backup if nothing else. If you're using Windows, get yourself a copy of the free utility "grep" -- it's great for searching masses of files for strings and "regular expression" patterns. If you're using Linux, "grep" comes with it.

Link to comment
Share on other sites

I would scan/search all your .php files for the string count(distinct p.products_id). It's very likely exactly like that. It's not in "vanilla" osC -- it's probably in an add-on file. It's possible that even that small piece is assembled from smaller pieces -- in that case, look for p.products_id. If you get multiple matches, you have to look at the rest of the query -- it won't be a character-for-character match, as table names are defined macro names (TABLE_PRODUCTS instead of products).

 

I recommend having a copy of all your files on your PC, for backup if nothing else. If you're using Windows, get yourself a copy of the free utility "grep" -- it's great for searching masses of files for strings and "regular expression" patterns. If you're using Linux, "grep" comes with it.

 

Thanks for your advices :-)

 

I have backed up all files several times and also searched all files with both grep for windows and also with other search tools. There are no hits for the string count(distinct p.products_id, so I guess I would try to search for just p.products_id, though I know this will give me many hits.

 

Thanks :-)

Best regards

Kjolebutikken

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...