WordPress.org

1. candell
   Member
   Posted 8 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>';
   
   }
   
    ?>

2. candell
   Member
   Posted 8 months ago #

   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

3. candell
   Member
   Posted 8 months ago #

   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

4. candell
   Member
   Posted 8 months ago #

   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

5. candell
   Member
   Posted 8 months ago #

   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.

6. candell
   Member
   Posted 8 months ago #

   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 }?>

7. daivu
   Member
   Posted 6 months ago #

   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??

8. candell
   Member
   Posted 6 months ago #

   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

9. daivu
   Member
   Posted 6 months ago #

   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!!!!!

10. daivu
    Member
    Posted 6 months ago #

    http://onlinepollsindia.com/

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

    plz help!!!!

Reply

You must log in to post.