skip to Main Content

I need to use some library to export product names, SKU and prices to one CSV file. This library connects using PDO and needs an SQL query.

I want to select ‘name’, ‘SKU’ and ‘price’ from 2 WordPress tables, namely wp_posts and wp_postmeta.

I don’t know how to get data from ‘meta_value’ column twice for ‘meta_key’=’_price’ and ‘meta_key’=’_sku’, ex.

phpMyAdmin WordPress table

My current query:

"SELECT a.post_title, m1.meta_value, m2.meta_value FROM wp_posts a, wp_postmeta m1, wp_postmeta m2
        WHERE a.post_type='product' AND m1.post_id = a.ID
        AND m1.meta_key='_sku'
        AND m2.meta_key='_price'"

2

Answers


  1. It sounds like you could do with a join so you’re relating the meta information to the right posts.

    SELECT
      post.post_title,
      meta.meta_value
    FROM wp_posts AS post
    LEFT JOIN wp_postmeta AS meta
      ON post.post_id = meta.post_id
    WHERE post.post_type = 'product'
      AND meta.meta_key IN ('_sku', '_price')
    

    Example results:

    post_title    | meta_value
    --------------|-----------
    Cheddar       | CHE001
    Cheddar       | 2.45
    Red Leicester | CHE002
    ...
    

    This assumes that the id column in wp_posts is post_id.

    It’s important to note that this will return up to two rows for each post, depending on whether it has a meta row for _sku and _price). If you need the data all on the same row (as you might for your export) you might need something like this instead:

    SELECT
      post.post_title,
      metaSku.meta_value AS sku,
      metaPrice.meta_value AS price
    FROM wp_posts AS post
    LEFT JOIN (
      SELECT
        *
      FROM wp_postmeta
      WHERE meta_key = '_sku'
    ) AS metaSku
      ON post.post_id = metaSku.post_id
    LEFT JOIN (
      SELECT
        *
      FROM wp_postmeta
      WHERE meta_key = '_price'
    ) AS metaPrice
      ON post.post_id = metaPrice.post_id
    WHERE post.post_type = 'product'
    

    Example results:

    post_title    | sku    | price
    --------------|--------|------
    Cheddar       | CHE001 | 2.45
    Red Leicester | CHE002 |
    ...
    

    I hope this helps.

    Login or Signup to reply.
  2. This will works for me

    SELECT post.post_title, metaSku.meta_value AS sku, metaPrice.meta_value AS price FROM wp_posts AS post LEFT JOIN ( SELECT * FROM wp_postmeta WHERE meta_key = '_sku' ) AS metaSku ON post.ID = metaSku.post_id LEFT JOIN ( SELECT * FROM wp_postmeta WHERE meta_key = '_price' ) AS metaPrice ON post.ID = metaPrice.post_id WHERE post.post_type = 'product';
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search