kernelpanik Posted July 5, 2005 Posted July 5, 2005 Hello and welcome to Challenge Tuesday!! Step right up, buy a cake, take a seat and have a read :) Current situation : Select a manufacturer from the manufacturer drop down and you are brought to a page with all products from this manufacturer; not a good idea (for larger stores) if there are 4000 prods from this manu alone. Ideal situation : Select a manufacturer from the manufacturer drop down and you are brought to a page that replicates the category tree in which this manufacturer has products. Then, you can browse through this as usual, but products are from the selected manufacturer alone. I had a look through the contributions/forums - got this : http://www.oscommerce.com/community/contri...arch,mini+store It blew up when I installed it (thankfully, nobody was hurt), so I presume that it is just an old incompatible version. Does anyone know of a recent version or am I wrong in my presumption? If not, I will be using this as a base and writing a new version (has to be done by this Friday...hahahah), so I will post back here with more info later. Any tips would be appreciated!
♥bruyndoncx Posted July 5, 2005 Posted July 5, 2005 using a file comparison tool like beyond compare or winmerge should help you in seeing the differences between these MS1 files and your MS2 files. There are probably some small differences, but overall it should be easy to detect the changes you need as they'll have some obvious mention of manufacturers_id, using a file comparison tool makes it easier to also see the closing brackets, which you otherwise tend to overlook ... KEEP CALM AND CARRY ON I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support). So if you are still here ? What are you waiting for ?! Find the most frequent unique errors to fix: grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt
kernelpanik Posted July 5, 2005 Author Posted July 5, 2005 using a file comparison tool like beyond compare or winmerge should help you in seeing the differences between these MS1 files and your MS2 files. There are probably some small differences, but overall it should be easy to detect the changes you need as they'll have some obvious mention of manufacturers_id, using a file comparison tool makes it easier to also see the closing brackets, which you otherwise tend to overlook ... <{POST_SNAPBACK}> Thanks for the advice, but after doing that, there were so many little differences that I couldn't tell which lines to copy from one file to another. In the end, I went through the code and found the following : includes/functions/general.php::tep_category_has_manufacturer_products from the contribution mentioned above. This seems to cycle through categories to see if the current manufacturer has products listed, which is what is needed. So I added code that used this method. So, the code shows a list of categories that are only relevant to the current manufacturer. The thing is, this is soooo slow to execute. My clients DB has 2700 categories and 24000 products and recursing through each category is killing it,as far as I can tell. I was thinking of creating a table of manufacturers to categories or something like that, when the database is created; and so taking the load off the website by stopping it from working it out on the fly. Anyone have any ideas???
Guest Posted July 5, 2005 Posted July 5, 2005 Thanks for the advice, but after doing that, there were so many little differences that I couldn't tell which lines to copy from one file to another. In the end, I went through the code and found the following : includes/functions/general.php::tep_category_has_manufacturer_products from the contribution mentioned above. This seems to cycle through categories to see if the current manufacturer has products listed, which is what is needed. So I added code that used this method. So, the code shows a list of categories that are only relevant to the current manufacturer. The thing is, this is soooo slow to execute. My clients DB has 2700 categories and 24000 products and recursing through each category is killing it,as far as I can tell. I was thinking of creating a table of manufacturers to categories or something like that, when the database is created; and so taking the load off the website by stopping it from working it out on the fly. Anyone have any ideas??? <{POST_SNAPBACK}> Can you post up the query that does the work, it may need optimising.
♥bruyndoncx Posted July 5, 2005 Posted July 5, 2005 actually, the issue is that a query is done for every category. you should rewrite this piece of code to just execute one query (in the catalog/include/categories.php code) and fetch the category count from the results array (in place of the current function call). the query pseudo code (not tested, but you get the idea): select p2c.categories_id, count(*) as total from products p, products_to_categories p2c where p.products_id = p2c.products_id and p.manufacturers_id = $HTTP_GET_VARS['manufacturers_id'] group by p2c.categories_id KEEP CALM AND CARRY ON I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support). So if you are still here ? What are you waiting for ?! Find the most frequent unique errors to fix: grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt
kernelpanik Posted July 6, 2005 Author Posted July 6, 2005 Thanks for the advice. I have gotten it to work now, but I am using a hack that other people won't be able to use. Maybe someone would be able to take this code and adapt it for everyone. However, I have to say that I have such a small amount of experience in SQL and osCommerce that I'm flying by the seat of my pants here but no matter, here is what I did : In general.php, I added the code below which was taken from http://www.oscommerce.com/community/contri...arch,mini+store // Find out whether or not this category, or any of its subcategories, has products of a manufacturer // this function should automatically work if HTTP_GET_VARS['manufacturers_id'] is set, assuming // the tep_count_products_in_category() function has been patched. // TABLES: categories // Author: Neo Era Media, Inc. <[email protected]> function tep_category_has_manufacturer_products($category_id) { if (tep_count_products_in_category($category_id) > 0) { return true; }else if (tep_has_category_subcategories($category_id)) { $categories_query = tep_db_query("select c.categories_id, cd.categories_name, c.categories_image, c.parent_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . $current_category_id . "' and c.categories_id = cd.categories_id and cd.language_id = '" . $languages_id . "' order by sort_order, cd.categories_name"); while ($categories_values = tep_db_fetch_array($categories_query)) { if ($val = tep_category_has_manufacturer_products($categories_values['categories_id'])) { return true; } } return false; } } Then, I updated general.php::tep_count_products_in_category with the following code, which was also taken from the above contrib : // Return the number of products in a category // TABLES: products, products_to_categories, categories function tep_count_products_in_category($category_id, $include_inactive = false) { global $HTTP_GET_VARS; $products_count = 0; if ($include_inactive == true) { if ($HTTP_GET_VARS['manufacturers_id'] && $HTTP_GET_VARS['manufacturers_id'] > 0) { $products_query = tep_db_query("select count(*) as total from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_MANUFACTURERS . " m where p.products_id = p2c.products_id and p2c.categories_id = '" . $category_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . $HTTP_GET_VARS['manufacturers_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 p2c.categories_id = '" . (int)$category_id . "'"); } } else { if ($HTTP_GET_VARS['manufacturers_id'] && $HTTP_GET_VARS['manufacturers_id'] > 0) { $products_query = tep_db_query("select count(*) as total from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_MANUFACTURERS . " m where p.products_id = p2c.products_id and p.products_status = 1 and p2c.categories_id = '" . $category_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . $HTTP_GET_VARS['manufacturers_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; } The osCommerce I installed had SubCat 1.1, http://www.oscommerce.com/community/contri...l/search,subcat, so I decided to make my changes to that in index.php. Basically, I added code from the main else branch into the if branch. So, that contribution then became : <!-- BOF: Show subcategories in Product Listing --> <tr> <td><table border="0" width="100%" cellspacing="0" cellpadding="2"><tr><?php //EDITED by kernelpanik for Manufacturer sorting //check to see if the manufacturer sorting has been chosen if (isset($HTTP_GET_VARS['manufacturers_id'])) { //check to see if the parent_id field has been set and hence one of the dynamic links below have been clicked //set the category to search if(isset($HTTP_GET_VARS['parent_id'])) { $cat_to_search = $HTTP_GET_VARS['parent_id']; } else { $cat_to_search = 0; } //get the manufacturer name, this is needed to redirect to the correct cPath later $manufacturer_query = tep_db_query("select m.manufacturers_name as name from manufacturers as m where m.manufacturers_id = '" . $HTTP_GET_VARS['manufacturers_id'] . "'"); $manufacturer_result = tep_db_fetch_array($manufacturer_query); // get all the categories that have a parent id of the category chosen $categories_query = tep_db_query("select c.categories_id as id, cd.categories_name as name from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.categories_id = cd.categories_id and c.parent_id = '" . $cat_to_search . "'"); //if there are categories within the category chosen if (tep_db_num_rows($categories_query) > 0 ) { $rows = 0; while ($categories = tep_db_fetch_array($categories_query)) { $rows++; //before creating links etc with category ids, check against tep_category_has_manufacturers_products if(tep_category_has_manufacturer_products($categories['id'])) { $width = (int)(100 / MAX_DISPLAY_CATEGORIES_PER_ROW) . '%'; //if the manufacturers name is the same as the category name, then //create the link as a redirect to the relevant category //otherwise create a link which will execute this code path again for subcategories if ($manufacturer_result['name'] == $categories['name']) { //took out image reference below because I dont have any of those echo '<td align="center" class="smallText" style="width: ' . $width . '" valign="top"><a href="index.php?cPath=' . $categories['id'] . '">' . $categories['name'] . '</a></td>' . "\n"; } else { //took out image reference below because I dont have any of those echo '<td align="center" class="smallText" style="width: ' . $width . '" valign="top"><a href="index.php?manufacturers_id=' . $HTTP_GET_VARS['manufacturers_id'] . '&parent_id=' . $categories['id'] . '">' . $categories['name'] . '</a></td>' . "\n"; } if ((($rows / MAX_DISPLAY_CATEGORIES_PER_ROW) == floor($rows / MAX_DISPLAY_CATEGORIES_PER_ROW)) && ($rows != tep_db_num_rows($categories_query))) { echo ' </tr>' . "\n"; echo ' <tr>' . "\n"; } } } } }else{ //this is where products are shown once you have gone down the category tree if (isset($cPath)) { if (ereg('_', $cPath)) { $category_links = array_reverse($cPath_array); $cat_to_search = $category_links[0]; } else { $cat_to_search = $cPath; } // check to see if there are deeper categories within the current category $categories_query = tep_db_query("select c.categories_id, cd.categories_name, c.categories_image, c.parent_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . $cat_to_search . "' and c.categories_id = cd.categories_id and cd.language_id = '" . $languages_id . "' order by sort_order, cd.categories_name"); if (tep_db_num_rows($categories_query) > 0 ) { $rows = 0; while ($categories = tep_db_fetch_array($categories_query)) { $rows++; $cPath_new = tep_get_path($categories['categories_id']); $width = (int)(100 / MAX_DISPLAY_CATEGORIES_PER_ROW) . '%'; echo ' <td align="center" class="smallText" style="width: ' . $width . '" valign="top"><a href="' . tep_href_link(FILENAME_DEFAULT, $cPath_new) . '">' . tep_image(DIR_WS_IMAGES . $categories['categories_image'], $categories['categories_name'], SUBCATEGORY_IMAGE_WIDTH, SUBCATEGORY_IMAGE_HEIGHT) . '<br>' . $categories['categories_name'] . '</a></td>' . "\n"; if ((($rows / MAX_DISPLAY_CATEGORIES_PER_ROW) == floor($rows / MAX_DISPLAY_CATEGORIES_PER_ROW)) && ($rows != tep_db_num_rows($categories_query))) { echo ' </tr>' . "\n"; echo ' <tr>' . "\n"; } } } } } ?></tr></table></td> </tr> <tr> <td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td> </tr> <!-- EOF: Show subcategories in Product Listing --> This does what I need it to do. This only works for me due to the fact that my client has the manufacturer as a category as well, i.e. Accessories->Keyboards->Microsoft->Keyboard A etc. My next step is to change tep_category_has_manufacturer_products so that it doesn't search the categories for products belonging to the manufacturer but rather just searches for the name of the manufacturer in the tree of categories. I'm sure there are a huge amount of improvements that could be made here; the most obvious being the change so that people (nearly everyone I presume) who don't have the manufacturer as a category can use it. Other improvements would be to handle the cPath correctly, so that the crumbs work . That's what I've got anyway.
kernelpanik Posted July 6, 2005 Author Posted July 6, 2005 I think, actually, that there are two searches going on in that code now that I look at it closer. I don't think I should have added this line and the while related to it : $categories_query = tep_db_query("select c.categories_id as id, cd.categories_name as name from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.categories_id = cd.categories_id and c.parent_id = '" . $cat_to_search . "'"); If I just provide the id to tep_category_has_manufacturer_products, it will recursviely search anyway...I think :)
Recommended Posts
Archived
This topic is now archived and is closed to further replies.