Sorting Attributes

[simolingerie]

:thumbsup:

I put this in place for myself because it was driving me crazy trying to get the product options displayed in the order I wanted. All the sorting contributions I came across were difficult to implement or changed a lot of code.

 

All I wanted was something that showed my options in order of price. Or at the very least showed them in the order I entered them.

 

It's a simple fix that takes less than a minute.

 

What it does...

First - sorts by price

Second - if price is same (or 0), it sorts based on the order that the option was entered.

 

File to change ... /catalog/product_info.php

 

Replace this line

$products_options_query = tep_db_query("select pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix from " . TABLE_PRODUCTS_ATTRIBUTES . " pa, " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov where pa.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pa.options_id = '" . (int)$products_options_name['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . (int)$languages_id . "'");

 

With this line

$products_options_query = tep_db_query("select pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix, pa.products_attributes_id from " . TABLE_PRODUCTS_ATTRIBUTES . " pa, " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov where pa.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pa.options_id = '" . (int)$products_options_name['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . (int)$languages_id . "'" . " order by pa.options_values_price, pa.products_attributes_id");

 

Hope someone else will find this useful.

 

Thank you!!

[nath69uk]

Thanks so much.

[rharkey]

:thumbsup: Awesome. It took all of 20 seconds. The ordering was something that was also driving me crazy. Thanks!!

 

I put this in place for myself because it was driving me crazy trying to get the product options displayed in the order I wanted. All the sorting contributions I came across were difficult to implement or changed a lot of code.

 

All I wanted was something that showed my options in order of price. Or at the very least showed them in the order I entered them.

 

It's a simple fix that takes less than a minute.

 

What it does...

First - sorts by price

Second - if price is same (or 0), it sorts based on the order that the option was entered.

 

File to change ... /catalog/product_info.php

 

Replace this line

$products_options_query = tep_db_query("select pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix from " . TABLE_PRODUCTS_ATTRIBUTES . " pa, " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov where pa.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pa.options_id = '" . (int)$products_options_name['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . (int)$languages_id . "'");

 

With this line

$products_options_query = tep_db_query("select pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix, pa.products_attributes_id from " . TABLE_PRODUCTS_ATTRIBUTES . " pa, " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov where pa.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pa.options_id = '" . (int)$products_options_name['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . (int)$languages_id . "'" . " order by pa.options_values_price, pa.products_attributes_id");

 

Hope someone else will find this useful.

[Flo Bee]

:thumbsup: Awesome. It took all of 20 seconds. The ordering was something that was also driving me crazy. Thanks!!

 

Great mod! Very simple to set up and that's the best part!

 

Attributes suck bad enough, but this really helps.

[scribionics]

Thanks, this suggestion (Product Attributes Sort Order v1.0) made product_info.php work very much better (looking like order now rather than chaos, always a plus), and clarified some things about variable names:

 

pa. is a Product Attribute, such as "flavor".

pov. is a Product Option Value. such as "chocolate".

 

I think being able to see that now will help me solve some other problems.

[Guest]

This fix rocks...

For anybody wondering, I saw another version of this fix that will actually sort your attributes out in alphabetical order. I'm sorry that I cannot find that code right now but is is very similar to the 'sort by admin order' (which I currently use in conjunction with my AJAX Attribute Manager, for the control panel)

 

Anyhow, thanks for the fix mitchind :thumbsup:

[denacarol]

:thumbsup: Way more than helpful dude! Saved me a TON of time. Thank goodness for Google and your sorting contribution!

[Eprime]

I absolutely love this contribution, however, I was wondering if anyone had any knowledge on how to reverse the sort from highest priced (top) to lowest price (bottom) instead of lowest priced (top) to highest price (bottom)?

 

I know this lies in the mentioned code somewhere

:

$products_options_query = tep_db_query("select pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix, pa.products_attributes_id from " . TABLE_PRODUCTS_ATTRIBUTES . " pa, " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov where pa.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pa.options_id = '" . (int)$products_options_name['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . (int)$languages_id . "'" . " order by pa.options_values_price, pa.products_attributes_id");

[JennieT]

I just implemented this fantastic tip, after dispairing of ever being able to sort my 2 options the way I wanted (defaulting to Yes instead of No).

 

I changed the order to highest price first by adding the word "desc" (for descending) after "order by pa.options_values_price", so the whole thing reads:

 

$products_options_query = tep_db_query("select pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix, pa.products_attributes_id from " . TABLE_PRODUCTS_ATTRIBUTES . " pa, " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov where pa.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pa.options_id = '" . (int)$products_options_name['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . (int)$languages_id . "'" . " order by pa.options_values_price desc, pa.products_attributes_id");

[nick robinson]

It's a simple fix that takes less than a minute.

 

I did this but get an error

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /var/www/vhosts/easyontheeye.net/httpdocs/catalog/includes/functions/database.php on line 99

 

Any ideas?

[rudolfl]

is there any way to code this so it sorts products alphabetically, even if they are already entered?

 

Use this code:

$products_options_query = tep_db_query("select pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix, pa.products_attributes_id from " . TABLE_PRODUCTS_ATTRIBUTES . " pa, " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov where pa.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pa.options_id = '" . (int)$products_options_name['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . (int)$languages_id . "'" . " order by pa.options_values_price, pov.products_options_values_name");

 

This will sort by price first, then by name.

 

Rudolf

[sighthound]

Awesome awesome! Thanks so much!!!

[almico]

I put this in place for myself because it was driving me crazy trying to get the product options displayed in the order I wanted. All the sorting contributions I came across were difficult to implement or changed a lot of code.

 

All I wanted was something that showed my options in order of price. Or at the very least showed them in the order I entered them.

 

It's a simple fix that takes less than a minute.

 

What it does...

First - sorts by price

Second - if price is same (or 0), it sorts based on the order that the option was entered.

 

File to change ... /catalog/product_info.php

 

Replace this line

$products_options_query = tep_db_query("select pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix from " . TABLE_PRODUCTS_ATTRIBUTES . " pa, " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov where pa.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pa.options_id = '" . (int)$products_options_name['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . (int)$languages_id . "'");

 

With this line

$products_options_query = tep_db_query("select pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix, pa.products_attributes_id from " . TABLE_PRODUCTS_ATTRIBUTES . " pa, " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov where pa.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pa.options_id = '" . (int)$products_options_name['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . (int)$languages_id . "'" . " order by pa.options_values_price, pa.products_attributes_id");

 

Hope someone else will find this useful.

This looks great, but I can't seem to find my Catalogs folder...

[pjhvan@gmail.com]

[sorting Attributes Code.]

Thanks for that piece of code... life saver ..

How would I make it list in revers price order .. i.e. higher to lower?

Peter

[le6ha2]

[sorting Attributes Code.]

Thanks for that piece of code... life saver ..

How would I make it list in revers price order .. i.e. higher to lower?

Peter

You can use this

$products_options_query = tep_db_query("select pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix, pa.products_attributes_id from " . TABLE_PRODUCTS_ATTRIBUTES . " pa, " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov where pa.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pa.options_id = '" . (int)$products_options_name['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . (int)$languages_id . "'" . " order by pa.options_values_price DESC, pa.products_attributes_id ASC"); 

[ismaiel_c]

This a fantastic contributuion but i need the months to come before the years.

[jbalter]

You are my hero.... You saved me so much time

[ArtWithHeart]

Thank you. SO much. For newbies like me it's small things like this that really help out. Wonderful - in case anyone else is wondering, it works perfectly! Clearly 6 years does not make a difference. Cheers!

[geco100]

Brilliant Just what i was looking for thanks :D

 

Helps to prevent my customers from automatically choosing the most expensive upgrades because they didnt see the options lol

 

anyway cheers :thumbsup:

Thank you for posting this

[Guest]

Use this code:

$products_options_query = tep_db_query("select pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix, pa.products_attributes_id from " . TABLE_PRODUCTS_ATTRIBUTES . " pa, " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov where pa.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pa.options_id = '" . (int)$products_options_name['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . (int)$languages_id . "'" . " order by pa.options_values_price, pov.products_options_values_name");

 

This will sort by price first, then by name.

 

Rudolf

 

I hope someone can help me I've been trying to get the attributes to be sorted alphabetically but nothing works, tried the above and it did nothing. I've tried a sort order tool but with thousands of attributes it's ridiculous giving them all a number and then having to re-arrange the numbers when new attributes are added(so they are alphabetical). Are there any other ways to either sort attributes alphabetically or an easier way to organize / sort through the attributes?

[b2995]

It's the simple things...an obvious step that should have been included in the standard version or added to the configuration. Even in 2010...Thank you! I sorted by Options Value ID in Descending order

 

$products_options_query = tep_db_query("select pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix from " . TABLE_PRODUCTS_ATTRIBUTES . " pa, " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov where pa.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pa.options_id = '" . (int)$products_options_name['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . (int)$languages_id . "'" . "order by pa.options_values_id DESC");

[Guest]

Im hoping this code works for me like it has for many others. Where do we enter this code?

[paul1011]

Thanks for this nice simple code change that works. Works for my 2.3 store.

[Guest]

this sorts the attribute by price all right but for me it fails to take into account whether theres a + or - sign in front of the price so the smallest prices are listed last :(

 

If you replace the options_values_price with convert(concat(price_prefix,options_values_price),signed) it will work....

[bowco]

I hope someone can help me I've been trying to get the attributes to be sorted alphabetically but nothing works, tried the above and it did nothing. I've tried a sort order tool but with thousands of attributes it's ridiculous giving them all a number and then having to re-arrange the numbers when new attributes are added(so they are alphabetical). Are there any other ways to either sort attributes alphabetically or an easier way to organize / sort through the attributes?

 

 

Seriously, I know very little about this stuff. I was wrestling with this same problem for days and it was driving me nuts!!! Then I found this post. The only problem was that I didn't want to sort by price but rather alphabetically. Anyway, I used OPs code and changed the following:

 

$products_options_query = tep_db_query("select pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix, pa.products_attributes_id from " . TABLE_PRODUCTS_ATTRIBUTES . " pa, " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov where pa.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pa.options_id = '" . (int)$products_options_name['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . (int)$languages_id . "'" . " order by pa.options_values_price, pov.products_options_values_name");

 

 

replace pa.options_values_price with pa.options_values_name

 

 

The sort seems to be working fine now.