Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Sorting product attribute


marcel emmen

Recommended Posts

Hi there,

 

I have found a easy solution to sort product options. However this only works with numbers or text not both at the same time. I know also how this can be solved but don't know how to put this in the code; Maybe there are others who now how to solve this; Let me explain more

 

Ok first solution to sort options with numbers you only have to added:

. " order by 0+pov.products_options_values_name" );

at the end of the line which starts with $products_options = tep_db_query("select pov.products_options_values_id, in the product_info.php file

 

if you which to sort the options for text options then you need to added;

. " order by pov.products_options_values_name" ); without the 0+ (that's a zero)

 

most of the time options will be text or numbers so there must be a way to check if the first option value of a option is text or numeric and then depending on the result do a; . " order by 0+pov.products_options_values_name" ); for numeric or a . " order by pov.products_options_values_name" ); for text.

 

I already have a part of the solution but maybe somebody else can complete it.

 

It has to be something like this:

if is_numeric(pov.products_options_values_name) {

0+pov.products_options_values_name

} else {

pov.products_options_values_name

}

 

the function is_numeric returns TRUE if the value is numeric and FALSE if the value is text. The only thing i don't know is how to get the first option value of the actual record. Are there others who can help to make this option complete? I think many people like this and are waiting for this solution.

 

Thanks Marcel

Link to comment
Share on other sites

Are you basically trying to pad with the 0 on the name?

 

Do you have an example of your numeric attributes?

 

I am being a tad slow here in understanding what you are trying to do.

 

Also, the Attribute Sorter is kinda handy as it doesn't care what you have as you can manually define the order, use price or use alpha.

 

Your idea is handy, if it is to accomplish what I think you are trying to do.

 

But I need to see an example.

Link to comment
Share on other sites

Dear Linda,

 

I already have a full sollusion.

What I basicly do is that I first check if product-atribute is numeric or alfa.

If the attribute is numeric then the field is been convert to a numeric type

this is done with the 0+ (its a zero) and if the attribute is alfa then the field type stays original.

 

if you sort a attribute with numeric value and you do not convert the field type to numeric the sort is going wrong. 1,2,3,4, is going ok but if your attribute contains 1,2,3,4,5,10,11 then the 10,11 are show first in the field.

 

Ok below is the correct working change you have to made to the product_info.php field

 

in the product_info.php file change the line:

$products_options = 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 = '" . $HTTP_GET_VARS['products_id'] . "' and pa.options_id = '" . $products_options_name_values['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . $languages_id . "'" . " order by pov.products_options_values_name" );

 

with:

// changes made by Marcel Emmen 12-2002 to sort options in the catalog

//

if (is_numeric(products_options_values_name))

{

$products_options = 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 = '" . $HTTP_GET_VARS['products_id'] . "' and pa.options_id = '" . $products_options_name_values['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . $languages_id . "'" . " order by 0+pov.products_options_values_name" );

}

else {

$products_options = 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 = '" . $HTTP_GET_VARS['products_id'] . "' and pa.options_id = '" . $products_options_name_values['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . $languages_id . "'" . " order by pov.products_options_values_name" );

}

// end of changes

//----------------------------------------------

Link to comment
Share on other sites

  • 2 weeks later...

That's neat Marcel. It is certainly simple and easy to implement (as I have done) and a lot better than the default order which seems totally random to me.

My only problem now is that all my fields are of mixed type so I end up with shoe sizes like this:

 

10 (Euro 46)

5 (Euro 36)

6 (Euro 37)

 

so everything gets alpha sorted anyway. It is tempting to try and extract the numeric bit where there is one and sort on that sort on that but it is perhaps not general enough.

Link to comment
Share on other sites

  • 1 month later...
Dear Linda,

 

I already have a full sollusion.

What I basicly do is that I first check if product-atribute is numeric or alfa.

If the attribute is numeric then the field is been convert to a numeric type

this is done with the 0+ (its a zero) and if the attribute is alfa then the field type stays original.

 

if you sort a attribute with numeric value and you do not convert the field type to numeric the sort is going wrong. 1,2,3,4, is going ok but if your attribute contains 1,2,3,4,5,10,11 then the 10,11 are show first in the field.

 

Ok below is the correct working change you have to made to the product_info.php field

 

in the product_info.php file change the line:

$products_options = 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 = '" . $HTTP_GET_VARS['products_id'] . "' and pa.options_id = '" . $products_options_name_values['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . $languages_id . "'" . " order by pov.products_options_values_name" );

 

with:

       // changes made by Marcel Emmen 12-2002 to sort options in the catalog

 //    

  if (is_numeric(products_options_values_name))

  {

   $products_options = 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 = '" . $HTTP_GET_VARS['products_id'] . "' and pa.options_id = '" . $products_options_name_values['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . $languages_id . "'" . " order by 0+pov.products_options_values_name" );

          }  

    else {

     $products_options = 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 = '" . $HTTP_GET_VARS['products_id'] . "' and pa.options_id = '" . $products_options_name_values['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . $languages_id . "'" . " order by pov.products_options_values_name" );

           }

 // end of changes

 //----------------------------------------------

 

Marcel,

 

This worked GREAT in "pre" ms1 But no longer works for the ms1 release. Any idea on where to update this code so that it works with ms1?

 

Thank you very much!

Link to comment
Share on other sites

What is the problem using this with MS1?

 

The instructions state:

 

in the product_info.php file change the line:  

$products_options = 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 = '" . $HTTP_GET_VARS['products_id'] . "' and pa.options_id = '" . $products_options_name_values['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . $languages_id . "'" . " order by pov.products_options_values_name" );  

 

But there is no such line in the product_info.php file of ms1

Link to comment
Share on other sites

I put it here:

      while ($products_options_name_values = tep_db_fetch_array($products_options_name)) {

       $selected = 0;

       $products_options_array = array();

       echo '<tr><td class="main">' . $products_options_name_values['products_options_name'] . ':</td><td>' . "n";

    // changes made by Marcel Emmen 12-2002 to sort options in the catalog

//

if (is_numeric(products_options_values_name))

{

$products_options = 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 = '" . $HTTP_GET_VARS['products_id'] . "' and pa.options_id = '" . $products_options_name_values['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . $languages_id . "'" . " order by 0+pov.products_options_values_name" );

}

else {

$products_options = 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 = '" . $HTTP_GET_VARS['products_id'] . "' and pa.options_id = '" . $products_options_name_values['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . $languages_id . "'" . " order by pov.products_options_values_name" );

}

// end of changes

Link to comment
Share on other sites

I put it here:
      while ($products_options_name_values = tep_db_fetch_array($products_options_name)) {

$selected = 0;

$products_options_array = array();

echo '<tr><td class="main">' . $products_options_name_values['products_options_name'] . ':</td><td>' . "n";

 

Are you sure your using the product_info.php from ms1? I don't see any of the above code in my ms1 product_info.php at all. :?:

 

P.S. Thanks for your help!

Link to comment
Share on other sites

Ok,

 

I found the code.

 

I replaced 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 from " . TABLE_PRODUCTS_ATTRIBUTES . " pa, " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov where pa.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pa.options_id = '" . $products_options_name['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . $languages_id . "'");

 

With this:

 

// changes made by Marcel Emmen 12-2002 to sort options in the catalog 

// 

if (is_numeric(products_options_values_name)) 

{ 

$products_options = 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 = '" . $HTTP_GET_VARS['products_id'] . "' and pa.options_id = '" . $products_options_name_values['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . $languages_id . "'" . " order by 0+pov.products_options_values_name" ); 

} 

else { 

$products_options = 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 = '" . $HTTP_GET_VARS['products_id'] . "' and pa.options_id = '" . $products_options_name_values['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . $languages_id . "'" . " order by pov.products_options_values_name" ); 

} 

// end of changes

 

And when I accessed the product page I get the error below instead of the atributes options...

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/www/store/includes/functions/database.php on line 99

 

Any ideas?

Link to comment
Share on other sites

OK I admit I am using Ian's Loaded version but the line is almost the same in MS1. The line number to change is around 114

 

David,

 

It's line 114 in ms1 also and that is the exact line I am replacing with your code when I get the error message above. Any else have any ideas?

 

P.S. Thanks for your help David!

Link to comment
Share on other sites

Ok, I think I've found part of the problem.

 

In the original fix the beginning of the line to be rapled starts with:

 

$products_options = tep_db_query

 

But in ms1 this code was changed to:

 

$products_options_query = tep_db_query

 

The lines in the code that are to be added start with:

 

$products_options = tep_db_query

 

So I switched them to start with:

 

$products_options_query = tep_db_query

 

When I tried the above I did not get an error message anymore, but I could not see any of the atributes on the products page anymore.

 

David,

 

In your loaded version what does the line say that you are replacing?

Link to comment
Share on other sites

AH it still had the old name!

But let's not overcomplicate things here. All you are doing is adding a sort on the end of whatever query you have

. " order by 0+pov.products_options_values_name"

or a slightly different sort if is not numeric

Link to comment
Share on other sites

AH it still had the old name!

But let's not overcomplicate things here. All you are doing is adding a sort  on the end of whatever query you have  

. " order by 0+pov.products_options_values_name"

or a slightly different sort if is not numeric

 

I have both numeric attributes and alpha attributes.

 

But I can play around with the "if" statement and see what happens.

 

If I get it working I'll start a new post with the code for the ms1 version.

 

Thanks again for all your help David.

Link to comment
Share on other sites

  • 2 months later...

OSC MS1 Default product_info.php

 

$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 = '" . $products_options_name['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . $languages_id . "'");

while ($products_options = tep_db_fetch_array($products_options_query)) {

$products_options_array[] = array('id' => $products_options['products_options_values_id'], 'text' => $products_options['products_options_values_name']);

if ($products_options['options_values_price'] != '0') {

$products_options_array[sizeof($products_options_array)-1]['text'] .= ' (' . $products_options['price_prefix'] . $currencies->display_price($products_options['options_values_price'], tep_get_tax_rate($product_info['products_tax_class_id'])) .') ';

}

}

 

Replace with this code sort fix for product_info.php

// changes made by Marcel Emmen 12-2002 to sort options in the catalog. MS1 test by crshNbrn

//

if (is_numeric(products_options_values_name))

{

$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 = '" . $products_options_name['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . $languages_id . "'order by 0+pov.products_options_values_name");

while ($products_options = tep_db_fetch_array($products_options_query)) {

$products_options_array[] = array('id' => $products_options['products_options_values_id'], 'text' => $products_options['products_options_values_name']);

if ($products_options['options_values_price'] != '0') {

$products_options_array[sizeof($products_options_array)-1]['text'] .= ' (' . $products_options['price_prefix'] . $currencies->display_price($products_options['options_values_price'], tep_get_tax_rate($product_info['products_tax_class_id'])) .') ';

}

}

} else {

$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 = '" . $products_options_name['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . $languages_id . "'order by pov.products_options_values_name");

 

while ($products_options = tep_db_fetch_array($products_options_query)) {

$products_options_array[] = array('id' => $products_options['products_options_values_id'], 'text' => $products_options['products_options_values_name']);

if ($products_options['options_values_price'] != '0') {

$products_options_array[sizeof($products_options_array)-1]['text'] .= ' (' . $products_options['price_prefix'] . $currencies->display_price($products_options['options_values_price'], tep_get_tax_rate($product_info['products_tax_class_id'])) .') ';

}

}

}

// end of changes

//----------------------------------------------

crshNbrn living on the edge.....

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