roback Posted July 26, 2009 Share Posted July 26, 2009 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 More sharing options...
Kjolebutikken Posted April 29, 2010 Share Posted April 29, 2010 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 More sharing options...
MrPhil Posted April 29, 2010 Share Posted April 29, 2010 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 More sharing options...
Kjolebutikken Posted May 4, 2010 Share Posted May 4, 2010 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 More sharing options...
MrPhil Posted May 5, 2010 Share Posted May 5, 2010 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 More sharing options...
Kjolebutikken Posted May 5, 2010 Share Posted May 5, 2010 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 More sharing options...
MrPhil Posted May 5, 2010 Share Posted May 5, 2010 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 More sharing options...
Kjolebutikken Posted May 5, 2010 Share Posted May 5, 2010 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 More sharing options...
MrPhil Posted May 5, 2010 Share Posted May 5, 2010 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 More sharing options...
Kjolebutikken Posted May 10, 2010 Share Posted May 10, 2010 Hi, Don't know anybody, so have to try myself :-) Will give it another try though :-)) Thanks! Best regards Kjolebutikken Link to comment Share on other sites More sharing options...
Kjolebutikken Posted May 10, 2010 Share Posted May 10, 2010 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 More sharing options...
Kjolebutikken Posted May 10, 2010 Share Posted May 10, 2010 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 More sharing options...
Kjolebutikken Posted May 10, 2010 Share Posted May 10, 2010 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 More sharing options...
MrPhil Posted May 10, 2010 Share Posted May 10, 2010 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 More sharing options...
MrPhil Posted May 10, 2010 Share Posted May 10, 2010 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 More sharing options...
Kjolebutikken Posted May 10, 2010 Share Posted May 10, 2010 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 More sharing options...
Kjolebutikken Posted May 15, 2010 Share Posted May 15, 2010 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 More sharing options...
MrPhil Posted May 15, 2010 Share Posted May 15, 2010 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 More sharing options...
Kjolebutikken Posted May 16, 2010 Share Posted May 16, 2010 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 More sharing options...
MrPhil Posted May 16, 2010 Share Posted May 16, 2010 This is a different query. You're changing the wrong code! Link to comment Share on other sites More sharing options...
Kjolebutikken Posted May 17, 2010 Share Posted May 17, 2010 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 More sharing options...
MrPhil Posted May 17, 2010 Share Posted May 17, 2010 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 More sharing options...
Kjolebutikken Posted May 17, 2010 Share Posted May 17, 2010 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 More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.