Jump to content
xt:Commerce Community Forum
Sign in to follow this  
gazaur.eu

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

Recommended Posts

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!

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 | mikel@ladykatcostumes.com
Customers Status v3.x (c) 2002-2003 Copyright Elari elari@free.fr | 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]

Share this post


Link to post
Share on other sites

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 :)

Edited by BTSpeedy

Share this post


Link to post
Share on other sites

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... :confused: und verrätst du mir ob es danach ging oder kamen noch mehr Fehlermeldungen zum Vorschein?

Danke schon mal...

Share this post


Link to post
Share on other sites

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]

Share this post


Link to post
Share on other sites

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...

Share this post


Link to post
Share on other sites
Sign in to follow this  

×
×
  • Create New...