Any way to provide custom select query to the loop?

I’ve been dying to do this for absolutely ages.. but no one has ever helped me ?? It should be easy but it doesn’t seem to be.

@lellie: I think I’ve figured it out.

To display posts derived from your own SELECT query, try something like (pseudo code):

$myposts = $wpdb->get_results("SELECT SOMETHING FROM THE DB", ARRAY_A); // only field really needed is ID from wp_posts

Then, where you would have your normal loop, use:

for ($i=0; $i < count($myposts); $i++):
$post = get_post($myposts[$i][ID], OBJECT);
setup_postdata($post);

/* And now just include the template tags for the
content you want to display as you would in a
normal loop (ie the_excerpt, the_content,
the_permalink and so on */

endfor;

So, basically the select query goes above the loop, and the code below that replaces the loop.

Hope this helps.

Much warmth,

planetthoughtful

I should probably think about adding this to the doc wiki. Anyone else think it might be useful information to have over there?

If you do you might want to simplify it through a mod of the pre-1.5 loop format:

<?php
$myposts = $wpdb->get_results("SELECT * FROM $wpdb->posts, $wpdb->postmeta WHERE ID = post_id and meta_key='tags' and meta_value='value_of_tag'");

if($myposts) : foreach($myposts as $post) : setup_postdata($post);
?>

…

<?php endforeach; endif; ?>

If one selects and stores all post(s) table content in their $myposts array, get_post is not required here.

Thanks for the tip, Kafkaesqui. I think I will put this on the wiki, if only to save other plugin authors who want to roll their own select queries for post loops a few headaches. I just need to figure out where it needs to go.

Much warmth,

planetthoughtful

Okay, I’ve created a draft Codex article at https://codex.www.ads-software.com/Displaying_Posts_Using_a_Custom_Select_Query

I’ll leave it in draft for a couple of days in case anyone has any comments / edits / changes they’d like me to make, and if I don’t get any feedback, I’ll move it over to the ‘New page created’ category for Codex editors to review.

Much warmth,

planetthoughtful

ooo.. I presume this doesn’t have to just be used on pages but could be used in many many places.. I’ve asked for help with this before and every time I’ve had no response! I shall have a play with it later! THANKYOU!!!

Hi Everyone,
I am using this method on my archive pages and it is working great (I am ordering my posts by rating). The only problem is that no matter which category I go to, It displays all the posts. Is there something I can change or add to only show the current category?

Sounds like your theme uses index.php for all views. Check out Template_Hierarchy — you may want to create a category.php or achive.php template page.

I might not understand you correctly but…

My theme is the default theme and yes, index.php does display all posts and that is fine. but my problem is that i have made a custom archive.php with a custom query in the loop and it is displaying all posts and not just the certain category. I am not sure but I think it has something to do with the lines

<?php foreach ($pageposts as $post): ?>
<?php setup_postdata($post); ?>

because in the original default archive.php it had

<?php if (is_category()) { $posts = query_posts($query_string . ‘&orderby=title&order=asc’); } ?>
<?php while (have_posts()) : the_post(); ?>

Doesnt the new one need something like if(is_category…?
I have no idea. Please excuse my ignorance but I am new to wordpress and not a programmer at all, ust a designer.

ps. if you are reading this Kaf, using a custom query caused your post_image plugin to stop working. It worked before and the reating plugin still works with the new query. any ideas?

I am using this method on my archive pages and it is working great (I am ordering my posts by rating). The only problem is that no matter which category I go to, It displays all the posts.

The archive Page has nothing to do with the categories. You either go to a category – and view it’s “archives”, i.e. all the posts in that specific category… or you go to the archive page, which is THE archive of the blog, not of any specific category.

Ok well I might have worded that wrong but the problem is still the same. When you click on a link on the menu under “Categories” it brings you to a page listing “Archives” of that category based on the template archive.php.

See https://codex.www.ads-software.com/Template_Hierarchy

moshu, I don’t think you get the problem. I read the template heirarchy and I know I can make category.php to be the template instead. I can change the template and customize the template, that is all working great. But ever since I used a custom query, I can’t figure out why it is displaying all posts regardless of the category. (and know i don’t want to make a custom template for every category)

Take a look for yourself.
https://travisdahl.com/wp/archives/category/punk/nofx/

try clicking on a different category link. and look at which posts show up.

I want it to work just like the default install. the only reason I edited it is to get it to order by rating and not date or title.

Change of my $myposts line from above (to provide by category querying):

$myposts = $wpdb->get_results("SELECT * FROM $wpdb->posts, $wpdb->post2cat, $wpdb->postmeta WHERE ID = $wpdb->postmeta.post_id AND ID = $wpdb->post2cat.post_id AND category_id = $cat AND meta_key='tags' and meta_value='value_of_tag'");

The global WordPress var $cat is available on any category query, and holds the category ID for the current category.

ps. if you are reading this Kaf, using a custom query caused your post_image plugin to stop working. It worked before and the reating plugin still works with the new query. any ideas?

It’s due to Post Image expecting to be run in WordPress’ standard posts loop (i.e. The Loop). You can alter this by commenting or editing out the following lines at the start of the plugin’s post_image() function:

if(!in_the_loop())
return;