Need help composing crazy complex nested MySQL query that returns simple array

Question

I'm having a bear of a time with this - I have a table with just 4 columns that define anywhere from 3 to 20 bits of info. I'm not even sure how to describe the problem except by showing parts of the DB and my desired output, so here goes. The table has 4 columns: meta_id, post_id, meta_key, meta_value. Yup, it's from WP. I need to start, (i think) with a list of distinct post_id where meta_key = "location" and meta_value = "washington." Using that list, I'd like to return a new "table" or array of ALL the other data where post_id = each one of the distinct values, the meta_key value is a new field name with the corresponding meta_value as it's value. I know that doesn't make a lot of sense, but here's the related data from ONE post_id from my distinct list - that may help you see what I'm after. I got the post_id for this result from the DISTINCT list of post_id mentioned above.

meta_id  |  post_id  |  meta_key  |  meta_value
---------|-----------|------------|-------------------
46922    |  5923     |  adlink    | htp://whatever.com
46923    |  5923     |  subloc    | Menu Page
46924    |  5923     |  size      | Premium
46924    |  5923     |  location  | Washington
46924    |  5923     |  image1    | 5924
46924    |  5923     |  image2    | 5925

Now, here's the kicker: I need to substitute the meta_value where meta_key = "image1" or "image2" with the meta_value where post_id = the meta_value where post_id = 5923 AND meta_key = "image1" or "image2". That data comes from part of the table that looks like this:

meta_id  |  post_id  |  meta_key  |  meta_value
---------|-----------|------------|-------------------
46942    |  5924     |  file      | img1.jpg
46923    |  5925     |  file      | img2.jpg

What I'd LOVE would look like this:

id   |  adlink            |  sublock  |  size   | location   | image1   | image2
-----|--------------------|-----------|---------|------------|----------|---------
5923 | htp://whatever.com | Menu Page | Premium | Washington | img1.jpg | img2.jpg

Does that make sense? It's really killing me - any help with this complex query is GREATLY appreciated!

kennypu · Accepted Answer · 2014-02-13 09:48:17Z

1

ha, working with wordpress I do this all the time. you basicly want to use several joins, and use the column_name as desired_name to set the column names. here's an example for you using id 5923:

SELECT meta1.post_id as 'id', 
 meta1.meta_value as 'adlink',
 meta2.meta_value as 'sublock',
 meta3.meta_value as 'size',
 meta4.meta_value as 'location',
 meta5.meta_value as 'image1',
 meta6.meta_value as 'image2' 

FROM wp_postmeta as meta1 

INNER JOIN wp_postmeta as meta2 ON meta1.post_id = meta2.post_id 
INNER JOIN wp_postmeta as meta3 ON meta1.post_id = meta3.post_id 
INNER JOIN wp_postmeta as meta4 ON meta1.post_id = meta4.post_id
INNER JOIN wp_postmeta as meta5 ON meta1.post_id = meta5.post_id
INNER JOIN wp_postmeta as meta6 ON meta1.post_id = meta6.post_id

WHERE 
  meta1.post_id = '5923'  
  AND meta1.meta_key = "adlink"
  AND meta2.meta_key = "sublock"
  AND meta3.meta_key = "size"
  AND meta4.meta_key = "location"
  AND meta5.meta_key = "image1"
  AND meta6.meta_key = "image2"

this is untested but you should get the idea. hope it helps

answered Feb 13, 2014 at 9:48

kennypu

6,1332 gold badges24 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

David McClave Over a year ago

OK - the HARD part, (for me) is figuring out how to get the resultant table to include EACH of the DISTINCT post_id as a separate row - so that the query includes getting the DISTINCT list as well... make sense?

kennypu Over a year ago

@DavidMcClave so you want results for more than one post_id i'm assuming? are you trying to get results for a select few or all post_id?

David McClave Over a year ago

trying to get results for just the data related to meta_key="location" and meta_value="washington" - there will be many. So, yes, more than one post_id

kennypu Over a year ago

in that case, in the above example, you just need to add AND meta4.meta_value = 'Washington' and that's it. that will return all post_id + all other info where location = washington

kennypu Over a year ago

@DavidMcClave oh but remove the first meta1.post_id = '5923' since that won't be needed in that case.

|

David McClave · Accepted Answer · 2014-02-17 18:16:41Z

The fastest and most efficient query to accomplish this was as follows:

$result = mysql_query("SELECT post_id,
       MAX(CASE WHEN meta_key = 'size' THEN meta_value END) size,
       MAX(CASE WHEN meta_key = 'adlink' THEN meta_value END) adlink,
       MAX(CASE WHEN meta_key = 'frontpage' THEN meta_value END) frontpage,
       MAX(CASE WHEN meta_key = 'expiration' THEN meta_value END) expiration,
       MAX(CASE WHEN meta_key = 'image1' THEN meta_value END) image1,
       MAX(CASE WHEN meta_key = 'image2' THEN meta_value END) image2,
       MAX(CASE WHEN meta_key = 'image1' THEN meta_value END) image3,
       MAX(CASE WHEN meta_key = 'image2' THEN meta_value END) image4,
       MAX(CASE WHEN meta_key = 'iframe' THEN meta_value END) iframe,
       MAX(CASE WHEN meta_key = 'location' THEN meta_value END) location,
       MAX(CASE WHEN meta_key = 'sublocation' THEN meta_value END) sublocation
       FROM wp_postmeta WHERE post_id IN(SELECT post_id from wp_postmeta WHERE meta_value LIKE '%". $page ."%')
 GROUP BY post_id");

Collectives™ on Stack Overflow

Need help composing crazy complex nested MySQL query that returns simple array

2 Answers 2

11 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related