Hajime Yamamoto
yamam****@ytw*****
2007年 5月 28日 (月) 09:39:45 JST
山本と申します。
mysql 5.XX では、join の解釈が MySQL 4 とは違うようです。
修正する箇所は３つ
１） default.php の175行目から191行目を以下のように修正
// We are asked to show only a specific category
// $listing_sql = "select " . $select_column_list . "
p.products_id, p.manufacturers_id, 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 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)$HTTP_GET_VARS['manufacturers_id'] . "'
and p.products_id = p2c.products_id and pd.products_id = p2c.products_id
and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id
= '" . (int)$HTTP_GET_VARS['filter_id'] . "'";
// MySQL 5.0.X
$listing_sql = "select " . $select_column_list . "
p.products_id, p.manufacturers_id, 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 from " .
TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on (
p.products_id = s.products_id ), " . TABLE_PRODUCTS_DESCRIPTION . " pd,
" . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c
where p.products_status = '1' and p.manufacturers_id =
m.manufacturers_id and m.manufacturers_id = '" .
(int)$HTTP_GET_VARS['manufacturers_id'] . "' and p.products_id =
p2c.products_id and pd.products_id = p2c.products_id and pd.language_id
= '" . (int)$languages_id . "' and p2c.categories_id = '" .
(int)$HTTP_GET_VARS['filter_id'] . "'";
} else {
// We show them all
// $listing_sql = "select " . $select_column_list . "
p.products_id, p.manufacturers_id, 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 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 and pd.language_id = '" .
(int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and
m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";
// MySQL 5.0.X
$listing_sql = "select " . $select_column_list . "
p.products_id, p.manufacturers_id, 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 from " .
TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on (
p.products_id = s.products_id ), " . TABLE_PRODUCTS_DESCRIPTION . " pd,
" . TABLE_MANUFACTURERS . " m where p.products_status = '1' and
pd.products_id = p.products_id and pd.language_id = '" .
(int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and
m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";
}
// We build the categories-dropdown
$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)$languages_id . "' and p.manufacturers_id = '" .
(int)$HTTP_GET_VARS['manufacturers_id'] . "' order by cd.categories_name";
} else {
// show the products in a given categorie
if (isset($HTTP_GET_VARS['filter_id'])) {
// We are asked to show only specific catgeory
// $listing_sql = "select " . $select_column_list . "
p.products_id, p.manufacturers_id, 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 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)$HTTP_GET_VARS['filter_id'] . "' and
p.products_id = p2c.products_id and pd.products_id = p2c.products_id and
pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '"
. (int)$current_category_id . "'";
// MySQL 5.0.X
$listing_sql = "select " . $select_column_list . "
p.products_id, p.manufacturers_id, 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 from " .
TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on (
p.products_id = s.products_id ), " . TABLE_PRODUCTS_DESCRIPTION . " pd,
" . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c
where p.products_status = '1' and p.manufacturers_id =
m.manufacturers_id and m.manufacturers_id = '" .
(int)$HTTP_GET_VARS['filter_id'] . "' and p.products_id =
p2c.products_id and pd.products_id = p2c.products_id and pd.language_id
= '" . (int)$languages_id . "' and p2c.categories_id = '" .
(int)$current_category_id . "'";
} else {
// We show them all
// $listing_sql = "select " . $select_column_list . "
p.products_id, p.manufacturers_id, 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 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 and
pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '"
. (int)$current_category_id . "'";
// MySQL 5.0.X
$listing_sql = "select " . $select_column_list . "
p.products_id, p.manufacturers_id, 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 from " .
TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join "
. TABLE_MANUFACTURERS . " m on ( p.manufacturers_id = m.manufacturers_id
) left join " . TABLE_SPECIALS . " s on ( p.products_id = s.products_id
), " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status =
'1' and p.products_id = p2c.products_id and pd.products_id =
p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and
p2c.categories_id = '" . (int)$current_category_id . "'";
２） advanced_search_result.php の183行目を以下のように修正
// $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";
// MySQL 5.0.X
$from_str = "from " . TABLE_PRODUCTS . " p left join " .
TABLE_MANUFACTURERS . " m using(manufacturers_id) left join " .
TABLE_SPECIALS . " s on ( p.products_id = s.products_id ), " .
TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c, " .
TABLE_PRODUCTS_TO_CATEGORIES . " p2c";
３） product_reviews_info.php の19行目を以下のように修正
// $reviews_query = tep_db_query("select rd.reviews_text,
r.reviews_rating, r.reviews_id, r.products_id, r.customers_name,
r.date_added, r.last_modified, r.reviews_read, p.products_id,
pd.products_name, p.products_image from " . TABLE_REVIEWS . " r, " .
TABLE_REVIEWS_DESCRIPTION . " rd left join " . TABLE_PRODUCTS . " p on
(r.products_id = p.products_id) left join " . TABLE_PRODUCTS_DESCRIPTION
. " pd on (p.products_id = pd.products_id and pd.language_id = '".
$languages_id . "') where r.reviews_id = '" .
(int)$HTTP_GET_VARS['reviews_id'] . "' and r.reviews_id = rd.reviews_id
and p.products_status = '1'");
// MySQL 5.0.X
$reviews_query = tep_db_query("select rd.reviews_text,
r.reviews_rating, r.reviews_id, r.products_id, r.customers_name,
r.date_added, r.last_modified, r.reviews_read, p.products_id,
pd.products_name, p.products_image from " . TABLE_REVIEWS . " r left
join " . TABLE_PRODUCTS . " p on (r.products_id = p.products_id) left
join " . TABLE_PRODUCTS_DESCRIPTION . " pd on (p.products_id =
pd.products_id and pd.language_id = '". $languages_id . "'), " .
TABLE_REVIEWS_DESCRIPTION . " rd where r.reviews_id = '" .
(int)$HTTP_GET_VARS['reviews_id'] . "' and r.reviews_id = rd.reviews_id
and p.products_status = '1'");
> 殆ど幽霊会員ですが、以下のエラーに遭遇し、何をどうして修正して良いのかわかりません。
> 皆さんお忙しいとは考えますが、どうしても乗り越えたいのでお教えください。
>
> 左側カテゴリーで商品を選択すると．．．
>
>
> 1054 - Unknown column 'p.products_id' in 'on clause'
> select count(*) as total 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 =
> '4' and p2c.categories_id = '22'
>
> このようなエラーです。
>
> 環境
> SuSE 10.2
> PHP 5.XX
> mysql 5.XX
>
> どの様に各ファイルを修正したら良いのか、お教えくださいます様、お願いもうしあげます。
>
>