MYsql query - product features

[nnvlahovic]

Hello everyone.

Iam glad to be part of prestashop, the best online shop ever.

Since Iam new to this, Iam learning and trying to learn as much as I can.

I have a small problem, if somebody can help me.

I want to export from databaze, phpMyAdmin, joined tables.

For now Iam using this query:

 

SELECT p.id_product, p.active, pl.name, GROUP_CONCAT( DISTINCT (

cl.name

)

SEPARATOR  "," ) AS categories, p.price, p.id_tax_rules_group, p.wholesale_price, p.reference, p.supplier_reference, p.id_supplier, p.id_manufacturer, p.upc, p.ecotax, p.weight, p.quantity, pl.description_short, pl.description, pl.meta_title, pl.meta_keywords, pl.meta_description, pl.link_rewrite, pl.available_now, pl.available_later, p.available_for_order, p.date_add, p.show_price, p.online_only, p.condition, p.id_shop_default

FROM ps_product p

LEFT JOIN ps_product_lang pl ON ( p.id_product = pl.id_product ) 

LEFT JOIN ps_category_product cp ON ( p.id_product = cp.id_product ) 

LEFT JOIN ps_category_lang cl ON ( cp.id_category = cl.id_category ) 

LEFT JOIN ps_category c ON ( cp.id_category = c.id_category ) 

LEFT JOIN ps_product_tag pt ON ( p.id_product = pt.id_product ) 

WHERE pl.id_lang =6

AND cl.id_lang =6

AND p.id_shop_default =1

AND c.id_shop_default =1

GROUP BY p.id_product

LIMIT 0 , 30

 

I would like to add to this, product features, from ps_feature_value_lang, length and width prom each product.

 

If somebody can help me with this, I would be very thankfull.

 

Thanks in advance.

[PascalVG]

for width and length, just add 

 

, p.width, p.height

 

 

to the SELECT , like:

 

SELECT p.id_product, p.active, pl.name, ... p.condition, p.id_shop_default, p.width,p.height 

FROM ...

 

(There's a third size unit, depth. if you want that one as well, add    , p.depth  )

 

 

Features maybe like this:

 

SELECT p.id_product, p.active, pl.name, GROUP_CONCAT( DISTINCT (

cl.name

)

SEPARATOR  "," ) AS categories, p.price, p.id_tax_rules_group, p.wholesale_price, p.reference, p.supplier_reference, p.id_supplier, p.id_manufacturer, p.upc, p.ecotax, p.weight, p.quantity, pl.description_short, pl.description, pl.meta_title, pl.meta_keywords, pl.meta_description, pl.link_rewrite, pl.available_now, pl.available_later, p.available_for_order, p.date_add, p.show_price, p.online_only, p.condition, p.id_shop_default, p.width, p.height, 

GROUP_CONCAT(DISTINCT (CONCAT(fl.name," : ",flv.value)) SEPARATOR ", " ) AS features

FROM ps_product p

LEFT JOIN ps_product_lang pl ON ( p.id_product = pl.id_product ) 

LEFT JOIN ps_category_product cp ON ( p.id_product = cp.id_product ) 

LEFT JOIN ps_category_lang cl ON ( cp.id_category = cl.id_category ) 

LEFT JOIN ps_category c ON ( cp.id_category = c.id_category ) 

LEFT JOIN ps_product_tag pt ON ( p.id_product = pt.id_product )

LEFT JOIN ps_feature_product fp ON (p.id_product = fp.id_product)

LEFT JOIN ps_feature_lang fl ON (fp.id_feature = fl.id_feature AND fl.id_lang = pl.id_lang)

LEFT JOIN ps_feature_value_lang flv ON (fp.id_feature_value = flv.id_feature_value AND flv.id_lang = pl.id_lang)

WHERE pl.id_lang =1

AND cl.id_lang =1

AND p.id_shop_default =1

AND c.id_shop_default =1

GROUP BY p.id_product

LIMIT 0 , 30

 

 

The features get grouped in one single field, separated by commas, and concatenated with their value (separated by ' : ' ) like this:

FeatureX : FeatureXvalue, FeatureY : FeatureYvalue, FeatureZ : FeatureZvalue etc

 

 

Hope this does it,

pascal.

[nnvlahovic]

Hello Pascal,

 

First, thanks for your reply. I really appreaciate it. 

Query is ok, but somehow, it returns 0,00000 (zeros) for length and width. 

It should be something like 200 | 150.

 

Thanks once more.

[PascalVG]

Hmm, can you check if the values are indeed stored in your ps_product table?

 

SELECT * FROM ps_product WHERE 1

 

or for one product

 

SELECT * FROM ps_product WHERE id_product = 1            (Change 1 into ID of one of your products that go wrong)

 

 

and see if the width and height fields are filled

 

 

pascal

[nnvlahovic]

Thanks for reply.

 

I found where is the problem. From this query,

 

SELECT * FROM ps_product WHERE id_product = 1  

 

It takes data from shipping instead from features, where are the sizes for each product.

 

Do you know how we can solve this?

 

Best regards.

[PascalVG]

If it's a feature value, it should be in the feature/featureValue list we created above, right?

 

Or do you need it as a separate field?

[nnvlahovic]

Thanks for answer Pascal,

 

I think we're almost done, I have features from your code, and everything is ok. I didnt see it well, and sorry for that.

 

Is there any possible way to have feature data, manufacture, size, length, width, colour etc., in each separate table.

 

Now I have like this:

 

Feature

Handmade, blue, 180.000, 300, 200,...

 

And I would like to have it like:

 

Manufacture | Colour | Knots     | Length | Width |...

Handmade   | Blue     | 180.000 | 300     | 200     | ....

....

....

...

 

Thanks.

[nnvlahovic]

Thanks Pacal for your replies.

 

Best regards.