how to fetch wp_posts details with source and image

1 Views

i used the following query to fetch the tittle,data,tagss content,slug,parent id .
this work fine but i need to get the post image and source of image that have a relation ship with wp_postmeta table so i used the second query in while loop inside of first query

First query:-
SELECT wp_posts.ID as Id,wp_posts.post_title as Title,wp_posts.post_date as DATE,

Read More
  GROUP_CONCAT(wp_terms.name) AS TAGS, wp_posts.post_content as CONTENT,wp_terms.term_id,wp_terms.slug,wp_posts.post_parent as parent_id FROM wp_terms

  INNER JOIN wp_term_taxonomy ON wp_terms.term_id  = wp_term_taxonomy.term_id

  INNER JOIN wp_term_relationships ON wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id

  INNER JOIN wp_posts ON wp_posts.ID = wp_term_relationships.object_id

  WHERE post_type LIKE 'post' AND post_status LIKE 'publish' AND wp_terms.slug in ('interview','variety','lastpage','sport','arab-global','opinion','business','uae','firstpage')  

GROUP BY wp_posts.ID order by post_date limit 150

and the sencond query as 

select select wp_postmeta.meta_value from wp_postmeta left join wp_posts on wp_postmeta.post_id in ('".$row['Id']."') = wp_posts.id in ('".$row['Id']."') and meta_key='_wp_attached_file'"

but i does not get any details so i make the relation ship with parent_id of wp_post table as the third query as instead of second query. 

select select wp_postmeta.meta_value from wp_postmeta left join wp_posts on wp_postmeta.post_id in ('".$row['parent_id']."') = wp_posts.id in ('".$row['parent_id']."') and meta_key='_wp_attached_file'"

after that i used single query to fetch all records like as below 

SELECT wp_posts.ID as Id,wp_posts.post_title as Title,wp_posts.post_date as DATE,wp_postmeta.meta_key,wp_postmeta.meta_value,
GROUP_CONCAT(wp_terms.name) AS TAGS, wp_posts.post_content as CONTENT,wp_terms.term_id,wp_terms.slug,wp_posts.post_parent as parent_id FROM wp_terms
INNER JOIN wp_term_taxonomy ON wp_terms.term_id  = wp_term_taxonomy.term_id
INNER JOIN wp_term_relationships ON wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id
INNER JOIN wp_posts ON wp_posts.ID = wp_term_relationships.object_id
INNER JOIN wp_postmeta ON(wp_posts.ID = wp_postmeta.post_id)
WHERE post_type LIKE 'post' AND post_status LIKE 'publish'
GROUP BY wp_posts.ID order by post_date limit 150

but in that query i got multiple meta_key and multiple meta value there is any useful solution for that so i can fetch wp_post details with its source and image by simple in my sql and php

Post Views: 1

Related posts

Leave a Reply

2 comments

  1. Try this

    $meta_value_id= “select meta_value from wp_postmeta where meta_key=’_thumbnail_id’ and post_id='”.$row[‘Id’].”‘”;
    $query_run_meta= mysql_query($meta_value_id);
    $row1= mysql_fetch_assoc($query_run_meta);

                $meta_image=  "select meta_value from wp_postmeta where meta_key='_wp_attached_file' and post_id='".$row1['meta_value']."'";   
            $query_meta=  mysql_query($meta_image);
            $row2=  mysql_fetch_assoc($query_meta);

    now you can get image source from $row2.

  2. From above query you are getting post id, you can use following functions to get post image url:

    $post_thumbnail_id = get_post_thumbnail_id( $post_id ); //It will return id of attached thumbnail
 $post_image_src= wp_get_attachment_image_src(post_thumbnail_id);// This will return source url of thumbnail