CODEWITHSUNDEEP
phpmysqljoin
David McClave
David McClave

Reputation: 167

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

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!

Upvotes: 2

Views: 69

Answers (2)

David McClave
David McClave

Reputation: 167

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");

Upvotes: 0

kennypu
kennypu

Reputation: 6061

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

Upvotes: 1

Related Questions