Skip Navigation

This thread is resolved. Here is a description of the problem and solution.

Problem:
How to display, using sql query, the products that have custom attributes?

Solution:
The custom attribute information are stored on the *_postmeta table. Thus, we can write the sql query like this:

SELECT t1.ID, t1.post_title, t2.meta_key, t2.meta_value FROM wp_posts AS t1 INNER JOIN wp_postmeta AS t2 ON t2.post_id = t1.ID WHERE t2.meta_key = '_product_attributes' and t2.meta_value LIKE '%|%'

This will show all products that have custom attributes and are divided by the pipe "|" (more than 1 attribute ).

This is the technical support forum for WPML - the multilingual WordPress plugin.

Everyone can read, but only WPML clients can post here. WPML team is replying on the forum 6 days per week, 22 hours per day.

This topic contains 2 replies, has 2 voices.

Last updated by Jurgen 4 years, 8 months ago.

Assigned support staff: Bruno.

Author Posts
April 1, 2016 at 10:22 am #851042

Jurgen

I am trying to: translate custom attributes of products

URL of (my) website where problem appears: hidden link (still in dev)

I expected to see: a tab of custom attributes

Instead, I got: an extra column when translating products

Steps to duplicate the issue: N/A

I created a lot of products and most of the time via "registered" attributes so they have their specific tab when translating. Unfortunately some products are that specific so we did not really create the attribute but added it using custom attributes.

These attributes appear in an additional column at the right when translating products. Now I don't want to open each and every product ("edit translation") to check which product got such a custom product. Is there a way (sql query?) to get a list of products that have one or more custom attributes?

April 1, 2016 at 9:19 pm #851574

Bruno

Hello Jurgen,

The custom attribute information are stored on the *_postmeta table.

I'm not sure if this should answer your question, but this SQL query takes the name of the posts having "meta_key" == "_product_attributes" and they are not empty ( that is has attributes ).

SELECT t1.ID, t1.post_title, t2.meta_key, t2.meta_value FROM wp_posts AS t1 INNER JOIN wp_postmeta AS t2 ON t2.post_id = t1.ID WHERE t2.meta_key = '_product_attributes' and t2.meta_value != 'a:0:{}'

This other query showing all products that have custom attributes and are divided by the pipe "|" (more than 1 attribute ).

SELECT t1.ID, t1.post_title, t2.meta_key, t2.meta_value FROM wp_posts AS t1 INNER JOIN wp_postmeta AS t2 ON t2.post_id = t1.ID WHERE t2.meta_key = '_product_attributes' and t2.meta_value LIKE '%|%'

Please let me know if that helps solve the problem and if I can help in any other question you may have.

Thank you.

April 3, 2016 at 11:58 am #851863

Jurgen

Hi Bruno,

Thanks for your swift reply & queries 🙂 It was the 2nd query that gave me the required products! Thanks a lot!

Best regards,
Jurgen