Putting attributes in logical order

[♥akmac]

I'm using the contribution "Alternative Product Attribute Handling Method" (which is a great contribution) and am wondering what the best way to order the attributes is. I've seen mention adding a database column-but would really rather avoid that if possible.

 

What I would like is for the drop down list to read in sequential order like:

 

8

7.5

7

6.5

6

5.5

5

4.5

4

 

But it is showing the numbers jumbled-even between products with the same attributes!

 

Any fix for this?

[bglkk]

Products Attributes Sort v1.0

 

http://www.oscommerce.com/community/contributions,1690/

[OceanRanch]

Around line 147 in catalog/product_info.php is the query that retruns the attribute values for the drop downs. I changed mine to sort alphabetically. The original code has no "order by" clause so you get a random return from mysql.

 

$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 pov.products_options_values_name");

 

You also could install a contribution that provides for more than just sorting...

 

http://www.oscommerce.com/community/contributions,1119

 

HTH

Tom

 

I think Doug's link above is a better contrib.....

[♥akmac]

Products Attributes Sort v1.0

 

http://www.oscommerce.com/community/contributions,1690/

<{POST_SNAPBACK}>

 

Doug,

 

I installed this-and am getting no errors-but it's not functioning as designed. The extra field shows up in product_attributes.php, but when I edit it, enter the value and select Update-it reverts back to zero.

 

I've checked and rechecked the install and everything looks the same...

 

 

PS-thanks to both of you for the replies!

[bglkk]

Hi Cleve:

 

Strange, it is a popular and time-tested contribution. Perhaps you can find something useful in the support thread:

 

http://www.oscommerce.com/forums/index.php?showtopic=68003&hl=

 

EDIT: Oops, I see you've already posted there.

 

Otherwise, there's still Tom's tip... :)

[♥akmac]

Hi Cleve:

 

Strange, it is a popular and time-tested contribution. Perhaps you can find something useful in the support thread:

 

http://www.oscommerce.com/forums/index.php?showtopic=68003&hl=

 

EDIT: Oops, I see you've already posted there.

 

Otherwise, there's still Tom's tip... :)

<{POST_SNAPBACK}>

 

Yeah-probably operator error. Anyway, I deleted all attributes and options and started fresh, because I read (somwewhere) that the changes wouldn't apply to old entries. Now when I attempt to add an attribute to a product I get a mysql error:

----------------------------------------------------

1136 - Column count doesn't match value count at row 1

insert into products_attributes values ('', '30', '1', '1', '', '+')

[TEP STOP]

----------------------------------------------------

I'm looking in phpmyadmin-but it may as well be greek. Wait, no.... Latin. In any case, I'm over my head. Any mysql lifeguards about?

[OceanRanch]

Did you back out ALL of the database changes you made when you installed the attributes contribution? Check those out carefully and then remove the column that they had you you add and then you should be back to square one.

 

HTH

Tom

[♥akmac]

Did you back out ALL of the database changes you made when you installed the attributes contribution? Check those out carefully and then remove the column that they had you you add and then you should be back to square one.

 

HTH

Tom

<{POST_SNAPBACK}>

 

I haven't uninstalled anything yet-was still trying to get it to work. Should I back it all out?

[OceanRanch]

Then maybe you never did the database table updates that would include the new column which also would cause the "insert" to fail becuase the new column was never put in to the contribution table that you installed.

 

The insert that fails looks like it is inserting 6 fields into the products_attributes table. Check your phpmyadmin on this table and see how many columns you have.

 

HTH

Tom

[♥akmac]

Then maybe you never did the database table updates that would include the new column which also would cause the "insert" to fail becuase the new column was never put in to the contribution table that you installed.

 

The insert that fails looks like it is inserting 6 fields into the products_attributes table. Check your phpmyadmin on this table and see how many columns you have.

 

HTH

Tom

<{POST_SNAPBACK}>

 

 

hmmm... Doesn't look like anything is in it... here's the line from phpmyadmin:

 

products_attributes??Browse Select Insert Properties Drop Empty 0 MyISAM? 2.6 KB

 

Browse, select, and empty are not linked-presumably because they are empty?

[Guest]

JMJ

 

How can I get the number 0 or a default selection to be first in this contribution?

 

I need the first to be either zero of select

 

 

Rachel

[♥akmac]

When I click insert I get:

 

products_attributes_id

int(11)

 

products_id

int(11)

 

options_id

int(11)

 

options_values_id

int(11)

 

options_values_price

decimal(15,4)

 

price_prefix

char(1)

 

attribute_sort

int(10) unsigned

 

I think attribute_sort is the added bit isn't it?

 

"value" is empty for all of them....

[OceanRanch]

Cleve,

 

Ok looks like you have 7 fields (correct for the contribution) however you probably did not update the .php files because they are still trying to insert only 6 fields. Go back to the contribution and double check the edits you made to the source.

 

Tom

[♥akmac]

Cleve,

 

Ok looks like you have 7 fields (correct for the contribution) however you probably did not update the .php files because they are still trying to insert only 6 fields. Go back to the contribution and double check the edits you made to the source.

 

Tom

<{POST_SNAPBACK}>

 

That all checks out. Cut and paste errors usually show php errors that are traceable. Is it possible that this is conflicting with the Alternative Product Attribute Handling Method by David Garcia? They both modify the categories.php...

 

If we find a fix for this I'll post it in the contributions as these features together are a powerful benefit for those with lots of products with similar attributes.

[OceanRanch]

Sure thing, mixing contributions can occasionally cause these conflicts. One way is to change the insert statements in the offending module that only has 6 fields.

 

insert tablename (fieldname1,fieldname2,fieldname3) values (value1,value2,value3)

 

That might be a pain to accomplish but is pretty straightforward if one contribution doesn't agree with the other.

 

HTH

Tom

[♥akmac]

I think this block of code in admin/categories.php is the culprit:

------------------------------------------

> // Update Product Attributes

> $rows = 0;

> $options_query = tep_db_query("select products_options_id, products_options_name from " . TABLE_PRODUCTS_OPTIONS . " where language_id = '" . $languages_id . "' order by products_options_name");

> while ($options = tep_db_fetch_array($options_query)) {

> $values_query = tep_db_query("select pov.products_options_values_id, pov.products_options_values_name from " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov, " . TABLE_PRODUCTS_OPTIONS_VALUES_TO_PRODUCTS_OPTIONS . " p2p where pov.products_options_values_id = p2p.products_options_values_id and p2p.products_options_id = '" . $options['products_options_id'] . "' and pov.language_id = '" . $languages_id . "'");

> while ($values = tep_db_fetch_array($values_query)) {

> $rows ++;

> $attributes_query = tep_db_query("select products_attributes_id, options_values_price, price_prefix from " . TABLE_PRODUCTS_ATTRIBUTES . " where products_id = '" . $products_id . "' and options_id = '" . $options['products_options_id'] . "' and options_values_id = '" . $values['products_options_values_id'] . "'");

> if (tep_db_num_rows($attributes_query) > 0) {

> $attributes = tep_db_fetch_array($attributes_query);

> if ($HTTP_POST_VARS['option'][$rows]) {

> if ( ($HTTP_POST_VARS['prefix'][$rows] <> $attributes['price_prefix']) || ($HTTP_POST_VARS['price'][$rows] <> $attributes['options_values_price']) ) {

> tep_db_query("update " . TABLE_PRODUCTS_ATTRIBUTES . " set options_values_price = '" . $HTTP_POST_VARS['price'][$rows] . "', price_prefix = '" . $HTTP_POST_VARS['prefix'][$rows] . "' where products_attributes_id = '" . $attributes['products_attributes_id'] . "'");

> }

> } else {

> tep_db_query("delete from " . TABLE_PRODUCTS_ATTRIBUTES . " where products_attributes_id = '" . $attributes['products_attributes_id'] . "'");

> }

> } elseif ($HTTP_POST_VARS['option'][$rows]) {

> tep_db_query("insert into " . TABLE_PRODUCTS_ATTRIBUTES . " values ('', '" . $products_id . "', '" . $options['products_options_id'] . "', '" . $values['products_options_values_id'] . "', '" . $HTTP_POST_VARS['price'][$rows] . "', '" . $HTTP_POST_VARS['prefix'][$rows] . "')");

> }

> }

> }

>

----------------------------------------------

 

But I don't have the savvy to solve it. Getting this store up is my homeschooling in php and mysql-so I'll keep fighting it. Very much appreciate the help thus far!

 

Should the:

 

order by products_options_name");

 

Be:

 

order by pa.attribute_sort");

 

?

 

I changed this but don't know if I should've-still getting the same error:

 

1136 - Column count doesn't match value count at row 1

insert into products_attributes values ('', '30', '1', '1', '', '+')

[TEP STOP]

 

Tom, Your aforementioned solution is over my head a bit... noob. It makes sense to me that if one section of code is requesting a 6 column table named products_attributes-but product_attributes has 7 columns-that it gives an error. I just don't know where/how to correct it.

[OceanRanch]

Try changing

tep_db_query("insert into " . TABLE_PRODUCTS_ATTRIBUTES . " values ('', '" . $products_id . "', '" . $options['products_options_id'] . "', '" . $values['products_options_values_id'] . "', '" . $HTTP_POST_VARS['price'][$rows] . "', '" . $HTTP_POST_VARS['prefix'][$rows] . "')");

 

to

 

tep_db_query("insert into " . TABLE_PRODUCTS_ATTRIBUTES . " (products_attributes_id, products_id, options_id, options_values_id, options_values_price, price_prefix)  values ('', '" . $products_id . "', '" . $options['products_options_id'] . "', '" . $values['products_options_values_id'] . "', '" . $HTTP_POST_VARS['price'][$rows] . "', '" . $HTTP_POST_VARS['prefix'][$rows] . "')");

 

Tom

[♥akmac]

Tom,

 

1136 - Column count doesn't match value count at row 1

insert into products_attributes values ('', '30', '1', '1', '', '+')

[TEP STOP]

 

Don't know what to do at this point-maybe just let my listing be random for the time being. Thanks for your time on this though, and if you think of anything else please let me know.

 

-Cleve