How to fetch all meta_key and meta_value using post_id wihout duplication

  • How to fetch all meta_key and meta_value using post_id wihout duplication of post meta using sql query.
    This query is repeting the post meta each time .

    $sql=”SELECT * FROM wp_posts
    LEFT JOIN wp_postmeta
    ON wp_posts.ID= wp_postmeta.post_id
    where post_type=’property'”;

Viewing 1 replies (of 1 total)

  • I’m not completely sure what you’re going for, but what I like to do is pull a single row from wp_posts and convert the records from wp_postmeta into columns so all the data I want for a given post is in one row.

    Say for example that you want to grab all the entries from the wp_posts table where the post_type is “property”, and you want the meta_values for the meta_keys “address”, “city”, and “state” from the wp_postmeta table returned as columns. You could do something like this:

    SELECT wp_posts.*, pm1.meta_value AS address, pm2.meta_value AS city, pm3.meta_value AS state FROM wp_posts
  JOIN wp_postmeta AS pm1 ON wp_posts.ID=pm1.post_id AND pm1.meta_key = 'address'
  JOIN wp_postmeta AS pm2 ON wp_posts.ID=pm2.post_id AND pm2.meta_key = 'city'
  JOIN wp_postmeta AS pm3 ON wp_posts.ID=pm3.post_id AND pm3.meta_key = 'state'
  WHERE post_type = 'property'

    You can add additional JOIN statements for each record in the wp_postmeta table that you want returned as columns. I used aliases for the wp_postmeta table to save typing, but you don’t have to.

Viewing 1 replies (of 1 total)
  • The topic ‘How to fetch all meta_key and meta_value using post_id wihout duplication’ is closed to new replies.

Tags