gazaur.eu Posted June 6, 2008 Report Share Posted June 6, 2008 Hallo, Bitte um Hilfe!!! Shop installiert,eingerichtet, aber wenn klick auf kategorie kommt folgendes: 1054 - Unknown column 'p.products_id' in 'on clause' select p.products_fsk18, p.products_shippingtime, p.products_model, p.products_ean, pd.products_name, m.manufacturers_name, p.products_quantity, p.products_image, p.products_weight, pd.products_short_description, pd.products_description, p.products_id, p.manufacturers_id, p.products_price, p.products_vpe, p.products_vpe_status, p.products_vpe_value, p.products_discount_allowed, p.products_tax_class_id from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '2' and p2c.categories_id = '1' ORDER BY p.products_price ASC [XT SQL Error] Finde leider nichts im Forum.... Danke! Link to comment Share on other sites More sharing options...
djb-side Posted June 8, 2008 Report Share Posted June 8, 2008 hallo zusammen, bei mir erscheint dieser nervige fehler beim aufrufen der suche, also "advanced_search_result.php" 1054 - Unknown column 'p2c.products_id' in 'on clause' SELECT DISTINCT p.products_model, pov.products_options_values_name, pd.products_name, m.manufacturers_name, p.products_quantity, p.products_shippingtime, p.products_fsk18, p.products_image, p.products_weight, pd.products_short_description, pd.products_description, m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id FROM products p LEFT JOIN manufacturers m using(manufacturers_id), products_description pd LEFT JOIN specials s ON p2c.products_id = s.products_id, categories c, products_to_categories p2c, products LEFT JOIN products_attributes pa ON p.products_id = pa.products_id LEFT JOIN products_options_values pov ON pa.options_values_id = pov.products_options_values_id WHERE p.products_status = '1' AND p.products_id = pd.products_id AND pd.language_id = '2' AND p.products_id = p2c.products_id AND p2c.categories_id = c.categories_id AND ( p.products_id = pa.products_id AND (pd.products_name LIKE '%taxi%' OR p.products_model LIKE '%taxi%' OR p.products_ean like '%taxi%' OR m.manufacturers_name LIKE '%taxi%' OR pov.products_options_values_name LIKE '%taxi%' OR pd.products_description LIKE '%taxi%' OR pd.products_short_description LIKE '%taxi%' OR pa.attributes_model LIKE '%taxi%') OR (pd.products_name LIKE '%taxi%' OR p.products_model like '%taxi%' OR p.products_ean LIKE '%taxi%' OR m.manufacturers_name LIKE '%taxi%' OR pd.products_description LIKE '%taxi%' OR pd.products_short_description LIKE '%taxi%') ) GROUP BY pd.products_id ORDER BY pd.products_name [XT SQL Error] Was muss ich in der "advanced_search_result.php" ändern, damit die suche wieder funktioniert? Hab schon sämtliche Fixes aus dem Netz probiert, aber nichts klappt! Danke im voraus! benutze xtc version v3.0.3 Link to comment Share on other sites More sharing options...
djb-side Posted June 8, 2008 Report Share Posted June 8, 2008 @gazaur.eu welche shop version benutzt du? liegt an der mySQL 5 Datenbank... die default.php unter "incldes/modules/" muss gefixt werden. Link to comment Share on other sites More sharing options...
gazaur.eu Posted June 8, 2008 Author Report Share Posted June 8, 2008 Version 3.0.4 Aber hab schon den problem gelöst in der "default.php" waren die fehler... Link to comment Share on other sites More sharing options...
djb-side Posted June 8, 2008 Report Share Posted June 8, 2008 kannst du mir vielleicht mal deine "default.php" schicken? danke Link to comment Share on other sites More sharing options...
gazaur.eu Posted June 8, 2008 Author Report Share Posted June 8, 2008 Ja, natürlich! Hiermit funktioniert alles... <?php /* ----------------------------------------------------------------------------------------- $Id: default.php 1238 2005-09-24 10:51:19Z mz $ XT-Commerce - community made shopping http://www.xt-commerce.com Copyright (c) 2003 XT-Commerce ----------------------------------------------------------------------------------------- based on: (c) 2000-2001 The Exchange Project (earlier name of osCommerce) (c) 2002-2003 osCommerce(default.php,v 1.84 2003/05/07); www.oscommerce.com (c) 2003 nextcommerce (default.php,v 1.11 2003/08/22); www.nextcommerce.org Released under the GNU General Public License ----------------------------------------------------------------------------------------- Third Party contributions: Enable_Disable_Categories 1.3 Autor: Mikel Williams | [email protected] Customers Status v3.x (c) 2002-2003 Copyright Elari [email protected] | www.unlockgsm.com/dload-osc/ | CVS : http://cvs.sourceforge.net/cgi-bin/viewcvs.cgi/elari/?sortby=date#dirlist Released under the GNU General Public License ---------------------------------------------------------------------------------------*/ $default_smarty = new smarty; $default_smarty->assign('tpl_path', 'templates/'.CURRENT_TEMPLATE.'/'); $default_smarty->assign('session', session_id()); $main_content = ''; // include needed functions require_once (DIR_FS_INC.'xtc_customer_greeting.inc.php'); require_once (DIR_FS_INC.'xtc_get_path.inc.php'); require_once (DIR_FS_INC.'xtc_check_categories_status.inc.php'); if (xtc_check_categories_status($current_category_id) >= 1) { $error = CATEGORIE_NOT_FOUND; include (DIR_WS_MODULES.FILENAME_ERROR_HANDLER); } else { if ($category_depth == 'nested') { if (GROUP_CHECK == 'true') { $group_check = "and c.group_permission_".$_SESSION['customers_status']['customers_status_id']."=1 "; } $category_query = "select cd.categories_description, cd.categories_name, cd.categories_heading_title, c.categories_template, c.categories_image from ".TABLE_CATEGORIES." c, ".TABLE_CATEGORIES_DESCRIPTION." cd where c.categories_id = '".$current_category_id."' and cd.categories_id = '".$current_category_id."' ".$group_check." and cd.language_id = '".(int) $_SESSION['languages_id']."'"; $category_query = xtDBquery($category_query); $category = xtc_db_fetch_array($category_query, true); if (isset ($cPath) && ereg('_', $cPath)) { // check to see if there are deeper categories within the current category $category_links = array_reverse($cPath_array); for ($i = 0, $n = sizeof($category_links); $i < $n; $i ++) { if (GROUP_CHECK == 'true') { $group_check = "and c.group_permission_".$_SESSION['customers_status']['customers_status_id']."=1 "; } $categories_query = "select cd.categories_description, c.categories_id, cd.categories_name, cd.categories_heading_title, c.categories_image, c.parent_id from ".TABLE_CATEGORIES." c, ".TABLE_CATEGORIES_DESCRIPTION." cd where c.categories_status = '1' and c.parent_id = '".$category_links[$i]."' and c.categories_id = cd.categories_id ".$group_check." and cd.language_id = '".(int) $_SESSION['languages_id']."' order by sort_order, cd.categories_name"; $categories_query = xtDBquery($categories_query); if (xtc_db_num_rows($categories_query, true) < 1) { // do nothing, go through the loop } else { break; // we've found the deepest category the customer is in } } } else { if (GROUP_CHECK == 'true') { $group_check = "and c.group_permission_".$_SESSION['customers_status']['customers_status_id']."=1 "; } $categories_query = "select cd.categories_description, c.categories_id, cd.categories_name, cd.categories_heading_title, c.categories_image, c.parent_id from ".TABLE_CATEGORIES." c, ".TABLE_CATEGORIES_DESCRIPTION." cd where c.categories_status = '1' and c.parent_id = '".$current_category_id."' and c.categories_id = cd.categories_id ".$group_check." and cd.language_id = '".(int) $_SESSION['languages_id']."' order by sort_order, cd.categories_name"; $categories_query = xtDBquery($categories_query); } $rows = 0; while ($categories = xtc_db_fetch_array($categories_query, true)) { $rows ++; $cPath_new = xtc_category_link($categories['categories_id'],$categories['categories_name']); $width = (int) (100 / MAX_DISPLAY_CATEGORIES_PER_ROW).'%'; $image = ''; if ($categories['categories_image'] != '') { $image = DIR_WS_IMAGES.'categories/'.$categories['categories_image']; } $categories_content[] = array ('CATEGORIES_NAME' => $categories['categories_name'], 'CATEGORIES_HEADING_TITLE' => $categories['categories_heading_title'], 'CATEGORIES_IMAGE' => $image, 'CATEGORIES_LINK' => xtc_href_link(FILENAME_DEFAULT, $cPath_new), 'CATEGORIES_DESCRIPTION' => $categories['categories_description']); } $new_products_category_id = $current_category_id; include (DIR_WS_MODULES.FILENAME_NEW_PRODUCTS); $image = ''; if ($category['categories_image'] != '') { $image = DIR_WS_IMAGES.'categories/'.$category['categories_image']; } $default_smarty->assign('CATEGORIES_NAME', $category['categories_name']); $default_smarty->assign('CATEGORIES_HEADING_TITLE', $category['categories_heading_title']); $default_smarty->assign('CATEGORIES_IMAGE', $image); $default_smarty->assign('CATEGORIES_DESCRIPTION', $category['categories_description']); $default_smarty->assign('language', $_SESSION['language']); $default_smarty->assign('module_content', $categories_content); // get default template if ($category['categories_template'] == '' or $category['categories_template'] == 'default') { $files = array (); if ($dir = opendir(DIR_FS_CATALOG.'templates/'.CURRENT_TEMPLATE.'/module/categorie_listing/')) { while (($file = readdir($dir)) !== false) { if (is_file(DIR_FS_CATALOG.'templates/'.CURRENT_TEMPLATE.'/module/categorie_listing/'.$file) and ($file != "index.html") and (substr($file, 0, 1) !=".")) { $files[] = array ('id' => $file, 'text' => $file); } //if } // while closedir($dir); } $category['categories_template'] = $files[0]['id']; } $default_smarty->caching = 0; $main_content = $default_smarty->fetch(CURRENT_TEMPLATE.'/module/categorie_listing/'.$category['categories_template']); $smarty->assign('main_content', $main_content); } elseif ($category_depth == 'products' || $_GET['manufacturers_id']) { //fsk18 lock $fsk_lock = ''; if ($_SESSION['customers_status']['customers_fsk18_display'] == '0') { $fsk_lock = ' and p.products_fsk18!=1'; } // show the products of a specified manufacturer if (isset ($_GET['manufacturers_id'])) { if (isset ($_GET['filter_id']) && xtc_not_null($_GET['filter_id'])) { // sorting query $sorting_query = xtDBquery("SELECT products_sorting, products_sorting2 FROM ".TABLE_CATEGORIES." where categories_id='".(int) $_GET['filter_id']."'"); $sorting_data = xtc_db_fetch_array($sorting_query,true); if (!$sorting_data['products_sorting']) $sorting_data['products_sorting'] = 'pd.products_name'; $sorting = ' ORDER BY '.$sorting_data['products_sorting'].' '.$sorting_data['products_sorting2'].' '; // We are asked to show only a specific category if (GROUP_CHECK == 'true') { $group_check = "and p.group_permission_".$_SESSION['customers_status']['customers_status_id']."=1 "; } $listing_sql = "select DISTINCT p.products_fsk18, p.products_shippingtime, p.products_model, pd.products_name, p.products_ean, p.products_price, p.products_tax_class_id, m.manufacturers_name, p.products_quantity, p.products_image, p.products_weight, pd.products_short_description, pd.products_description, p.products_id, p.manufacturers_id, p.products_price, p.products_vpe, p.products_vpe_status, p.products_vpe_value, p.products_discount_allowed, p.products_tax_class_id from (".TABLE_PRODUCTS." p, ".TABLE_PRODUCTS_DESCRIPTION." pd, ".TABLE_MANUFACTURERS." m, ".TABLE_PRODUCTS_TO_CATEGORIES." p2c )left join ".TABLE_SPECIALS." s on p.products_id = s.products_id where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '".(int) $_GET['manufacturers_id']."' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id ".$group_check." ".$fsk_lock." and pd.language_id = '".(int) $_SESSION['languages_id']."' and p2c.categories_id = '".(int) $_GET['filter_id']."'".$sorting; } else { // We show them all if (GROUP_CHECK == 'true') { $group_check = "and p.group_permission_".$_SESSION['customers_status']['customers_status_id']."=1 "; } $listing_sql = "select p.products_fsk18, p.products_shippingtime, p.products_model, p.products_ean, pd.products_name, p.products_id, p.products_price, m.manufacturers_name, p.products_quantity, p.products_image, p.products_weight, pd.products_short_description, pd.products_description, p.manufacturers_id, p.products_vpe, p.products_vpe_status, p.products_vpe_value, p.products_discount_allowed, p.products_tax_class_id from( ".TABLE_PRODUCTS." p, ".TABLE_PRODUCTS_DESCRIPTION." pd, ".TABLE_MANUFACTURERS." m )left join ".TABLE_SPECIALS." s on p.products_id = s.products_id where p.products_status = '1' and pd.products_id = p.products_id ".$group_check." ".$fsk_lock." and pd.language_id = '".(int) $_SESSION['languages_id']."' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '".(int) $_GET['manufacturers_id']."'"; } } else { // show the products in a given categorie if (isset ($_GET['filter_id']) && xtc_not_null($_GET['filter_id'])) { // sorting query $sorting_query = xtDBquery("SELECT products_sorting, products_sorting2 FROM ".TABLE_CATEGORIES." where categories_id='".$current_category_id."'"); $sorting_data = xtc_db_fetch_array($sorting_query,true); if (!$sorting_data['products_sorting']) $sorting_data['products_sorting'] = 'pd.products_name'; $sorting = ' ORDER BY '.$sorting_data['products_sorting'].' '.$sorting_data['products_sorting2'].' '; // We are asked to show only specific catgeory if (GROUP_CHECK == 'true') { $group_check = "and p.group_permission_".$_SESSION['customers_status']['customers_status_id']."=1 "; } $listing_sql = "select p.products_fsk18, p.products_shippingtime, p.products_model, p.products_ean, pd.products_name, p.products_id, m.manufacturers_name, p.products_quantity, p.products_image, p.products_weight, pd.products_short_description, pd.products_description, p.manufacturers_id, p.products_price, p.products_vpe, p.products_vpe_status, p.products_vpe_value, p.products_discount_allowed, p.products_tax_class_id from (".TABLE_PRODUCTS." p, ".TABLE_PRODUCTS_DESCRIPTION." pd, ".TABLE_MANUFACTURERS." m, ".TABLE_PRODUCTS_TO_CATEGORIES." p2c )left join ".TABLE_SPECIALS." s on p.products_id = s.products_id where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '".(int) $_GET['filter_id']."' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id ".$group_check." ".$fsk_lock." and pd.language_id = '".(int) $_SESSION['languages_id']."' and p2c.categories_id = '".$current_category_id."'".$sorting; } else { // sorting query $sorting_query = xtDBquery("SELECT products_sorting, products_sorting2 FROM ".TABLE_CATEGORIES." where categories_id='".$current_category_id."'"); $sorting_data = xtc_db_fetch_array($sorting_query,true); if (!$sorting_data['products_sorting']) $sorting_data['products_sorting'] = 'pd.products_name'; $sorting = ' ORDER BY '.$sorting_data['products_sorting'].' '.$sorting_data['products_sorting2'].' '; // We show them all if (GROUP_CHECK == 'true') { $group_check = "and p.group_permission_".$_SESSION['customers_status']['customers_status_id']."=1 "; } $listing_sql = "select p.products_fsk18, p.products_shippingtime, p.products_model, p.products_ean, pd.products_name, m.manufacturers_name, p.products_quantity, p.products_image, p.products_weight, pd.products_short_description, pd.products_description, p.products_id, p.manufacturers_id, p.products_price, p.products_vpe, p.products_vpe_status, p.products_vpe_value, p.products_discount_allowed, p.products_tax_class_id from ((".TABLE_PRODUCTS_DESCRIPTION." pd, ".TABLE_PRODUCTS." p )left join ".TABLE_MANUFACTURERS." m on p.manufacturers_id = m.manufacturers_id, ".TABLE_PRODUCTS_TO_CATEGORIES." p2c )left join ".TABLE_SPECIALS." s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id ".$group_check." ".$fsk_lock." and pd.language_id = '".(int) $_SESSION['languages_id']."' and p2c.categories_id = '".$current_category_id."'".$sorting; } } // optional Product List Filter if (PRODUCT_LIST_FILTER > 0) { if (isset ($_GET['manufacturers_id'])) { $filterlist_sql = "select distinct c.categories_id as id, cd.categories_name as name from ".TABLE_PRODUCTS." p, ".TABLE_PRODUCTS_TO_CATEGORIES." p2c, ".TABLE_CATEGORIES." c, ".TABLE_CATEGORIES_DESCRIPTION." cd where p.products_status = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and p2c.categories_id = cd.categories_id and cd.language_id = '".(int) $_SESSION['languages_id']."' and p.manufacturers_id = '".(int) $_GET['manufacturers_id']."' order by cd.categories_name"; } else { $filterlist_sql = "select distinct m.manufacturers_id as id, m.manufacturers_name as name from ".TABLE_PRODUCTS." p, ".TABLE_PRODUCTS_TO_CATEGORIES." p2c, ".TABLE_MANUFACTURERS." m where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and p.products_id = p2c.products_id and p2c.categories_id = '".$current_category_id."' order by m.manufacturers_name"; } $filterlist_query = xtDBquery($filterlist_sql); if (xtc_db_num_rows($filterlist_query, true) > 1) { $manufacturer_dropdown = xtc_draw_form('filter', FILENAME_DEFAULT, 'get'); if (isset ($_GET['manufacturers_id'])) { $manufacturer_dropdown .= xtc_draw_hidden_field('manufacturers_id', $_GET['manufacturers_id']); $options = array (array ('text' => TEXT_ALL_CATEGORIES)); } else { $manufacturer_dropdown .= xtc_draw_hidden_field('cPath', $cPath); $options = array (array ('text' => TEXT_ALL_MANUFACTURERS)); } $manufacturer_dropdown .= xtc_draw_hidden_field('sort', $_GET['sort']); $manufacturer_dropdown .= xtc_draw_hidden_field(xtc_session_name(), xtc_session_id()); while ($filterlist = xtc_db_fetch_array($filterlist_query, true)) { $options[] = array ('id' => $filterlist['id'], 'text' => $filterlist['name']); } $manufacturer_dropdown .= xtc_draw_pull_down_menu('filter_id', $options, $_GET['filter_id'], 'onchange="this.form.submit()"'); $manufacturer_dropdown .= '</form>'."\n"; } } // Get the right image for the top-right $image = DIR_WS_IMAGES.'table_background_list.gif'; if (isset ($_GET['manufacturers_id'])) { $image = xtDBquery("select manufacturers_image from ".TABLE_MANUFACTURERS." where manufacturers_id = '".(int) $_GET['manufacturers_id']."'"); $image = xtc_db_fetch_array($image,true); $image = $image['manufacturers_image']; } elseif ($current_category_id) { $image = xtDBquery("select categories_image from ".TABLE_CATEGORIES." where categories_id = '".$current_category_id."'"); $image = xtc_db_fetch_array($image,true); $image = $image['categories_image']; } include (DIR_WS_MODULES.FILENAME_PRODUCT_LISTING); } else { // default page if (GROUP_CHECK == 'true') { $group_check = "and group_ids LIKE '%c_".$_SESSION['customers_status']['customers_status_id']."_group%'"; } $shop_content_query = xtDBquery("SELECT content_title, content_heading, content_text, content_file FROM ".TABLE_CONTENT_MANAGER." WHERE content_group='5' ".$group_check." AND languages_id='".$_SESSION['languages_id']."'"); $shop_content_data = xtc_db_fetch_array($shop_content_query,true); $default_smarty->assign('title', $shop_content_data['content_heading']); include (DIR_WS_INCLUDES.FILENAME_CENTER_MODULES); if ($shop_content_data['content_file'] != '') { ob_start(); if (strpos($shop_content_data['content_file'], '.txt')) echo '<pre>'; include (DIR_FS_CATALOG.'media/content/'.$shop_content_data['content_file']); if (strpos($shop_content_data['content_file'], '.txt')) echo '</pre>'; $shop_content_data['content_text'] = ob_get_contents(); ob_end_clean(); } $default_smarty->assign('text', str_replace('{$greeting}', xtc_customer_greeting(), $shop_content_data['content_text'])); $default_smarty->assign('language', $_SESSION['language']); // set cache ID if (!CacheCheck()) { $default_smarty->caching = 0; $main_content = $default_smarty->fetch(CURRENT_TEMPLATE.'/module/main_content.html'); } else { $default_smarty->caching = 1; $default_smarty->cache_lifetime = CACHE_LIFETIME; $default_smarty->cache_modified_check = CACHE_CHECK; $cache_id = $_SESSION['language'].$_SESSION['currency'].$_SESSION['customer_id']; $main_content = $default_smarty->fetch(CURRENT_TEMPLATE.'/module/main_content.html', $cache_id); } $smarty->assign('main_content', $main_content); } } ?>[/PHP] Link to comment Share on other sites More sharing options...
BTSpeedy Posted April 19, 2010 Report Share Posted April 19, 2010 Ich habe dieses Problem bei der Suche immer noch. Die default.php habe ich inzwischen geändert. Keine Ahnung was jetzt? Hat noch jemand einen Hinweis? xt:Commerce v3.0.4 SP2.1 Release Datum: 17 Aug 2006 MySQL 5.0 gehostet bei 1&1 AHA, Meine jetzigen Infos sagen mir, das es wohl an der MySQL Version liegt. Mit Version 4 würde es laufen. Ist irgendwie mal eine Updateversion vorgesehen um von 4 auf 5 umzusteigen? Alles klar!!! Nach austausch obiger default.php muss auch die SQL-Abfrage in der advances_search_result.php entsprechnd mit Klammern versehen werden. Ich vermute mal das andere Stellen an denen dieser Fehler auftaucht ähnlich nachbearbeitet werden müssen. Boah, entlich gefunden. kann ganz hilfreich sein, so ein Selbstgespräch Link to comment Share on other sites More sharing options...
schmuckart Posted May 2, 2010 Report Share Posted May 2, 2010 Alles klar!!! Nach austausch obiger default.php muss auch die SQL-Abfrage in der advances_search_result.php entsprechnd mit Klammern versehen werden. Hallo BT Speedy, wie genau sollte die advances_search_result.php aussehen? Ich krieg's nicht hin... und verrätst du mir ob es danach ging oder kamen noch mehr Fehlermeldungen zum Vorschein? Danke schon mal... Link to comment Share on other sites More sharing options...
BTSpeedy Posted May 2, 2010 Report Share Posted May 2, 2010 Hallo, Nach obiger Änderung hat bisher alles ohne Fehler funktioniert. Die Syntax von SQL-Abfragen hat sich leicht geändert. Bei Verwendung von JOIN müssen die Worte hinter dem FROM in Klammern gesetzt werden. Ich gebe Dir im Folgenden meine "advanced_search_ressult.php". Hier ist auch das lästige "Alle Artikel zeigen" bei leerem Suchstring abgestellt. Leerer Suchstring führt 1. zur Erweiterten Suche und 2. hier zu keinem Ergebniss. Wenn dir das nicht gefällt, musst du die entsprechenden Zeilen entfernen. Sie sind im Quelltext kommerntiert. <?php /* ----------------------------------------------------------------------------------------- $Id: advanced_search_result.php 1141 2005-08-10 11:31:36Z novalis $ XT-Commerce - community made shopping http://www.xt-commerce.com Copyright (c) 2005 XT-Commerce ----------------------------------------------------------------------------------------- based on: (c) 2000-2001 The Exchange Project (earlier name of osCommerce) (c) 2002-2003 osCommerce(advanced_search_result.php,v 1.68 2003/05/14); www.oscommerce.com (c) 2003 nextcommerce (advanced_search_result.php,v 1.17 2003/08/21); www.nextcommerce.org Released under the GNU General Public License ---------------------------------------------------------------------------------------*/ include ('includes/application_top.php'); // create smarty elements $smarty = new Smarty; // include boxes require (DIR_FS_CATALOG.'templates/'.CURRENT_TEMPLATE.'/source/boxes.php'); // include needed functions require_once (DIR_FS_INC.'xtc_parse_search_string.inc.php'); require_once (DIR_FS_INC.'xtc_get_subcategories.inc.php'); require_once (DIR_FS_INC.'xtc_get_currencies_values.inc.php'); /* * check search entry */ $error = 0; // reset error flag to false $errorno = 0; $keyerror = 0; if (isset ($_GET['keywords']) && empty($_GET['keywords'])) { // ********** Bei Suche ohne Zeicheneingabe wird zur erweiterten Suche verzweigt // $keyerror = 1; // alt $error = 1; //neu // ********************************************************** } if ((isset ($_GET['keywords']) && empty ($_GET['keywords'])) && (isset ($_GET['pfrom']) && empty ($_GET['pfrom'])) && (isset ($_GET['pto']) && empty ($_GET['pto']))) { $errorno += 1; $error = 1; } elseif (isset ($_GET['keywords']) && empty ($_GET['keywords']) && !(isset ($_GET['pfrom'])) && !(isset ($_GET['pto']))) { $errorno += 1; $error = 1; } if (strlen($_GET['keywords']) < 3 && strlen($_GET['keywords']) > 0 && $error == 0) { $errorno += 1; $error = 1; $keyerror = 1; } if (strlen($_GET['pfrom']) > 0) { $pfrom_to_check = xtc_db_input($_GET['pfrom']); if (!settype($pfrom_to_check, "double")) { $errorno += 10000; $error = 1; } } if (strlen($_GET['pto']) > 0) { $pto_to_check = $_GET['pto']; if (!settype($pto_to_check, "double")) { $errorno += 100000; $error = 1; } } if (strlen($_GET['pfrom']) > 0 && !(($errorno & 10000) == 10000) && strlen($_GET['pto']) > 0 && !(($errorno & 100000) == 100000)) { if ($pfrom_to_check > $pto_to_check) { $errorno += 1000000; $error = 1; } } if (strlen($_GET['keywords']) > 0) { if (!xtc_parse_search_string(stripslashes($_GET['keywords']), $search_keywords)) { $errorno += 10000000; $error = 1; $keyerror = 1; } } if ($error == 1 && $keyerror != 1) { xtc_redirect(xtc_href_link(FILENAME_ADVANCED_SEARCH, 'errorno='.$errorno.'&'.xtc_get_all_get_params(array ('x', 'y')))); } else { /* * search process starts here */ $breadcrumb->add(NAVBAR_TITLE1_ADVANCED_SEARCH, xtc_href_link(FILENAME_ADVANCED_SEARCH)); $breadcrumb->add(NAVBAR_TITLE2_ADVANCED_SEARCH, xtc_href_link(FILENAME_ADVANCED_SEARCH_RESULT, 'keywords='.xtc_db_input($_GET['keywords']).'&search_in_description='.xtc_db_input($_GET['search_in_description']).'&categories_id='.(int)$_GET['categories_id'].'&inc_subcat='.xtc_db_input($_GET['inc_subcat']).'&manufacturers_id='.(int)$_GET['manufacturers_id'].'&pfrom='.xtc_db_input($_GET['pfrom']).'&pto='.xtc_db_input($_GET['pto']).'&dfrom='.xtc_db_input($_GET['dfrom']).'&dto='.xtc_db_input($_GET['dto']))); require (DIR_WS_INCLUDES.'header.php'); // define additional filters // //fsk18 lock if ($_SESSION['customers_status']['customers_fsk18_display'] == '0') { $fsk_lock = " AND p.products_fsk18 != '1' "; } else { unset ($fsk_lock); } //group check if (GROUP_CHECK == 'true') { $group_check = " AND p.group_permission_".$_SESSION['customers_status']['customers_status_id']."=1 "; } else { unset ($group_check); } //manufacturers if set if (isset ($_GET['manufacturers_id']) && xtc_not_null($_GET['manufacturers_id'])) { $manu_check = " AND p.manufacturers_id = '".(int)$_GET['manufacturers_id']."' "; } //include subcategories if needed if (isset ($_GET['categories_id']) && xtc_not_null($_GET['categories_id'])) { if ($_GET['inc_subcat'] == '1') { $subcategories_array = array (); xtc_get_subcategories($subcategories_array, (int)$_GET['categories_id']); $subcat_join = " LEFT OUTER JOIN ".TABLE_PRODUCTS_TO_CATEGORIES." AS p2c ON (p.products_id = p2c.products_id) "; $subcat_where = " AND p2c.categories_id IN ('".(int) $_GET['categories_id']."' "; foreach ($subcategories_array AS $scat) { $subcat_where .= ", '".$scat."'"; } $subcat_where .= ") "; } else { $subcat_join = " LEFT OUTER JOIN ".TABLE_PRODUCTS_TO_CATEGORIES." AS p2c ON (p.products_id = p2c.products_id) "; $subcat_where = " AND p2c.categories_id = '".(int) $_GET['categories_id']."' "; } } if ($_GET['pfrom'] || $_GET['pto']) { $rate = xtc_get_currencies_values($_SESSION['currency']); $rate = $rate['value']; if ($rate && $_GET['pfrom'] != '') { $pfrom = $_GET['pfrom'] / $rate; } if ($rate && $_GET['pto'] != '') { $pto = $_GET['pto'] / $rate; } } //price filters if (($pfrom != '') && (is_numeric($pfrom))) { $pfrom_check = " AND (IF(s.status = '1' AND p.products_id = s.products_id, s.specials_new_products_price, p.products_price) >= ".$pfrom.") "; } else { unset ($pfrom_check); } if (($pto != '') && (is_numeric($pto))) { $pto_check = " AND (IF(s.status = '1' AND p.products_id = s.products_id, s.specials_new_products_price, p.products_price) <= ".$pto." ) "; } else { unset ($pto_check); } //build query $select_str = "SELECT distinct p.products_id, p.products_price, p.products_model, p.products_quantity, p.products_shippingtime, p.products_fsk18, p.products_image, p.products_weight, p.products_tax_class_id, pd.products_name, pd.products_short_description, pd.products_description "; // ***************** Um inaktive Kategorien auszuschalten! // $from_str = "FROM ".TABLE_PRODUCTS." AS p LEFT JOIN ".TABLE_PRODUCTS_DESCRIPTION." AS pd ON (p.products_id = pd.products_id) "; // alt $from_str = "FROM (".TABLE_PRODUCTS." AS p, ".TABLE_PRODUCTS_TO_CATEGORIES." AS pc, ".TABLE_CATEGORIES." AS c ) LEFT JOIN ".TABLE_PRODUCTS_DESCRIPTION." AS pd ON (p.products_id = pd.products_id) "; //neu //************************** $from_str .= $subcat_join; if (SEARCH_IN_ATTR == 'true') { $from_str .= " LEFT OUTER JOIN ".TABLE_PRODUCTS_ATTRIBUTES." AS pa ON (p.products_id = pa.products_id) LEFT OUTER JOIN ".TABLE_PRODUCTS_OPTIONS_VALUES." AS pov ON (pa.options_values_id = pov.products_options_values_id) "; } $from_str .= "LEFT OUTER JOIN ".TABLE_SPECIALS." AS s ON (p.products_id = s.products_id) AND s.status = '1'"; if ((DISPLAY_PRICE_WITH_TAX == 'true') && ((isset ($_GET['pfrom']) && xtc_not_null($_GET['pfrom'])) || (isset ($_GET['pto']) && xtc_not_null($_GET['pto'])))) { if (!isset ($_SESSION['customer_country_id'])) { $_SESSION['customer_country_id'] = STORE_COUNTRY; $_SESSION['customer_zone_id'] = STORE_ZONE; } $from_str .= " LEFT OUTER JOIN ".TABLE_TAX_RATES." tr ON (p.products_tax_class_id = tr.tax_class_id) LEFT OUTER JOIN ".TABLE_ZONES_TO_GEO_ZONES." gz ON (tr.tax_zone_id = gz.geo_zone_id) "; $tax_where = " AND (gz.zone_country_id IS NULL OR gz.zone_country_id = '0' OR gz.zone_country_id = '".(int) $_SESSION['customer_country_id']."') AND (gz.zone_id is null OR gz.zone_id = '0' OR gz.zone_id = '".(int) $_SESSION['customer_zone_id']."')"; } else { unset ($tax_where); } //where-string $where_str = " WHERE p.products_status = '1' "." AND pd.language_id = '".(int) $_SESSION['languages_id']."'".$subcat_where.$fsk_lock.$manu_check.$group_check.$tax_where.$pfrom_check.$pto_check; // ***************** Um inaktive Kategorien auszuschalten! $where_str .= " AND pc.products_id = p.products_id AND c.categories_id = pc.categories_id AND c.categories_status = 1 "; // zusätzlich //************************** //go for keywords... this is the main search process if (isset ($_GET['keywords']) && xtc_not_null($_GET['keywords'])) { if (xtc_parse_search_string(stripslashes($_GET['keywords']), $search_keywords)) { $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 : $where_str .= " ( "; $where_str .= "pd.products_keywords LIKE ('%".addslashes($search_keywords[$i])."%') "; if (SEARCH_IN_DESC == 'true') { $where_str .= "OR pd.products_description LIKE ('%".addslashes($search_keywords[$i])."%') "; $where_str .= "OR pd.products_short_description LIKE ('%".addslashes($search_keywords[$i])."%') "; } $where_str .= "OR pd.products_name LIKE ('%".addslashes($search_keywords[$i])."%') "; $where_str .= "OR p.products_model LIKE ('%".addslashes($search_keywords[$i])."%') "; if (SEARCH_IN_ATTR == 'true') { $where_str .= "OR (pov.products_options_values_name LIKE ('%".addslashes($search_keywords[$i])."%') "; $where_str .= "AND pov.language_id = '".(int) $_SESSION['languages_id']."')"; } $where_str .= " ) "; break; } } $where_str .= " ) GROUP BY p.products_id ORDER BY p.products_id "; } } //glue together $listing_sql = $select_str.$from_str.$where_str; require (DIR_WS_MODULES.FILENAME_PRODUCT_LISTING); } $smarty->assign('language', $_SESSION['language']); $smarty->caching = 0; if (!defined(RM)) $smarty->load_filter('output', 'note'); $smarty->display(CURRENT_TEMPLATE.'/index.html'); include ('includes/application_bottom.php'); ?> [/PHP] Link to comment Share on other sites More sharing options...
schmuckart Posted May 2, 2010 Report Share Posted May 2, 2010 Danke! Hab es so einefügt. Das mit dem leeren Suchstring geht, der Rest aber nicht... Das ist die Fehlermeldung: 1054 - Unbekanntes Tabellenfeld 'pd.products_keywords' in where clause SELECT distinct p.products_id, p.products_price, p.products_model, p.products_quantity, p.products_shippingtime, p.products_fsk18, p.products_image, p.products_weight, p.products_tax_class_id, pd.products_name, pd.products_short_description, pd.products_description FROM (products AS p, products_to_categories AS pc, categories AS c ) LEFT JOIN products_description AS pd ON (p.products_id = pd.products_id) LEFT OUTER JOIN specials AS s ON (p.products_id = s.products_id) AND s.status = '1' WHERE p.products_status = '1' AND pd.language_id = '2' AND pc.products_id = p.products_id AND c.categories_id = pc.categories_id AND c.categories_status = 1 AND ( ( pd.products_keywords LIKE ('%suchbegriff%') OR pd.products_name LIKE ('%suchbegriff%') OR p.products_model LIKE ('%suchbegriff%') ) ) GROUP BY p.products_id ORDER BY p.products_id [XT SQL Error] An welcher Stelle hängt es denn noch? Kann mir bitte jemand helfen... ich werd noch wahnsinnig... Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.