Dirty Butter Posted August 4, 2015 Share Posted August 4, 2015 When I started our plush store I had about 500 items listed. I'm now closing in on 4,000. Most of our Categories were created a long time ago, but I feel sure there are categories I need to add to feature brands with large numbers of listings. The trouble is - how to easily determine how many listings there are for each manufacturer. Any ideas? Edited 6/25/16 - Bsmither provided the code to fix this, but I had trouble editing the code correctly. So he helped me again with this today, and you can jump to the directions I was able to follow correctly HERE: Quote Link to comment Share on other sites More sharing options...
bsmither Posted August 4, 2015 Share Posted August 4, 2015 In the admin, Manufacturers screen, would a column/tally of the products assigned to each manufacturer be sufficient? Quote Link to comment Share on other sites More sharing options...
Dirty Butter Posted August 4, 2015 Author Share Posted August 4, 2015 yes it would, although it would be even nicer if it would sort Quote Link to comment Share on other sites More sharing options...
bsmither Posted August 5, 2015 Share Posted August 5, 2015 We can work on the sort later.In products.manufacturers.inc.php:Find near line 76: if (($manufacturers = $GLOBALS['db']->select('CubeCart_manufacturers', array('id', 'image'), false, 'name', $per_page, $page)) !== false) { Change to: if (($manufacturers = $GLOBALS['db']->select('CubeCart_manufacturers',"`id`, `image`, (SELECT COUNT('product_id') FROM ".$GLOBALS['config']->get('config', 'dbprefix')."CubeCart_inventory WHERE manufacturer = id) AS manu_prod_count", false, 'name', $per_page, $page)) !== false) { In the template products.manufacturers.php:Find near line 19: <tr> <td width="250">{$LANG.catalogue.manufacturer}</td> <td>{$LANG.form.action}</td> </tr> Change to: <tr> <td width="250">{$LANG.catalogue.manufacturer}</td> <td>#Prods</td> <td>{$LANG.form.action}</td> </tr> Find near line 25: {foreach from=$MANUFACTURERS item=manufacturer} <tr> <td>{$manufacturer.name}</td> <td> Change to: {foreach from=$MANUFACTURERS item=manufacturer} <tr> <td>{$manufacturer.name}</td> <td align="right">{$manufacturer.manu_prod_count}</td> <td> Quote Link to comment Share on other sites More sharing options...
Dirty Butter Posted August 5, 2015 Author Share Posted August 5, 2015 (edited) Worked like a charm. Thank you! With close to 400 manufacturers, it would be nice to be able to sort, but certainly not necessary. Edited August 5, 2015 by Dirty Butter Quote Link to comment Share on other sites More sharing options...
havenswift-hosting Posted August 5, 2015 Share Posted August 5, 2015 Rosemary - we have an Enhanced Manufacturers plugin just available that gives each manufacturer a front end listing page similar to the category pages and also gives them a page where you can list details of each of them. There are two ways of displaying the manufacturers (dropdown or list or indeed you can even have both with different manufacturers assigned to each style - useful if you wish to highlight one or two specific manufacturers rather than being in a dropdown box). You can see it working on our demo website https://www.cubecart-demo.co.uk although it is currently only in the new "Nautical" skin which you would need to change to. This and the default "RetailTherapy" are two skins we are just completing and will be available soon. Your request for product listing by manufacturer within the admin side was also requested two days ago by one of our first users of this plugin so we are going to be adding that very shortly - we will even include sorting !ThanksIan Quote Link to comment Share on other sites More sharing options...
bsmither Posted August 5, 2015 Share Posted August 5, 2015 Here is a redeux of the edits with sorting.In products.manufacturers.inc.php:Find near line 76: if (($manufacturers = $GLOBALS['db']->select('CubeCart_manufacturers', array('id', 'image'), false, 'name', $per_page, $page)) !== false) { Change to: if (($manufacturers = $GLOBALS['db']->select('CubeCart_manufacturers',"`id`, `image`, (SELECT COUNT('product_id') FROM ".$GLOBALS['config']->get('config', 'dbprefix')."CubeCart_inventory WHERE manufacturer = id) AS manu_prod_count", false, $_GET['sort'], $per_page, $page)) !== false) {Find near line 68: $GLOBALS['smarty']->assign('DISPLAY_FORM', true); } else { Add After: /* NEW */ // Sorting $current_page = currentPage(array('sort')); if (!isset($_GET['sort']) || !is_array($_GET['sort'])) { $_GET['sort'] = array('name' => 'DESC'); } $thead_sort = array ( 'name' => $GLOBALS['db']->column_sort('name', $lang['catalogue']['manufacturer'], 'sort', $current_page, $_GET['sort']), 'no_prods' => $GLOBALS['db']->column_sort('manu_prod_count', "#Prods", 'sort', $current_page, $_GET['sort']), 'action' => $lang['form']['action'], ); $GLOBALS['smarty']->assign('THEAD', $thead_sort); /* /NEW */ In the template products.manufacturers.php:Find near line 19: <tr> <td width="250">{$LANG.catalogue.manufacturer}</td> <td>{$LANG.form.action}</td> </tr> Change to: <tr> <th nowrap="nowrap" width="250">{$THEAD.name}</th> <th nowrap="nowrap">{$THEAD.no_prods}</th> <th nowrap="nowrap">{$THEAD.action}</th> </tr> Find near line 25: {foreach from=$MANUFACTURERS item=manufacturer} <tr> <td>{$manufacturer.name}</td> <td> Change to: {foreach from=$MANUFACTURERS item=manufacturer} <tr> <td>{$manufacturer.name}</td> <td align="right">{$manufacturer.manu_prod_count}</td> <td> Quote Link to comment Share on other sites More sharing options...
Dirty Butter Posted August 5, 2015 Author Share Posted August 5, 2015 It's not sorting. Knowing me, I probably just got lost trying to mesh the count and sort codes together:Here's my products.manufacturer.inc.php section: $GLOBALS['smarty']->assign('DISPLAY_FORM', true); } else { /* BSMITHER SORTED BRAND STOCK LEVEL */ // Sorting $current_page = currentPage(array('sort')); if (!isset($_GET['sort']) || !is_array($_GET['sort'])) { $_GET['sort'] = array('name' => 'DESC'); } $thead_sort = array ( 'name' => $GLOBALS['db']->column_sort('name', $lang['catalogue']['manufacturer'], 'sort', $current_page, $_GET['sort']), 'no_prods' => $GLOBALS['db']->column_sort('manu_prod_count', "#Prods", 'sort', $current_page, $_GET['sort']), 'action' => $lang['form']['action'], ); $GLOBALS['smarty']->assign('THEAD', $thead_sort); /* END BSMITHER SORTED BRAND STOCK LEVEL */ $GLOBALS['main']->addTabControl($lang['catalogue']['title_manufacturer'], 'manufacturers'); $GLOBALS['main']->addTabControl($lang['catalogue']['title_manufacturer_add'], 'manu_add'); $catalogue = Catalogue::getInstance(); $page = (isset($_GET['page'])) ? $_GET['page'] : 1; $per_page = 10; //BSMITHER SORTED BRAND STOCK LEVEL if (($manufacturers = $GLOBALS['db']->select('CubeCart_manufacturers',"`id`, `image`, (SELECT COUNT('product_id') FROM ".$GLOBALS['config']->get('config', 'dbprefix')."CubeCart_inventory WHERE manufacturer = id) AS manu_prod_count", false, $_GET['sort'], $per_page, $page)) !== false) { //END BSMITHER SORTED BRAND STOCK LEVEL And here's the products.manufacturer.php section:{* BSMITHER BRAND SORTED STOCK LEVEL *} <tr> <th nowrap="nowrap" width="250">{$THEAD.name}</th> <th nowrap="nowrap">{$THEAD.no_prods}</th> <th nowrap="nowrap">{$THEAD.action}</th> </tr> </thead> <tbody> {foreach from=$MANUFACTURERS item=manufacturer} <tr> <td>{$manufacturer.name}</td> <td align="right">{$manufacturer.manu_prod_count}</td> <td>{* END BSMITHER BRAND SORTED STOCK LEVEL *} Thank you for the information about your Enhanced Manufacturers plugin Ian. So many of our manufacturers in the list only have one or two items that putting the list in any way other than major categories on the front end probably wouldn't be useful for our customers. But it sounds like something a lot of sellers would find useful on their stores. Quote Link to comment Share on other sites More sharing options...
bsmither Posted August 5, 2015 Share Posted August 5, 2015 Looks good to me. Did you clear the skin cache? Does the URL that shows in your browser's address bar, after you click a sort header, look like, for example:/admin.php?_g=products&node=manufacturers&sort[manu_prod_count]=DESC Quote Link to comment Share on other sites More sharing options...
Dirty Butter Posted August 5, 2015 Author Share Posted August 5, 2015 ?_g=products&node=manufacturers&sort[manu_prod_count]=DESCI've changed the name of admin, but this appears to be the same as what you have. I cleared the cache, and, when that didn't fix it, I manually deleted all cache and rebooted FF. Quote Link to comment Share on other sites More sharing options...
bsmither Posted August 5, 2015 Share Posted August 5, 2015 And what happened? Quote Link to comment Share on other sites More sharing options...
Dirty Butter Posted August 5, 2015 Author Share Posted August 5, 2015 Sorry, it still doesn't work - I should have made that clear. Quote Link to comment Share on other sites More sharing options...
bsmither Posted August 5, 2015 Share Posted August 5, 2015 Enable CubeCart's debugging. (Be sure to enter your own IP address in the adjacent field.)When you click on a table header, which should be a link with a sort key/value, and the page gets refetched (it does fetch a new page, right?), the SQL queries in the debug section should have, near the bottom, something very similar to the following:[16] SHOW COLUMNS FROM CubeCart_manufacturers; -- (0.015625 sec) [NOT CACHED] [17] SELECT SQL_CALC_FOUND_ROWS `id`, `image`, (SELECT COUNT('product_id') FROM CubeCart_inventory WHERE manufacturer = id) AS manu_prod_count FROM `CubeCart_manufacturers` ORDER BY `name` ASC LIMIT 10 OFFSET 0; -- (0 sec) [NOT CACHED] [18] SELECT FOUND_ROWS() as Count; -- (0 sec) [NOT CACHED] [19] SELECT `name`, `URL` FROM `CubeCart_manufacturers` WHERE CubeCart_manufacturers.id = '2' ; -- (0 sec) [NOT CACHED] [20] SELECT `name`, `URL` FROM `CubeCart_manufacturers` WHERE CubeCart_manufacturers.id = '1' ; -- (0 sec) [NOT CACHED] [21] SELECT `name`, `URL` FROM `CubeCart_manufacturers` WHERE CubeCart_manufacturers.id = '3' ; -- (0 sec) [NOT CACHED]The ORDER BY `name` ASC changes depending in the sort key/value. Quote Link to comment Share on other sites More sharing options...
Dirty Butter Posted August 5, 2015 Author Share Posted August 5, 2015 [15] SELECT SQL_CALC_FOUND_ROWS `id`, `image`, (SELECT COUNT('product_id') FROM CubeCart_inventory WHERE manufacturer = id) AS manu_prod_count FROM `CubeCart_manufacturers` LIMIT 10 OFFSET 0; -- (0.144041061401 sec) [NOT CACHED] [16] SELECT FOUND_ROWS() as Count; -- (0.000195980072021 sec) [NOT CACHED] [17] SELECT `name`, `URL` FROM `CubeCart_manufacturers` WHERE CubeCart_manufacturers.id = '1' ; -- (0.000429153442383 sec) [NOT CACHED] [18] SELECT `name`, `URL` FROM `CubeCart_manufacturers` WHERE CubeCart_manufacturers.id = '2' ; -- (0.000168085098267 sec) [NOT CACHED] [19] SELECT `name`, `URL` FROM `CubeCart_manufacturers` WHERE CubeCart_manufacturers.id = '3' ; -- (0.000164985656738 sec) [NOT CACHED] [20] SELECT `name`, `URL` FROM `CubeCart_manufacturers` WHERE CubeCart_manufacturers.id = '4' ; -- (0.000190019607544 sec) [NOT CACHED] Quote Link to comment Share on other sites More sharing options...
bsmither Posted August 5, 2015 Share Posted August 5, 2015 This part of your query [15]:AS manu_prod_count FROM `CubeCart_manufacturers` LIMIT 10 OFFSET 0;is missing the ORDER BY clause.The part of the PHP statement of the revised select() call would include $_GET['sort']:... AS manu_prod_count", false, $_GET['sort'], $per_page, $page)) !== false) {I have no idea why your database class is not accepting $_GET['sort'] as the 'order_by' argument. It's there because the table header links have it. Quote Link to comment Share on other sites More sharing options...
Dirty Butter Posted August 5, 2015 Author Share Posted August 5, 2015 Well if YOU don't know, I SURE don't! But I can scan the list and find the ones I need to make sub-categories for, without the sort. And hopefully the sort will work for anyone else who reads this thread and can make use of your code. Thank you as always for all your help. Without your time and expertise this forum would probably fall apart - I know I would stay lost. Quote Link to comment Share on other sites More sharing options...
Dirty Butter Posted August 15, 2015 Author Share Posted August 15, 2015 I have added more products and more manufacturers since adding this code. I noticed that the new manufacturers added were NOT sorting, so I took the sort numbers out of the code you had provided. Of course sorting the numbers never worked for me, so for someone else the lack of sorting newly added manufacturers might not happen. Quote Link to comment Share on other sites More sharing options...
Dirty Butter Posted March 16, 2016 Author Share Posted March 16, 2016 Revisiting this for v6.0.11 upgrade, which included some changes to do with the manufactures in some way. products.manufacturers.inc.php 6.0.10 code: foreach ($GLOBALS['hooks']->load('admin.product.manufacturers.save.post_process') as $hook) include $hook; httpredir('?_g=products&node=manufacturers#tab_manufacturers'); } $GLOBALS['gui']->addBreadcrumb($lang['catalogue']['title_manufacturer'], currentPage(array('edit'))); foreach ($GLOBALS['hooks']->load('admin.product.manufacturer.pre_display') as $hook) include $hook; if (isset($_GET['edit']) && is_numeric($_GET['edit'])) { $GLOBALS['main']->addTabControl($lang['catalogue']['title_manufacturer'], false, currentPage(array('edit'))); $GLOBALS['main']->addTabControl($lang['catalogue']['title_manufacturer_edit'], 'manu_edit'); if (($manufacturers = $GLOBALS['db']->select('CubeCart_manufacturers', array('name', 'id', 'URL'), array('id' => (int)$_GET['edit']))) !== false) { $GLOBALS['smarty']->assign('EDIT', $manufacturers[0]); } else { $GLOBALS['main']->setACPWarning($lang['catalogue']['error_manufacturer_found']); httpredir(currentPage(array('edit'))); } $GLOBALS['smarty']->assign('DISPLAY_FORM', true); } else { $GLOBALS['main']->addTabControl($lang['catalogue']['title_manufacturer'], 'manufacturers'); $GLOBALS['main']->addTabControl($lang['catalogue']['title_manufacturer_add'], 'manu_add'); $catalogue = Catalogue::getInstance(); $page = (isset($_GET['page'])) ? $_GET['page'] : 1; $per_page = 10; //BSMITHER BRAND STOCK LEVEL if (($manufacturers = $GLOBALS['db']->select('CubeCart_manufacturers',"`id`, `image`, (SELECT COUNT('product_id') FROM ".$GLOBALS['config']->get('config', 'dbprefix')."CubeCart_inventory WHERE manufacturer = id) AS manu_prod_count", false, 'name', $per_page, $page)) !== false) { //END BSMITHER BRAND STOCK LEVEL $GLOBALS['smarty']->assign('PAGINATION', $GLOBALS['db']->pagination(false, $per_page, $page)); foreach ($manufacturers as $manufacturer) { $manufacturer['name'] = $catalogue->getManufacturer($manufacturer['id']); $smarty_data['manufacturers'][] = $manufacturer; } $GLOBALS['smarty']->assign('MANUFACTURERS', $smarty_data['manufacturers']); } $GLOBALS['smarty']->assign('DISPLAY_LIST', true); } $page_content = $GLOBALS['smarty']->fetch('templates/products.manufacturers.php'); 6.0.11 has this line instead of what Bsmither's brand stock level code has: if (($manufacturers = $GLOBALS['db']->select('CubeCart_manufacturers', false, false, 'name', $per_page, $page)) !== false) { What do I need to change, so I get the brand count again? Bsmither Brand Stock Level section is currently commented out. With the stock level edit active and the 6.0.11 line commented out, I have no manufacturer names showing at all. Brand counts, edit and delete icons are there, View All works, just no manufacture names. Quote Link to comment Share on other sites More sharing options...
bsmither Posted March 16, 2016 Share Posted March 16, 2016 Working with the 6011 code: Find: if (($manufacturers = $GLOBALS['db']->select('CubeCart_manufacturers', false, false, 'name', $per_page, $page)) !== false) { Change to: //BSMITHER BRAND PRODUCT COUNT if (($manufacturers = $GLOBALS['db']->select( 'CubeCart_manufacturers', "*, (SELECT COUNT('product_id') FROM ".$GLOBALS['config']->get('config', 'dbprefix')."CubeCart_inventory WHERE manufacturer = id) AS manu_prod_count", false, 'name', $per_page, $page)) !== false) { //END BSMITHER BRAND PRODUCT COUNT CC6010 used Catalogue->getManufacturer() to retrieve the 'name' to supplement having only retrieved the 'id' and 'image' from the database record. In CC6011, the entire record is retrieved, including the name, and the call to getManufacturer() for the missed name is dropped. Quote Link to comment Share on other sites More sharing options...
Dirty Butter Posted March 17, 2016 Author Share Posted March 17, 2016 THANK YOU!! I appreciate your help, as always. Quote Link to comment Share on other sites More sharing options...
Dirty Butter Posted June 24, 2016 Author Share Posted June 24, 2016 I'm revisiting this, attempting to get Sort to work - mine never did. I'm on 6.0.12. I'm approaching 5000 product listings now, and being able to sort the count is getting even more important, as I have almost 430 manufacturers listed. Quote Link to comment Share on other sites More sharing options...
bsmither Posted June 25, 2016 Share Posted June 25, 2016 I say we start over with a fresh products.manufacturers.inc.php, and apply these edits appropriately: Near line 83, find: if (($manufacturers = $GLOBALS['db']->select('CubeCart_manufacturers', false, false, 'name', $per_page, $page)) !== false) { Replace with: if (($manufacturers = $GLOBALS['db']->select('CubeCart_manufacturers',"*, (SELECT COUNT('product_id') FROM ".$GLOBALS['config']->get('config', 'dbprefix')."CubeCart_inventory WHERE manufacturer = id) AS manu_prod_count", false, 'name', $per_page, $page)) !== false) { With a fresh template products.manufacturers.php, apply these edits: Near line 21, find: <td>{$LANG.form.action}</td> Replace with: <td>#Prods</td><td>{$LANG.form.action}</td> Near line 28, find: <td> Replace with: <td align="right">{$manufacturer.manu_prod_count}</td><td> Test. Now for the sorting. Getting back to the products.manufacturers.inc.php, make these edits: The edited statement at line 83: if (($manufacturers = $GLOBALS['db']->select('CubeCart_manufacturers',"*, (SELECT COUNT('product_id') FROM ".$GLOBALS['config']->get('config', 'dbprefix')."CubeCart_inventory WHERE manufacturer = id) AS manu_prod_count", false, 'name', $per_page, $page)) !== false) { Replace with: if (($manufacturers = $GLOBALS['db']->select('CubeCart_manufacturers',"*, (SELECT COUNT('product_id') FROM ".$GLOBALS['config']->get('config', 'dbprefix')."CubeCart_inventory WHERE manufacturer = id) AS manu_prod_count", false, $sort_order_string, $per_page, $page)) !== false) { Near line 76, find: } else { Add AFTER: /* NEW */ // Sorting $current_page = currentPage(array('sort')); $sort_order_string = 'name ASC'; $sort_wanted = $_GET['sort']; $sortable_columns = array('name', 'manu_prod_count'); if (!empty($sort_wanted)) { $sort_order_string = (in_array(key($sort_wanted), $sortable_columns) ? key($sort_wanted) : 'name') . ' ' . ((current($sort_wanted) == "DESC") ? "DESC" : "ASC" ); } $thead_sort = array ( 'name' => $GLOBALS['db']->column_sort('name', $lang['catalogue']['manufacturer'], 'sort', $current_page, $_GET['sort']), 'no_prods' => $GLOBALS['db']->column_sort('manu_prod_count', "#Prods", 'sort', $current_page, $_GET['sort']), 'action' => $lang['form']['action'], ); $GLOBALS['smarty']->assign('THEAD', $thead_sort); /* /NEW */ Getting back to the template products.manufacturers,php: Find what is now: <thead> <tr> <td width="250">{$LANG.catalogue.manufacturer}</td> <td>#Prods</td><td>{$LANG.form.action}</td> </tr> </thead> Replace with: <thead> <tr> <th nowrap="nowrap" width="250">{$THEAD.name}</th> <th nowrap="nowrap">{$THEAD.no_prods}</th> <th nowrap="nowrap">{$THEAD.action}</th> </tr> </thead> Quote Link to comment Share on other sites More sharing options...
Dirty Butter Posted June 25, 2016 Author Share Posted June 25, 2016 PERFECT!! I figured I'd made a mess of it, as always. That helped me a LOT! What do you think: should I delete all the old stuff and just keep this part that was clearer to me, and perhaps others?? Quote Link to comment Share on other sites More sharing options...
bsmither Posted June 25, 2016 Share Posted June 25, 2016 I would say no, but its also my opinion that, as the OP, it is up to you. Quote Link to comment Share on other sites More sharing options...
Dirty Butter Posted June 25, 2016 Author Share Posted June 25, 2016 Well, I compromised - edited the first post and provided a jump to the new discussion. (I did take all that wrong code I listed yesterday out of it.) Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.