skip to Main Content

I have a problem when doing a SQL query in WordPress, I would like to have each Post ID in a single row with the meta_key columns, but I get several uneven records.

SELECT P.ID,
     IF(PM.meta_key = 'nombrem', PM.meta_value, NULL) AS nombrem,
     IF(PM.meta_key = 'generom', PM.meta_value, NULL) AS generom,
     IF(PM.meta_key = 'tiempom', PM.meta_value, NULL) AS tiempom,
     IF(PM.meta_key = 'urlm', PM.meta_value, NULL) AS urlm,
     IF(PM.meta_key = 'imagenm', PM.meta_value, NULL) AS imagenm 
FROM K1nG_posts AS P
LEFT JOIN K1nG_postmeta AS PM ON ( P.ID = PM.post_id ) 
WHERE P.post_type = 'post' 
AND (P.post_status = 'publish' OR P.post_status = 'private')  ORDER BY P.post_date DESC

By placing the above code I get the following in phpmyadmin, I want a single post ID to have each meta_value value in a non-diagonal horizontal column as shown.

enter image description here

2

Answers


  1. You seeem to be looking for conditional aggregation. For this, you would need to add a GROUP BY clause and surround your conditional expressions with an aggregate function:

    SELECT P.ID,
        MAX(CASE WHEN PM.meta_key = 'nombrem' THEN PM.meta_value END) AS nombrem,
        MAX(CASE WHEN PM.meta_key = 'generom' THEN PM.meta_value END) AS generom,
        MAX(CASE WHEN PM.meta_key = 'tiempom' THEN PM.meta_value END) AS tiempom,
        MAX(CASE WHEN PM.meta_key = 'urlm'    THEN PM.meta_value END) AS urlm,
        MAX(CASE WHEN PM.meta_key = 'imagenm' THEN PM.meta_value END) AS imagenm
    FROM 
        K1nG_posts AS P
        LEFT JOIN K1nG_postmeta AS PM ON P.ID = PM.post_id 
    WHERE 
        P.post_type = 'post' 
        AND (P.post_status = 'publish' OR P.post_status = 'private')  
    GROUP BY P.ID, P.post_date
    ORDER BY P.post_date DESC
    
    Login or Signup to reply.
  2. Group the data by P.id and max the ifs out

    like

    SELECT P.ID,
     MAX(IF(PM.meta_key = 'nombrem', PM.meta_value, NULL)) AS nombrem,
     MAX(IF(PM.meta_key = 'generom', PM.meta_value, NULL)) AS generom,
     MAX(IF(PM.meta_key = 'tiempom', PM.meta_value, NULL)) AS tiempom,
     MAX(IF(PM.meta_key = 'urlm', PM.meta_value, NULL)) AS urlm,
     MAX(IF(PM.meta_key = 'imagenm', PM.meta_value, NULL)) AS imagenm 
    FROM K1nG_posts AS P
      LEFT JOIN K1nG_postmeta AS PM ON ( P.ID = PM.post_id ) 
    WHERE P.post_type = 'post' 
      AND (P.post_status = 'publish' OR P.post_status = 'private')  ORDER BY P.post_date DESC
    GROUP BY P.ID;
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search