Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Simple & Fast Query INFOBOX Categories


Parikesit

Recommended Posts

Dear all,

 

The default INFOBOX categories (still) works and give visitors categories tree. However it's done by make a lot of queries to our database that make a quite time for displaying page. It's will spend more time if you have hundreds categories... <_<

 

Here I just want to share a litle trick to catch categories by one single query.

 

First step:

  
 if ($cPath) {
   $cPath_new = str_replace('_',',',$cPath);
   $categories_query = tep_db_query("select cc.sort_order as tree, c.categories_id, FIELD(c.parent_id,".$cPath_new.") as level, FIND_IN_SET(c.categories_id,'".$cPath_new."') as child, REPLACE(SUBSTRING_INDEX('".$cPath_new."', ',', FIELD(c.parent_id,".$cPath_new.")), ',', '_') as path, cd.categories_name from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES . " cc, " . TABLE_CATEGORIES_DESCRIPTION . " cd where cc.parent_id=0 and FIND_IN_SET(cc.categories_id, CONCAT_WS(',', SUBSTRING_INDEX('".$cPath_new."', ',', FIELD(c.parent_id,".$cPath_new.")), c.categories_id)  ) and c.categories_id = cd.categories_id and FIND_IN_SET(c.parent_id, '0," . $cPath_new . "') and cd.language_id='" . (int)$languages_id ."' order by tree, path, sort_order, cd.categories_name");
 } else {
   $cPath_new = '';
   $categories_query = tep_db_query("select c.sort_order as tree, c.categories_id, '0' as level, '0' as child, NULL as path, cd.categories_name from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.categories_id = cd.categories_id and FIND_IN_SET(c.parent_id, '0') and cd.language_id='" . (int)$languages_id ."' order by tree, path, sort_order, cd.categories_name");
 }

 

This query will give us:

  • tree : with path, to sort the categories base on top-parent sort order
  • path : with tree, to sort the categories base on top-parent sort order. This also required to create href-link.
  • categories_name : the categories name
  • categories_id : this categories id
  • level : categories_level (0,1,2,...n)
  • child : is the categories have child categories (0,1)

The next step are here:

  $categories_string = '';
 $cPath_array = explode(',', $cPath_new);
 if (tep_db_num_rows($categories_query)) {
   while ($row = tep_db_fetch_array($categories_query)) {
     $linkPath = 'cPath=' . (($row['path'] == '')? $row['categories_id'] : $row['path'] . '_' . $row['categories_id']);
     if (in_array($row['categories_id'], $cPath_array)) {
       $categories_string .= '<div style="margin:0px 0px 0px ' . $row['level']*8 . 'px;"><b><a href="' . tep_href_link(FILENAME_DEFAULT, $linkPath) . '">' . $row['categories_name'] . '</a></b></div>';
     } else {
       $categories_string .= '<div style="margin:0px 0px 0px ' . $row['level']*8 . 'px;"><a href="' . tep_href_link(FILENAME_DEFAULT, $linkPath) . '">' . $row['categories_name'] . '</a></div>';
     }
   }
 }

 

Instead of using ' " or images, I'm using <div> style to indent the sub-categories, like:

'<div style="margin:0px 0px 0px ' . $row['level']*8 . 'px;">' . $SOME_TEXT . '</div>'

 

Also there are important step above to create cPath link:

$linkPath = 'cPath=' . (($row['path'] == '')? $row['categories_id'] : $row['path'] . '_' . $row['categories_id']);

 

The last step:

  $info_box_contents = array();
 $info_box_contents[] = array('text' => $categories_string);

 new infoBox($info_box_contents);

 

Best,

zaenal

Link to comment
Share on other sites

Here the complete script for catalog/includes/boxes/categories.php

 

 

<?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
 
 Modification: Zaenal <[email protected]>
 
 Released under the GNU General Public License
*/
?>
<!-- categories //-->
         <tr>
           <td>
<?php
 $info_box_contents = array();
 $info_box_contents[] = array('text' => BOX_HEADING_CATEGORIES);

 new infoBoxHeading($info_box_contents, true, false);


 if ($cPath) {
   $cPath_new = str_replace('_',',',$cPath);
   $categories_query = tep_db_query("select cc.sort_order as tree, c.categories_id, FIELD(c.parent_id,".$cPath_new.") as level, FIND_IN_SET(c.categories_id,'".$cPath_new."') as child, REPLACE(SUBSTRING_INDEX('".$cPath_new."', ',', FIELD(c.parent_id,".$cPath_new.")), ',', '_') as path, cd.categories_name from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES . " cc, " . TABLE_CATEGORIES_DESCRIPTION . " cd where cc.parent_id=0 and FIND_IN_SET(cc.categories_id, CONCAT_WS(',', SUBSTRING_INDEX('".$cPath_new."', ',', FIELD(c.parent_id,".$cPath_new.")), c.categories_id)  ) and c.categories_id = cd.categories_id and FIND_IN_SET(c.parent_id, '0," . $cPath_new . "') and cd.language_id='" . (int)$languages_id ."' order by tree, path, sort_order, cd.categories_name");
 } else {
   $cPath_new = '';
   $categories_query = tep_db_query("select c.sort_order as tree, c.categories_id, '0' as level, '0' as child, NULL as path, cd.categories_name from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.categories_id = cd.categories_id and FIND_IN_SET(c.parent_id, '0') and cd.language_id='" . (int)$languages_id ."' order by tree, path, sort_order, cd.categories_name");
 }

 $categories_string = '';
 $cPath_array = explode(',', $cPath_new);
 if (tep_db_num_rows($categories_query)) {
   while ($row = tep_db_fetch_array($categories_query)) {
     $linkPath = 'cPath=' . (($row['path'] == '')? $row['categories_id'] : $row['path'] . '_' . $row['categories_id']);

     if (in_array($row['categories_id'], $cPath_array)) {
       $categories_string .= '<div style="margin:0px 0px 0px ' . $row['level']*8 . 'px;"><b><a href="' . tep_href_link(FILENAME_DEFAULT, $linkPath) . '">' . $row['categories_name'] . '</a></b></div>';
     } else {
       $categories_string .= '<div style="margin:0px 0px 0px ' . $row['level']*8 . 'px;"><a href="' . tep_href_link(FILENAME_DEFAULT, $linkPath) . '">' . $row['categories_name'] . '</a></div>';
     }

   }
 }

 $info_box_contents = array();
 $info_box_contents[] = array('text' => $categories_string);

 new infoBox($info_box_contents);
 
?>
           </td>
         </tr>
<!-- categories_eof //-->

 

 

Best,

zaenal

Link to comment
Share on other sites

Hi

 

great bit of code (still trying to figure it out :rolleyes: ) it has really speeded things up (osc was really slow after adding over 600 categories).

 

I found 2 problems, one which I may have corrected and one small problem still outstanding.

 

the first, I was getting an SQL error something like: 'ambiguous sort_order in group by clause'

 

this was fixed by changing sort_order to cc.sort_order (see code below)

FIELD(c.parent_id,".$cPath_new.")), c.categories_id)  ) and c.categories_id = cd.categories_id and FIND_IN_SET(c.parent_id, '0," . $cPath_new . "') and cd.language_id='" . (int)$languages_id ."' order by tree, path, cc.sort_order, cd.categories_name");
} else {
  $cPath_new = '';

 

This stops the error, but there is still a problem since the expanded subcategories appear at the bottom of the category list rather than in their correct location.

 

If you have a solution to this I would be greatful. If I figure it out myself, I will post it here.

 

It really does make make an amazing difference, many thanks.

 

showman

Link to comment
Share on other sites

Hi,

Thank for reply.

Sorry, there are miss-typing in previous thread.

 

The order by must be:

..."order by tree, path, c.sort_order, cd.categories_name", not cc.sort_order.

 

 

Here the complete code:

 

  if ($cPath) {
   $cPath_new = str_replace('_',',',$cPath);
   $categories_query = tep_db_query("select cc.sort_order as tree, c.categories_id, FIELD(c.parent_id,".$cPath_new.") as level, FIND_IN_SET(c.categories_id,'".$cPath_new."') as child, REPLACE(SUBSTRING_INDEX('".$cPath_new."', ',', FIELD(c.parent_id,".$cPath_new.")), ',', '_') as path, cd.categories_name from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES . " cc, " . TABLE_CATEGORIES_DESCRIPTION . " cd where cc.parent_id=0 and FIND_IN_SET(cc.categories_id, CONCAT_WS(',', SUBSTRING_INDEX('".$cPath_new."', ',', FIELD(c.parent_id,".$cPath_new.")), c.categories_id)  ) and c.categories_id = cd.categories_id and FIND_IN_SET(c.parent_id, '0," . $cPath_new . "') and cd.language_id='" . (int)$languages_id ."' order by tree, path, c.sort_order, cd.categories_name");
 } else {
   $cPath_new = '';
   $categories_query = tep_db_query("select c.sort_order as tree, c.categories_id, '0' as level, '0' as child, NULL as path, cd.categories_name from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.categories_id = cd.categories_id and FIND_IN_SET(c.parent_id, '0') and cd.language_id='" . (int)$languages_id ."' order by tree, path, c.sort_order, cd.categories_name");
 }

 

 

Best,

zaenal

Link to comment
Share on other sites

Litle fixing to sort order:

 

if ($cPath) {

  ... order by tree, path, c.sort_order, cd.categories_name");

} else {

  ... order by tree, cd.categories_name");

}

 

Best,

zaenal

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...