sql query help to grab post title, postmeta and featured image

  • Resolved candell

    (@candell)


    13 years, 2 months ago

    I am trying to code up a page that will display the title, featured image and permalink of published pages that utilise a certain template.

    SQL is not my strong point, so far I have managed to write a script that will call the titles of the posts that come from a certain template using a join, but I am stuck on also outputting the featured image associated with the post.

    Can someone help please?

    Here is what I have muddled together so far

    <?php

 $querystr = "
    SELECT $wpdb->posts.id, $wpdb->posts.post_title, $wpdb->postmeta.meta_value
    FROM $wpdb->posts, $wpdb->postmeta
    WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id
    AND $wpdb->posts.post_status = 'publish'
    AND $wpdb->posts.post_date < NOW()
	AND $wpdb->postmeta.meta_value = 'scheme.php'
    ORDER BY $wpdb->posts.post_date DESC
 ";

 $theposts = $wpdb->get_results($querystr);

 foreach ($theposts as $pageresult) {

    echo '<p>' .$pageresult->post_title. '</p>';

}

 ?>
Viewing 9 replies - 1 through 9 (of 9 total)
  • Thread Starter candell

    (@candell)

    Update

    Just noticed that the featured image is actually in the postmeta table so I modified my code to

    <?php

 $querystr = "
    SELECT $wpdb->posts.id, $wpdb->posts.post_title, $wpdb->postmeta.meta_value, $wpdb->postmeta._wp_attached_file
    FROM $wpdb->posts, $wpdb->postmeta
    WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id
    AND $wpdb->posts.post_status = 'publish'
    AND $wpdb->posts.post_date < NOW()
	AND $wpdb->postmeta.meta_value = 'scheme.php'
    ORDER BY $wpdb->posts.post_date DESC
 ";

 $theposts = $wpdb->get_results($querystr);

 foreach ($theposts as $pageresult) {

    echo '<p><img src=' .$pageresult->_wp_attached_file. ' class=alignleft />' .$pageresult->post_title. '</p>';

}

 ?>

    But this returns nothing. Looking more into the database, one post can have multiple entries in postmeta, in my case I need to pull out two rows from post meta (along with some data from posts) but it is stopping after it finds the first.

    How can I modify my code to get the feaqtured image out?

    Thanks

    Thread Starter candell

    (@candell)

    Sorry to be talking to myself, but I feel I am getting closer, my query returns an empty result though. Here is my latest effort

    SELECT wp_posts.id, wp_posts.post_title, wp_postmeta.meta_value, wp_postmeta.meta_value FROM wp_posts, wp_postmeta
WHERE wp_posts.ID = wp_postmeta.post_id
AND wp_postmeta.meta_value = '_wp_attached_file'
AND wp_posts.post_status = 'publish'
AND wp_posts.post_date < NOW()
AND wp_postmeta.meta_value = 'scheme.php'
ORDER BY wp_posts.post_date DESC
    Thread Starter candell

    (@candell)

    Oh I see what is wrong with the last posts sql, I am calling wp_postmeta.meta_value twice.

    How do I call it so that it matches both i.e.

    for post_id = 10

    wp_postmeta._wp_page_template = ‘scheme.php’

    AND

    wp_postmeta._wp_attached_file = not empty

    Thread Starter candell

    (@candell)

    Still plugging away at this. I need two column values from posts and two rows from metadata so I decided the way to go is to run a query on posts that will grab what I need from there, then within the output loop to run two queries from metadata based on the post_id and also the meta_key.

    My second query isnt working though, it isnt being built correctly

    [Code moderated as per the Forum Rules. Please use the pastebin]

    echo $querystr2; is coming out as

    SELECT FROM wp_postmeta WHERE = ‘231’ AND = ‘_wp_attached_file’ so it is missing what to select and the condition in the AND.

    Thread Starter candell

    (@candell)

    Trying a different tact, instead of going directly into the database poking around the wordpress knowledgebase I found some fucstions that should do what I am after.

    I am almost there but for two things

    1. It is printing the correct number of results but all of the titles are the title of the page I am currently on when testing the code. How do I make it pull the title from the row in the loop?

    2. It is bringing in everything due to the first line requesting everything of status ‘publish’. I also need to limit the output based upon the meta_key of ‘ _wp_page_template’ being set to ‘scheme.php’

    <?php $pages = get_pages(array('post_status' => 'publish')); ?>

<?php foreach($pages as $page) { ?>

<h1><?php the_post_thumbnail(); ?><?php the_title(); ?></h1>

<p><?php echo get_post_meta($page->ID, 'schemeprice', true) ?></p>

 <?php }?>
    daivu

    (@daivu)

    where is the code?? which page???

    i want to edit post query i want to show post who have open comment…. in home page??? how these possible??

    Thread Starter candell

    (@candell)

    I used query_posts() to achieve what I was after, you can pass all sorts of arguments to it making it very powerful to generate very custom queries

    ohh! but archive page are not in my theme. i am using p2 theme there is only entry.php but no code or query in this page. i want to update query where i have to just add that comment_status=open…

    thats it..

    but query is where, no idea.

    plz help yara!!!!!

    https://onlinepollsindia.com/

    here in home page i want to show only those post who have open comment….?

    plz help!!!!

Viewing 9 replies - 1 through 9 (of 9 total)
  • The topic ‘sql query help to grab post title, postmeta and featured image’ is closed to new replies.

Tags