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;
 }
 }