Help to create list of unique products based on attributes applied

Hi Everyone,

I have a requirement to create a list of unique products from an inventory on zen cart using the attributes that are set.

For example products are listed like this:

model 1000 with options small, medium, large
model 1001 with options small, medium
model 1003 eith options medium, large

What I need to be able to do is build a query that will output a list like this:

model 1000 - small
model 1000 - medium
model 1000 - large
model 1001 - small
model 1001 - medium
model 1003 - medium
model 1003 - large

can anyone help with with the query please?

I note that on the store there is never multiple attributes/combination attributes as I would think that that would make it near impossible to do but am hope what I want to achieve should be.

Many thanks,

Phil

philip937:

Hi Everyone,

I have a requirement to create a list of unique products from an inventory on zen cart using the attributes that are set.

For example products are listed like this:

model 1000 with options small, medium, large
model 1001 with options small, medium
model 1003 eith options medium, large

What I need to be able to do is build a query that will output a list like this:

model 1000 - small
model 1000 - medium
model 1000 - large
model 1001 - small
model 1001 - medium
model 1003 - medium
model 1003 - large

can anyone help with with the query please?

I note that on the store there is never multiple attributes/combination attributes as I would think that that would make it near impossible to do but am hope what I want to achieve should be.

Many thanks,

Phil

Not sure what the true need is, but have you lookedat the available output of EasyPopulate ver 4? If not mistaken it offers this exact result if that is all that you are after.

ive not seen anything in easy populate 4. its a table join ?I need help with.

To get what I need I need the product table joined with the attributes table and in turn joined with the table that contains the attributes name.

so joining 'products' to 'products_attributes' with **products_id **then 'products_attributes' to 'products_options_values' with **products_options_values_id **

If there is anyone that can help me build this query i'd be greatful for the help. Thanks

philip937:

ive not seen anything in easy populate 4. its a table join ?I need help with.

To get what I need I need the product table joined with the attributes table and in turn joined with the table that contains the attributes name.

so joining 'products' to 'products_attributes' with **products_id **then 'products_attributes' to 'products_options_values' with **products_options_values_id **

If there is anyone that can help me build this query i'd be greatful for the help. Thanks

You mean both this won't do the trick for you and you didn't see it? It's been simplified down to the two pieces of data you were trying to display, but is essentially what you were after I thought, and EP4 can provide this as output to a file, so if you are trying to "reinvent" the wheel, was trying to save you some time unless there is some other need for the code to return that same type data.

SELECT
p.products_model    as v_products_model,
o.products_options_name             as v_products_options_name,
FROM '
.TABLE_PRODUCTS_ATTRIBUTES.     ' as a,'
.TABLE_PRODUCTS.                ' as p,'
.TABLE_PRODUCTS_OPTIONS.        ' as o,'
.TABLE_PRODUCTS_OPTIONS_VALUES. ' as v
WHERE
a.products_id       = p.products_id AND
a.options_id        = o.products_options_id AND
a.options_values_id = v.products_options_values_id AND
o.language_id       = v.language_id AND
o.language_id       = 1 ORDER BY a.products_id, a.options_id, v.products_options_values_id'

Cheers for this, i'll have a play.

no I hadnt seen this as when I use EP4 the option values are liated agains individual items seperated by commas so on the face of it didnt appear to be somewhere I could look for the info.

I'll have a play with what you posted cheers.

mc12345678:

You mean both this won't do the trick for you and you didn't see it? It's been simplified down to the two pieces of data you were trying to display, but is essentially what you were after I thought, and EP4 can provide this as output to a file, so if you are trying to "reinvent" the wheel, was trying to save you some time unless there is some other need for the code to return that same type data.

SELECT
p.products_model as v_products_model,
o.products_options_name as v_products_options_name,
FROM '
.TABLE_PRODUCTS_ATTRIBUTES. ' as a,'
.TABLE_PRODUCTS. ' as p,'
.TABLE_PRODUCTS_OPTIONS. ' as o,'
.TABLE_PRODUCTS_OPTIONS_VALUES. ' as v
WHERE
a.products_id = p.products_id AND
a.options_id = o.products_options_id AND
a.options_values_id = v.products_options_values_id AND
o.language_id = v.language_id AND
o.language_id = 1 ORDER BY a.products_id, a.options_id, v.products_options_values_id'

I did just notice that this only returns/addresses option names/option features in the first/primary language of the cart. (Note: o.language_id = 1 in the "last" line)

thats cool I only need it in one language!

philip937:

Cheers for this, i'll have a play.

no I hadnt seen this as when I use EP4 the option values are liated agains individual items seperated by commas so on the face of it didnt appear to be somewhere I could look for the info.

I'll have a play with what you posted cheers.

That I believe is the basic attribute export to which you are referring. The above comes from the detailed attribute list where more information is presented specific to a product and it's attribute.

For anyone else following, as said, I trimmed down the list of output variables significantly to just the two for which you indicated you were looking: model# and option name. If I happened to miss a variable you wanted, don't worry you have any field within those four tables from which you can pull a piece of information if you like.