W standardowej wersji sklepu osCommerce podczas wyświetlania liczebności kategorii wykonywanych jest dużo zapytań do bazy danych (zależnie od ilości kategorii i produktów). Niniejsza porada pokazuje, jak zoptymalizować nasz sklep.

W pliku includes/functions/general.php odszukaj funkcję:

testKod: php
function tep_count_products_in_category($category_id, $include_inactive = false) {
 $products_count = 0;
 if ($include_inactive == true) {
 $products_query = tep_db_query("select count(*) as total from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_id = p2c.products_id and p2c.categories_id = '" . (int)$category_id . "'");
 } else {
 $products_query = tep_db_query("select count(*) as total from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_id = p2c.products_id and p.products_status = '1' and p2c.categories_id = '" . (int)$category_id . "'");
 }
 $products = tep_db_fetch_array($products_query);
 $products_count += $products['total'];
 
 $child_categories_query = tep_db_query("select categories_id from " . TABLE_CATEGORIES . " where parent_id = '" . (int)$category_id . "'");
 if (tep_db_num_rows($child_categories_query)) {
 while ($child_categories = tep_db_fetch_array($child_categories_query)) {
 $products_count += tep_count_products_in_category($child_categories['categories_id'], $include_inactive);
 }
 }
 
 return $products_count;
 }

i zamień ją na funkcję:

testKod: php
function tep_count_products_in_category($category_id, $include_inactive = false) {
 
 if ($include_inactive) {
 $subindex = 'all';
 $active_clause = '';
 } else {
 $subindex = 'active_only';
 $active_clause = "and p.products_status = '1'";
 }
 if (!isset($GLOBALS['products_in_category'][$subindex])) {
 $query = tep_db_query("
 select   categories_id as category, parent_id,
 0 as num_products, 0 as total
 from     " . TABLE_CATEGORIES . " c
 ");
 while ($row = tep_db_fetch_array($query)) {
 $GLOBALS['products_in_category'][$subindex][$row['category']] = $row;
 }
 $query = tep_db_query("
 select   count(*) as num_products,
 p2c.categories_id as category
 from     " . TABLE_PRODUCTS . " p,
 " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c
 where    p.products_id = p2c.products_id
 $active_clause
 group by category
 ");
 while ($row = tep_db_fetch_array($query)) {
 $GLOBALS['products_in_category'][$subindex][$row['category']]['num_products'] = $row['num_products'];
 }
 
 foreach ($GLOBALS['products_in_category'][$subindex] as $row) {
 $cat = $row['category'];
 $num = $row['num_products'];
 do {
 if (!isset($GLOBALS['products_in_category'][$subindex][$cat])) {
 $GLOBALS['products_in_category'][$subindex][$cat] = array();
 }
 $GLOBALS['products_in_category'][$subindex][$cat]['total'] += $num;
 $cat = $GLOBALS['products_in_category'][$subindex][$cat]['parent_id'];
 } while ($cat > 0);
 }
 }
 $pic =& $GLOBALS['products_in_category'][$subindex];
 
 if (isset($pic[$category_id]) && isset($pic[$category_id]['total'])) {
 return($pic[$category_id]['total']);
 } else {
 return 0;
 }
 }

Jeżeli błąd o treści 1054 - Unknown column 'p.products_id' in 'on clause' pojawia się na liście produktów lub w wynikach wyszukiwania koniecznie zastosuj się do poniższej porady.

Zmiany dotyczą dwóch plików: index.php i advanced_search_result.php z katalogu głównego sklepu.

W pierwszym z nich odszukujemy:

testKod: php
// show the products of a specified manufacturer
 if (isset($HTTP_GET_VARS['manufacturers_id'])) {
 if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {
// 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'] . "'";
 } 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'] . "'";
 }
 } else {
// show the products in a given categorie
 if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($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 . "'";
 } 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 . "'";
 }
 }

i zamieniamy na:

testKod: php
// show the products of a specified manufacturer
 if (isset($HTTP_GET_VARS['manufacturers_id'])) {
 if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {
// 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'] . "'";
 } 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'] . "'";
 }
 } else {
// show the products in a given categorie
 if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($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 . "'";
 } 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 . "'";
 }
 }

W drugim pliku odszukujemy kod:

testKod: php
$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";

i zamieniamy na:

testKod: php
$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";

Jeżeli problem występuje na niewymienionej stronie należy wprowadzić zmiany zamykając zbiór tabel w nawiasy przed użyciem polecenia left join, czyli:

testKod: php
select * from (tabela1) left join tabela 2 on tabela1.products_id = tabela2.products_id

Jeżeli stosujemy zapytanie z większą ilością poleceń left join należy to zrobić tak:

testKod: php
select * from ((tabela1) left join tabela 2 on tabela1.products_id = tabela2.products_id) left join tabela3 on tabela3.status = tabela2.status)