I need to multiply my attribute sort orders by 10 to make room for more in between

Ive done a bit of googling but couldnt find anyone thats done this before.
Ive run out of room "between" attributes, I want to multiply the entire attribute order column by 10 in phpmyadmin to give myself some space to add new ones.

This is the name of the column

products_options_sort_order

Its in the products_attributes table

What query would work to do this please?

How many option names do you have in the list?

Have you used the Admin->Catalog->Option Name Sorter screen?
(Or option values sorter, if that's what you're wanting to sort)

Hmm Ive never actually used those tools before.
I have 120 or so options under colour and now I have many that are on the same number, I get errors sometimes when I add a new one using the same number as one thats already there. Error refers to duplicates.

I need to learn to leave 20 number gaps, I was using gaps of 5 when I started these colours.

I thought a query that did the column * 10 would solve my problem.

Are these option NAMES? or option VALUES?

These are values. Did I get the column name wrong? I didnt look too close, it may have been the Name order column.
But these are for colours like red, blue etc, so values.

I'm gonna defer to Ajeh, because she's got some additional insight you should consider beyond just updating the sort order numbers with a SQL query.

Plus, she's smarter than me :blush:

There are 3 tables of interest here ...

products_options
products_options_values
products_attributes

The table:
products_options

Holds the Option Names such as:
Size
Color

The table:
products_options_values

Holds the Option Value Names such as:
Red
Yellow
Green
Small
Medium
Large

The table:
products_attributes

Holds the combination of the Option Name and Option Values and the Product products_id that go together ...

On the Option Names table:
products_options

You can set the Sort Order for the Option Names for when you have a Product with multiple Option Names like Color and Size to indicate which comes in what order ...

On the Option Values table:
products_options_values

You can set the DEFAULT Sort Order for the Option Values for when you have a Product with an Option Names like Color with multiple values and to indicate which Option Name comes in what order, such as:
Red 10
Green 20
Yellow 30

then, when you add these Option Values to a Product in the Attributes Controller, these DEFAULT Sort Orders are used to Pre-Set these values or to reset the values later ...

I think what you are really wanting to do is redo the table:
products_options_values

and fix the DEFAULT Sort Orders on the Option Values and then update all the Attributes on your site with these new values, which can be done in the Tools ... Store Manager ...

Why don't you peek at the table:
products_options_values

and see if those DEFAULT Sort Orders were redone, then applied to your table:
products_attributes

if that wouldn't resolve your issue ...

NOTE: products_options_values_sort_order * 10 would mean that:
Red 10 becomes 100
Yellow 20 becomes 200
Green 30 becomes 300

Is that what you are wanting?

Im pretty sure this is what I want

NOTE: products_options_values_sort_order * 10 would mean that:
Red 10 becomes 100
Yellow 20 becomes 200
Green 30 becomes 300

Is that what you are wanting?

I need more room between the option values to add more colours.

Backup your database ...

Next, in phpMyAdmin you can run the SQL:

UPDATE products_options_values SET products_options_values_sort_order = products_options_values_sort_order * 10;

Then, go to your Zen Cart Admin to the Tools ... Store Manager ... and you can run the:

Update All Products' Attribute Sort Orders
to match Option Value Default Sort Orders: UPDATE

Great thanks. Will do this later today.
If only some of the paid software I use had support like this....

NOTE: this may take time to run the last step in the Store Manager ...

If it does not "appear" to ever finish ... carefully mouse over the Tools ... Store Manager ... and click RIGHT and say open in new tab ... see if the Success message appears in the header ... if not, close that tab and wait 3-5 minutes and try again ...

You didn't say how many rows you have in the table:
products_attributes

but again, it can take some time to complete this ...

Remember us when you are rich and famous and we love coffee and donuts ... :cool:

Ok this has worked perfectly.
Now I have at least 10 spots between the current vales to put more colours in.
There are variations like "blue/grey" or "Animal print" etc on this site so the number of colours for this client may never end...

Cool beanos! Glad to hear that this worked for you ... :smile:

On a footnote to this, I still get this error when adding a new option value, its not relate to the query because I got the same error before it. Does the "/" cause a problem or is it something else? Seems if I try again, it works fine.

1062 Duplicate entry '219-1' for key 1
in:
[insert into products_options_values (products_options_values_id, language_id, products_options_values_name, products_options_values_sort_order) values ('219', '1', 'Blue / Fushia', '632')]
If you were entering information, press the BACK button in your browser and re-check the information you had entered to be sure you left no blank fields.

Where are you when you get that error?

What version of Zen Cart are you running?

Clean install or an upgrade?

Any add ons associated with the Admin or Attributes?

Its when adding a new option value
1.39h
Clean install
I have the stock by attributes addon.

You might check on the support thread for that add on about this ...

I do not use it ...