Mark Russell Posted January 24, 2003 Share Posted January 24, 2003 Product Sort Contribution I needed the capability of sorting by product similar to the way in which categories are sorted. I read through many posts of how people use different techniques to accomplish product sort (i.e. putting a number such as 1., 2., 3. etc in front of the Product Name); however, none quite met what I was looking to accomplish. So, I put together this contribution with the following features: - product sort entry on the product entry page in admin - product and category sort order displayed on each of the category pages in admin (next to the red and green status indicators) - ability to set the column sort order on the admin/configuration/product_listing page - ability to set the column to being visible or not on the admin/configuration/product_listing page - ability to use product sort order as the default catalog display whether or not the column is actually made visible - maintain the native column sort capability in the catalog (click on column heading to get ascending/descending sort), though I did remove the "+" and "-" indicators <<<<< I built this contribution on the fully loaded snapshot from Phesis dated September 14th, 2002. >>>>>>>>> Find it here: http://www.oscommerce.com/community/contributions,911 Mark Quote Link to comment Share on other sites More sharing options...
gdfwilliams Posted January 24, 2003 Share Posted January 24, 2003 Mark - This looks great. Is there a URL where we can see it in action? Thanks, GDW Quote Link to comment Share on other sites More sharing options...
Trailz Posted January 24, 2003 Share Posted January 24, 2003 Did you try this with the other attribute addons? or is this without the attribute copier? Quote Link to comment Share on other sites More sharing options...
Mark Russell Posted January 25, 2003 Author Share Posted January 25, 2003 This looks great. Is there a URL where we can see it in action? GDW, the best I can do is to post some screen shots. My dev environment is local. I'll try to do that later tonight. M Did you try this with the other attribute addons? or is this without the attribute copier? Trailz, no I didn't and it is w/out the attribute copier. Don't know what effect this may have on those. Why don't you try it out and let us know. :wink: M Quote Link to comment Share on other sites More sharing options...
Mark Russell Posted January 25, 2003 Author Share Posted January 25, 2003 Screen Shots Configuration/Product Listing - here is the product sort order added to the configuration/product_listing page New Product Entry - you can see the new sort order entry field for the product. Category/Product page - sort order visible next to the status field Catalog - this is a shot of my site with the sort order on this page driven by the settings shown in the previous shot of the Category/Product page Mark Quote Link to comment Share on other sites More sharing options...
gdfwilliams Posted January 25, 2003 Share Posted January 25, 2003 Looks great! Can't wait to install. Thanks, GDW Quote Link to comment Share on other sites More sharing options...
Mark Russell Posted January 27, 2003 Author Share Posted January 27, 2003 I found a minor bug over the weekend. The product sort did not work past page 1 on categories with products spanning multiple pages. The fix for this is listed in the txt file as mod #7 to file: catalog/default.php. I also included screen shots in this zip. Find it here: http://www.oscommerce.com/community/contributions,911 Mark Quote Link to comment Share on other sites More sharing options...
strictlypc Posted January 28, 2003 Share Posted January 28, 2003 Hi Everyone, First, I would like to thank Mark for this great contribution. After installing this contribution, I encountered the following error: 1064 - You have an error in your SQL syntax near 'order by p.products_sort_order' at line 1 select count(*) as total order by p.products_sort_order Apparently, there was an error in the instructions. In catalogdefault.php, Step #4, it should read: ORIGINAL CODE: $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . $HTTP_GET_VARS['filter_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . $languages_id . "' and p2c.categories_id = '" . $current_category_id . "'"; } else { // We show them all $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . $languages_id . "' and p2c.categories_id = '" . $current_category_id . "'"; Replace with this NEW CODE: $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, p.products_sort_order, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . $HTTP_GET_VARS['filter_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . $languages_id . "' and p2c.categories_id = '" . $current_category_id . "'"; } else { // We show them all $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, p.products_sort_order, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . $languages_id . "' and p2c.categories_id = '" . $current_category_id . "'"; Instead of //P.PRODUCT_SORT_ORDER ADDED TO SELECT STATEMENT FOR PRODUCT SORT FUNCTION $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, p.products_sort_order, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . $languages_id . "' and p2c.categories_id = '" . $current_category_id . "'"; } // We build the manufacturers Dropdown $filterlist_sql= "select distinct m.manufacturers_id as id, m.manufacturers_name as name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_MANUFACTURERS . " m where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and p.products_id = p2c.products_id and p2c.categories_id = '" . $current_category_id . "' order by m.manufacturers_name"; I am using Ian's Loaded Snapshot 4 with other heavily modified files. I hope this helps. Thanks Mark for a great contribution, David Quote Link to comment Share on other sites More sharing options...
Mark Russell Posted January 28, 2003 Author Share Posted January 28, 2003 Thanks for catching that David. I pasted the wrong section :( I'll post a corrected version today to the contrib archive. M Quote Link to comment Share on other sites More sharing options...
Snowman Posted January 29, 2003 Share Posted January 29, 2003 Ive just applied the mod to yesterdays CVS however, while most of it appears correct i cant get it to display the sort order in admin categories. It also pushes the table out of shape. (note i have the categories status mod fitted) Any chance of posting a copy of your admin/categories.php file so i can compare. Thanks Quote Link to comment Share on other sites More sharing options...
Mark Russell Posted January 29, 2003 Author Share Posted January 29, 2003 Steve, The file is quite long to post here. PM me your email addy and I'll send it as an attachment. Mark Quote Link to comment Share on other sites More sharing options...
Snowman Posted January 29, 2003 Share Posted January 29, 2003 Ah mark its ok i managed to get it sussed. My file is laid out differently from the one you referred to in the readme, i had to make a few adjustments to suit. Quote Link to comment Share on other sites More sharing options...
Paulino Posted May 1, 2003 Share Posted May 1, 2003 I've been strugling to get the mod to work but seem to be getting closer with the help of some diff software. Could anyone help me change the design so that product_sort is part of the product_to_categories table? I think this is really where the sort should be stored as it means that product "aliases" will be sorted correctly as well. Quote Link to comment Share on other sites More sharing options...
jonsteward Posted May 20, 2003 Share Posted May 20, 2003 I have tried to install this contribution but I am getting the following error whilst trying to view my products can someone tell me why? 1054 - Unknown column 'p.products_sort_order' in 'field list' select p.products_sort_order, p.products_image, pd.products_name, m.manufacturers_name, p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, p.products_sort_order, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '68' order by p.products_sort_order limit 0, 20 [TEP STOP] Quote Link to comment Share on other sites More sharing options...
alfaowner Posted May 20, 2003 Share Posted May 20, 2003 Hey, thats the error I get, however, with no time to refine, I simply left it for now, when I find a fix, Ill be sure to post it, in the mean time, perhaps someone else can help. At least you know ur not alone. Quote Link to comment Share on other sites More sharing options...
updmike Posted June 18, 2003 Share Posted June 18, 2003 In my admin/categories.php page I am getting this error 1054 - Unknown column 'p.products_mimage' in 'field list' select p.products_id, pd.products_name, p.products_quantity, p.products_image, p.products_mimage, p.products_bimage, p.products_price, p.products_date_added, p.products_last_modified, p.products_date_available, p.products_status, p.products_sort_order from products p, products_description pd, products_to_categories p2c where p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = '0' order by p.products_sort_order, pd.products_name [TEP STOP] I am not sure where to even start looking? I have gone through the instructions and I can find where I have made an error. Any advice on where to start looking? thanks.. The contribution itself is very kewl and exactly what I needed. Quote Link to comment Share on other sites More sharing options...
updmike Posted June 18, 2003 Share Posted June 18, 2003 I went ahead and added the rows manually into my database. I added the products_mimage and productss_bimage and it seemed to work.. I am just now able to go into the catalouge and put in the sort orders.. I will let ya know if it worked.. I dont know what mimage and bimage are used for but maybe I am just using an old or newer snapshot.. Not sure.. Peace.. Quote Link to comment Share on other sites More sharing options...
leolll Posted June 20, 2003 Share Posted June 20, 2003 I have tried to install this contribution but I am getting the following error whilst trying to view my products can someone tell me why? 1054 - Unknown column 'p.products_sort_order' in 'field list' You should double check to see you did this step properly: Insert a new field into TABLE_PRODUCTS, using this SQL command: ALTER TABLE PRODUCTS ADD `products_sort_order` INT(3); Quote Link to comment Share on other sites More sharing options...
weird2u2 Posted October 10, 2003 Share Posted October 10, 2003 Ia m getting this error as well: 1054 - Unknown column 'p.product_sort_order' in 'field list' To begin with, if I use the suggested sql.txt included with 'Product Sort files', I find that the command: " ALTER TABLE PRODUCTS ADD `products_sort_order` INT(3); " Is not accepted by phpadmin, but if I change : ALTER TABLE products ADD `products_sort_order` INT(3); ie: 'PRODUCTS' change to small caps 'products', it is then accepted into the database, but still gives the 1054 error. Any help or solutions plz ??? Quote Link to comment Share on other sites More sharing options...
weird2u2 Posted October 10, 2003 Share Posted October 10, 2003 I have come across a repeating instuction, included in the install.txt, for this contribution....as follows 6. Approximately at lines 579-585, >>> FIND THIS: <tr> <td colspan="2"><?php echo tep_draw_separator('pixel_trans.gif', '1', '10'); ?></td> </tr> <tr> <td class="main"><?php echo TEXT_PRODUCTS_WEIGHT; ?></td> <td class="main"><?php echo tep_draw_separator('pixel_trans.gif', '24', '15') . ' ' . tep_draw_input_field('products_weight', $pInfo->products_weight); ?></td> </tr> >>> ADD THE FOLLOWING 7 LINES *AFTER* THE ABOVE: <tr> <td colspan="2"><?php echo tep_draw_separator('pixel_trans.gif', '1', '10'); ?></td> </tr> <tr> <td class="main"><?php echo TEXT_EDIT_SORT_ORDER; ?></td> <td class="main"><?php echo tep_draw_separator('pixel_trans.gif', '24', '15') . ' ' . tep_draw_input_field('products_sort_order', $pInfo->products_sort_order, 'size="2"'); ?></td> </tr> This is repeated twice.......which means you end up with two sort tick boxes in admin product edit area..... Watch out for this...... Quote Link to comment Share on other sites More sharing options...
kolten Posted October 16, 2003 Share Posted October 16, 2003 so nobody found a fix for this? i find it quite annoying, i installed it exactly like the read me says and i still cant edit the product, i get this "1054 - Unknown column 'p.product_sort_order'" error... :( Quote Link to comment Share on other sites More sharing options...
weird2u2 Posted October 16, 2003 Share Posted October 16, 2003 To get around the 1054 error...... Watch out for this sql error: " ALTER TABLE PRODUCTS ADD `products_sort_order` INT(3); " Is not accepted by phpadmin, but if I change : ALTER TABLE products ADD `products_sort_order` INT(3); ie: 'PRODUCTS' change to small caps 'products', it is then accepted into the database. * You also then need to give a product order number to any products you have already inserted into the database..after running the sql command above. * ie you need to use phpAdmin to gain access to your sql-database, and give product order numbers to your products that you have already got....these are what are giving you the error, as they have all got the same sort-order number...once this is fixed, you will then have access again to your products via Osc Admin. Where you will be able to add new products with new sort orders... Hope this helps, it works for me... :) Quote Link to comment Share on other sites More sharing options...
kolten Posted October 16, 2003 Share Posted October 16, 2003 ah, thanks, i already have all needed tables but i didnt know about the order numbers i have to set.... i'll give it a try now, thanks for the fast answer!!! peace, kolt Quote Link to comment Share on other sites More sharing options...
kolten Posted October 16, 2003 Share Posted October 16, 2003 sorry, one more question: do i need an unique number for every product? or just for every produkt in 1 category? Quote Link to comment Share on other sites More sharing options...
manthped Posted October 17, 2003 Share Posted October 17, 2003 I had the same problem with "1054 error; p.product_sort_order...". There is a misstake in the product_sort.txt. (i think <_< ). For your /admin/categories.php file at step no. 5 it says ...replace .... with: ......_id, p.manufacturers_id, p.product_sort_order from " . TABLE........ I changed this to: ......_id, p.manufacturers_id, p.products_sort_order from " . TABLE........ --------------------------------------------!!!-------------------------------------- and now it does it?s job without that "1054 error". :D Quote Link to comment Share on other sites More sharing options...
New 
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.
Note: Your post will require moderator approval before it will be visible.