Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

[Contribution] Product Sort Order


Mark Russell

Recommended Posts

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

Link to comment
Share on other sites

  • Replies 106
  • Created
  • Last Reply

Top Posters In This Topic

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

Link to comment
Share on other sites

Screen Shots

Configuration/Product Listing - here is the product sort order added to the configuration/product_listing page

prod_listing.jpg

 

New Product Entry - you can see the new sort order entry field for the product.

new_product.jpg

 

Category/Product page - sort order visible next to the status field

catprod_sort.jpg

 

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

catalog.jpg

 

Mark

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 3 months later...

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.

Link to comment
Share on other sites

  • 3 weeks later...

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]

 

error1.jpg

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 5 weeks later...

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.

Link to comment
Share on other sites

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..

Link to comment
Share on other sites

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);

Link to comment
Share on other sites

  • 3 months later...

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 ???

Link to comment
Share on other sites

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......

Link to comment
Share on other sites

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... :)

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...