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

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

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

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

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.

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

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

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