0
Andreas2003, on 22 June 2010, 07:50, said:
But perhaps I should firstly focus on the index.php:
I still have these ~200 queries, coming from "select c.categories..... where parent_id = 'xx', which is produced by some queries in the index.php.
IMHO that comes from the category check, how many sub-cats are available, is the deepest end of the cats reached etc.
Is it correct, that the cat query is querying all cat and sub-cats every time?
Can I somehow optimize that ?
Or is this quantity of queries only resulting from my quantity of categories?
I still have these ~200 queries, coming from "select c.categories..... where parent_id = 'xx', which is produced by some queries in the index.php.
IMHO that comes from the category check, how many sub-cats are available, is the deepest end of the cats reached etc.
Is it correct, that the cat query is querying all cat and sub-cats every time?
Can I somehow optimize that ?
Or is this quantity of queries only resulting from my quantity of categories?
The select code from languages queries should be easy to solve (it that hasn't been done already in newer versions).
The select c.categories..... where parent_id = 'xx' queries are typical for the drop-down in the categories box of the category box enhanced contribution.
But you wouldn't see those if you commented out the inclusion of the categories box (unless you use an old version of the STS template contribution where the drop-down queries were executed even if you didn't use the drop-down).
For the osC2.2 rc2a with standard install of products I found the following number of queries:
index.php: 84, cPath=1: 125, product_info.php?products_id=20: 103
With the enhanced category box added:
index.php: 104, cPath=1: 147, product_info.php?products_id=20: 126 (so about the number of categories higher...)
With an adapted CountProductsStore and an adapted categories box (to use the CountProductsStore):
index.php: 43, cPath=1: 43, product_info.php?products_id=20: 42:
The adapted includes/classes/CountProductsStore.php (adapted from the optimize categories box contribution):
<?php
/* $Id: CountProductsStore.php v 1.2 2010/06/23 JanZ
an object to store the number of products in a category and which category_id has which parent_id
osCommerce, Open Source E-Commerce Solutions
http://www.oscommerce.com
Copyright (c) 2006 osCommerce
Released under the GNU General Public License
*/
class CountProductsStore {
var $prods_in_category, $category_tree, $category_name_tree;
function CountProductsStore() {
if (SHOW_COUNTS == 'true') {
if (USE_PRODUCTS_COUNT_CACHE == 'true') {
$this->prods_in_category = tep_cache_products_count();
} else {
$category_query = tep_db_query("select categories_id from categories");
while ($_categories = tep_db_fetch_array($category_query)) {
$categories[] = $_categories['categories_id'];
}
$products_query = tep_db_query("select count(*) as number_in_category, categories_id 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 in (" . implode(",", $categories) . ") group by categories_id");
while ($_prods_in_category = tep_db_fetch_array($products_query)) {
$this->prods_in_category[$_prods_in_category['categories_id']] = $_prods_in_category['number_in_category'];
}
}
}
$category_trees_array = $this->buildCategoryTree();
$this->category_tree = $category_trees_array['parent_tree'];
$this->category_name_tree = $category_trees_array['name_tree'];
}
function CountProductsInCategory($category_id) {
if (isset($this->prods_in_category[$category_id])) {
return $this->prods_in_category[$category_id];
} else {
return 0;
}
}
function hasChildCategories($category_id) {
foreach ($this->category_tree as $categories_id => $parent_id) {
if ($parent_id == $category_id) {
$ChildCategories[] = $categories_id;
}
} // end foreach ($this->category_tree as $categories_id => $parent_id)
if (isset($ChildCategories)) {
return $ChildCategories;
} else {
return false;
}
}
function buildCategoryTree() {
$languages_id = '';
if (!isset($_SESSION['languages_id']) || $_SESSION['languages_id'] < 1) {
$languages_id = 1;
} else {
$languages_id = $_SESSION['languages_id'];
}
$category_query = tep_db_query("select c.categories_id, c.parent_id, cd.categories_name from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.categories_id = cd.categories_id and language_id='" . (int)$languages_id ."' order by sort_order, cd.categories_name");
while ($categories = tep_db_fetch_array($category_query)) {
$category_tree_array[$categories['categories_id']] = $categories['parent_id'];
$category_name_tree_array[$categories['categories_id']] = $categories['categories_name'];
}
$categories_trees_array = array('parent_tree' => $category_tree_array, 'name_tree' => $category_name_tree_array);
return $categories_trees_array;
}
function getParentCategory($category_id) {
foreach ($this->category_tree as $categories_id => $parent_id) {
if ($categories_id == $category_id) {
return $parent_id;
}
} // end foreach ($this->category_tree as $categories_id => $parent_id)
return false;
}
function countChildCategories($category_id) {
$count = 0;
foreach ($this->category_tree as $categories_id => $parent_id) {
if ($parent_id == $category_id) {
$count += 1;
}
} // end foreach ($this->category_tree as $categories_id => $parent_id)
return $count;
}
function getChildCategories($category_id) {
foreach ($this->category_tree as $categories_id => $parent_id) {
if ($parent_id == $category_id) {
$child_category[] = array('categories_id' => $categories_id, 'categories_name' => $this->category_name_tree[$categories_id]);
}
} // end foreach ($this->category_tree as $categories_id => $parent_id)
return $child_category;
}
}
?>
The adapted categories enhanced box:
<?php
/*
$Id: categories.php,v 1.25 2003/07/09 01:13:58 hpdl Exp $
osCommerce, Open Source E-Commerce Solutions
http://www.oscommerce.com
Copyright (c) 2003 osCommerce
Released under the GNU General Public License
changed August 2003, by Nils Petersson
contribution "Category Box Enhancement", version 1.1
version with enhanced CountProductsStore object
*/
function tep_show_category($counter) {
// BoF - Contribution Category Box Enhancement 1.1
global $tree, $categories_string, $cPath_array, $cat_name;
for ($i=0; $i<$tree[$counter]['level']; $i++) {
$categories_string .= " ";
}
$cPath_new = 'cPath=' . $tree[$counter]['path'];
if (isset($cPath_array) && in_array($counter, $cPath_array) && $cat_name == $tree[$counter]['name']) { //Link nicht anklickbar, wenn angewählt
$categories_string .= '<a href="';
$categories_string .= tep_href_link(FILENAME_DEFAULT, $cPath_new) . '">'; //Link nicht anklickbar, wenn angewählt
} else { //Link nicht anklickbar, wenn angewählt
$categories_string .= '<a href="';
$categories_string .= tep_href_link(FILENAME_DEFAULT, $cPath_new) . '">';
} //Link nicht anklickbar, wenn angewählt
if (tep_has_category_subcategories($counter)) {
$categories_string .= tep_image(DIR_WS_IMAGES . 'pointer_blue.gif', '');
}
else {
$categories_string .= tep_image(DIR_WS_IMAGES . 'pointer_blue_light.gif', '');
}
if (isset($cPath_array) && in_array($counter, $cPath_array)) {
$categories_string .= '<b>';
}
if ($cat_name == $tree[$counter]['name']) {
$categories_string .= '<span class="errorText">';
}
// display category name
$categories_string .= $tree[$counter]['name'];
if ($cat_name == $tree[$counter]['name']) {
$categories_string .= '</span>';
}
if (isset($cPath_array) && in_array($counter, $cPath_array)) {
$categories_string .= '</b>';
}
// EoF Category Box Enhancement
$categories_string .= '</a>';
if (SHOW_COUNTS == 'true') {
$products_in_category = tep_count_products_in_category($counter);
if ($products_in_category > 0) {
$categories_string .= ' (' . $products_in_category . ')';
}
}
$categories_string .= '<br>';
if ($tree[$counter]['next_id'] != false) {
tep_show_category($tree[$counter]['next_id']);
}
}
?>
<!-- categories //-->
<tr>
<td>
<?php
// BoF - Contribution Category Box Enhancement 1.1
if (isset($cPath_array)) {
$cat_name = '';
for ($i=0, $n=sizeof($cPath_array); $i<$n; $i++) {
if (isset($countproducts->category_name_tree[(int)$cPath_array[$i]])) {
$cat_name = $countproducts->category_name_tree[(int)$cPath_array[$i]];
}
}
$cat_name = $categories['categories_name'];
}
// EoF Category Box Enhancement
// display category name
$info_box_contents = array();
$info_box_contents[] = array('text' => BOX_HEADING_CATEGORIES);
new infoBoxHeading($info_box_contents, true, false);
$categories_string = '';
$tree = array();
$ChildCategories = $countproducts->getChildCategories('0');
if (count($ChildCategories) > 0) {
foreach ($ChildCategories as $chca_key => $id_name_array) {
$tree[$id_name_array['categories_id']] = array('name' => $id_name_array['categories_name'],
'parent' => '0',
'level' => 0,
'path' => $id_name_array['categories_id'],
'next_id' => false);
if (isset($parent_id)) {
$tree[$parent_id]['next_id'] = $id_name_array['categories_id'];
}
$parent_id = $id_name_array['categories_id'];
if (!isset($first_element)) {
$first_element = $id_name_array['categories_id'];
}
}
} // end if (count($ChildCategories) > 0)
//------------------------
if (tep_not_null($cPath)) {
$new_path = '';
reset($cPath_array);
while (list($key, $value) = each($cPath_array)) {
unset($parent_id);
unset($first_id);
$ChildCategories = $countproducts->getChildCategories((int)$value);
if (count($ChildCategories) > 0) {
$new_path .= $value;
foreach ($ChildCategories as $chca_key => $id_name_array) {
$tree[$id_name_array['categories_id']] = array('name' => $id_name_array['categories_name'],
'parent' => (int)$value,
'level' => $key+1,
'path' => $new_path . '_' . $id_name_array['categories_id'],
'next_id' => false);
if (isset($parent_id)) {
$tree[$parent_id]['next_id'] = $id_name_array['categories_id'];
}
$parent_id = $id_name_array['categories_id'];
if (!isset($first_id)) {
$first_id = $id_name_array['categories_id'];
}
$last_id = $id_name_array['categories_id'];
}
$tree[$last_id]['next_id'] = $tree[$value]['next_id'];
$tree[$value]['next_id'] = $first_id;
$new_path .= '_';
} else {
break;
}
} // end while (list($key, $value) = each($cPath_array))
}
tep_show_category($first_element);
$info_box_contents = array();
$info_box_contents[] = array('text' => $categories_string);
if ( (basename($PHP_SELF) != FILENAME_SPECIALS)) {
$info_box_contents[] = array('align' => 'left',
'text' => '<font size=-2><b><a href="' . tep_href_link(FILENAME_SPECIALS, '', 'NONSSL') . '">' . BOX_INFORMATION_SPECIALS . '</a></b></font>');
}else{
$info_box_contents[] = array('align' => 'left',
'text' => '<font size=-2><b><a href="' . tep_href_link(FILENAME_SPECIALS, '', 'NONSSL') . '"><span class="errorText">' . BOX_INFORMATION_SPECIALS . '</a></b></font></span>');
}
if ( (basename($PHP_SELF) != FILENAME_PRODUCTS_NEW)) {
$info_box_contents[] = array('align' => 'left',
'text' => '<font size=-2><b><a href="' . tep_href_link(FILENAME_PRODUCTS_NEW, '', 'NONSSL') . '">' . BOX_INFORMATION_PRODUCTS_NEW . '</a></b></font>');
}else{
$info_box_contents[] = array('align' => 'left',
'text' => '<font size=-2><b><a href="' . tep_href_link(FILENAME_PRODUCTS_NEW, '', 'NONSSL') . '"><span class="errorText">' . BOX_INFORMATION_PRODUCTS_NEW . '</a></b></font></span>');
}
function tep_get_paths($categories_array = '', $parent_id = '0', $indent = '', $path='') {
global $languages_id, $countproducts;
if (!is_array($categories_array)) $categories_array = array();
$ChildCategories = $countproducts->getChildCategories((int)$parent_id);
foreach ($ChildCategories as $chca_key => $id_name_array) {
if ($parent_id=='0'){
$categories_array[] = array('id' => $id_name_array['categories_id'],
'text' => $indent . $id_name_array['categories_name']);
}
else{
$categories_array[] = array('id' => $path . $parent_id . '_' .$id_name_array['categories_id'],
'text' => $indent . $id_name_array['categories_name']);
}
if ($id_name_array['categories_id'] != $parent_id) {
$this_path=$path;
if ($parent_id != '0')
$this_path = $path . $parent_id . '_';
$categories_array = tep_get_paths($categories_array, $id_name_array['categories_id'], $indent . ' ', $this_path);
}
}
return $categories_array;
}
$info_box_contents[] = array('form' => '<form action="' . tep_href_link(FILENAME_DEFAULT) . '" method="get">' . tep_hide_session_id(),
'align' => 'left',
'text' => '<b>' . BOX_DROPDOWN_MENUS . '<br>' . tep_draw_pull_down_menu('cPath', tep_get_paths(array(array('id' => '', 'text' => PULL_DOWN_DEFAULT))), $cPath, 'onchange="this.form.submit();" style="width: 100%"')
);
new infoBox($info_box_contents);
?>
</td>
</tr>
<!-- categories_eof //-->
Edited by Jan Zonjee, 22 June 2010, 23:35.
added new code
!!
