Jump to content

MYsql query - product features


nnvlahovic
 Share

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More