Stocks by attributes

[evenangelsdie]

One other thing... How do I change the sort order of the sizes I have listed? I have tried in the admin backend but they still display alphabetically it looks like:

-Large
-Medium
-Small

instead of S, M, L.

[krazykev]

In the attribute controller (where you create the attributes) you can give each attribute a sort order (a numerical value). Lower values are listed first. e.g.

10 => small
20 => large
30 => x-large

[cropinstop]

Applied the changes below, and get the error message

1146 Table 'cactus2_zc2.products_with_attributes_stock' doesn't exist
in:
[select pov.products_options_values_id, pov.products_options_values_name, pa.* from zen_products_attributes pa join zen_products_options_values pov on pa.options_values_id = pov.products_options_values_id join products_with_attributes_stock PWAS on PWAS.stock_attributes = pa.products_attributes_id where pa.products_id = '339' and pa.options_id = '23' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '1' and PWAS.quantity > 0 order by LPAD(pa.products_options_sort_order,11,"0"), pov.products_options_values_name]

pasted the change from the post, so should be good. Zcart 1.3.8, php 5.2.6.

Stock with attributes worked OK before applying the change, with the Out of Stock items being displayed as OOS at checkout.

Verified table "cactus2_zc2.products_with_attributes_stock" does exist.

Should
"Join products_with_attributes_stock PWAS"
be
"join pa.products_with_attributes_stock PWAS" ??

Hi Kellan,

I did something very similar but didn't check for null/0 so didn't have to make your second change above.

select pov.products_options_values_id,
pov.products_options_values_name,
pa.*
from " . TABLE_PRODUCTS_ATTRIBUTES . " pa join " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov
on pa.options_values_id = pov.products_options_values_id
join products_with_attributes_stock PWAS
on PWAS.stock_attributes = pa.products_attributes_id
where pa.products_id = '" . (int)$_GET['products_id'] . "'
and pa.options_id = '" . (int)$products_options_names->fields['products_options_id'] . "'
and pa.options_values_id = pov.products_options_values_id
and pov.language_id = '" . (int)$_SESSION['languages_id'] . "'
and PWAS.quantity > 0 " .
$order_by;


This is in attributes.php

[krazykev]

Hi,

>Verified table "cactus2_zc2.products_with_attributes_stock" does exist.

Are you absolutely sure it exists? (Check for prefixes)


>Should "Join products_with_attributes_stock PWAS" be "join pa.products_with_attributes_stock PWAS" ??

No - this isn't valid SQL.

Are you using table prefixes - in which case cactus2_zc2.products_with_attributes_stock may really be something like cactus2_zc2.zen_products_with_attributes_stock.


In fact, to test this theory, I have amended the SQL to use the table define - try this query instead

select pov.products_options_values_id,
pov.products_options_values_name,
pa.*
from " . TABLE_PRODUCTS_ATTRIBUTES . " pa join " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov
on pa.options_values_id = pov.products_options_values_id
join " . TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK . " PWAS
on PWAS.stock_attributes = pa.products_attributes_id
where pa.products_id = '" . (int)$_GET['products_id'] . "'
and pa.options_id = '" . (int)$products_options_names->fields['products_options_id'] . "'
and pa.options_values_id = pov.products_options_values_id
and pov.language_id = '" . (int)$_SESSION['languages_id'] . "'
and PWAS.quantity > 0 " .
$order_by;

Post back with your results.

Kev

[kellan4459]

Are you using table prefixes - in which case cactus2_zc2.products_with_attributes_stock may really be something like cactus2_zc2.zen_products_with_attributes_stock.

Based on the way the define for the stock attribute table is in code I would believe that Kev is on to something here

define('TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK', DB_PREFIX . 'products_with_attributes_stock');

the DB_PREFIX in the define looks to be the problem.

Kellan

[cropinstop]

Are you absolutely sure it exists? (Check for prefixes)



>Should "Join products_with_attributes_stock PWAS" be "join pa.products_with_attributes_stock PWAS" ??

No - this isn't valid SQL.

Are you using table prefixes - in which case cactus2_zc2.products_with_attributes_stock may really be something like cactus2_zc2.zen_products_with_attributes_stock.

yes, am using a table prefix zen. Need to get the glasses fixed

In fact, to test this theory, I have amended the SQL to use the table define - try this query instead

select pov.products_options_values_id,
pov.products_options_values_name,
pa.*
from " . TABLE_PRODUCTS_ATTRIBUTES . " pa join " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov
on pa.options_values_id = pov.products_options_values_id
join " . TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK . " PWAS
on PWAS.stock_attributes = pa.products_attributes_id
where pa.products_id = '" . (int)$_GET['products_id'] . "'
and pa.options_id = '" . (int)$products_options_names->fields['products_options_id'] . "'
and pa.options_values_id = pov.products_options_values_id
and pov.language_id = '" . (int)$_SESSION['languages_id'] . "'
and PWAS.quantity > 0 " .
$order_by;

Post back with your results.

Kev

Tried the revised SQL, and now the out-of stock items are masked from the selection menu, BUT, all selection menus for items that are not "stock by attributes" have the selection list truncated such that no selections are visible. Go to http://offlinestore.azbizp.com/index...products_id=83 for an example.

[krazykev]

Hi cropinstop,

I see what you mean. I only use one attribute so that SQL works great for me. I will try and replicate your issue but I tihnk I may struggle. You could try a left join on PWAS - see sql below - BUT I HAVEN'T TRIED IT!!!

select pov.products_options_values_id,
pov.products_options_values_name,
pa.*
from " . TABLE_PRODUCTS_ATTRIBUTES . " pa join " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov
on pa.options_values_id = pov.products_options_values_id
LEFT join " . TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK . " PWAS
on PWAS.stock_attributes = pa.products_attributes_id
where pa.products_id = '" . (int)$_GET['products_id'] . "'
and pa.options_id = '" . (int)$products_options_names->fields['products_options_id'] . "'
and pa.options_values_id = pov.products_options_values_id
and pov.language_id = '" . (int)$_SESSION['languages_id'] . "'
and PWAS.quantity > 0 " .
$order_by;

[kellan4459]

yes, am using a table prefix zen. Need to get the glasses fixed


Tried the revised SQL, and now the out-of stock items are masked from the selection menu, BUT, all selection menus for items that are not "stock by attributes" have the selection list truncated such that no selections are visible. Go to http://offlinestore.azbizp.com/index...products_id=83 for an example.

cropinstop and kev

I use the left join with mine and it works across all items with attributes whether they have their own stock value or not.

example of mine is at

http://www.zen-cart.com/forum/showpo...&postcount=559

[cropinstop]

Change by krazykev did not fix the problem, change by kellan4459 (post 559) seems to be working. Will do more testing offline to see if there are problems.

Thanks

[krazykev]

Cropinstop,

I guess you have the problem fixed but I would like to understand this better. I am quite new to Zen Cart, so wondered if you could elaborate on your statement

>BUT, all selection menus for items that are not "stock by attributes"

What kind of attributes are you using that do not use "stock by attributes" such that they don't get displayed?

Kev