paracetamol

Hm, I can’t edit the old post anymore.

This one get’s the categories:

SELECT *
FROM $wpdb->wp_categories
WHERE category_parent = $cat

But how do I implement that in the query? A friend helped me clean up the old query; it reads a correct list by ‘year’ now (it did before, but by accident).

SELECT *
FROM $wpdb->posts
JOIN $wpdb->post2cat ON ID = post2cat.post_id
LEFT JOIN $wpdb->postmeta ON ID = postmeta.post_id
WHERE category_id = $cat
AND post_status = 'publish'
AND meta_key='year'
ORDER BY meta_value DESC

Thanks a lot, it worked and looks much better now!

— some problems later .. —

Ok, I realized I can’t skip the Custom Field dropdown as I use the Custom Field GUI for separate meta data and leaving out the regular custom field editor fucks up the whole account+cookie.

But thanks again! I wouldn’t have figured out the global variable thing by myself.

Trying to sort posts via custom field tags, this post helped me a lot: I managed to display and sort posts with a certain key, in this case “Year”, which I allow the user to enter, if necessary.

But there’s still something missing: 1) I can’t get results only for the single active category and 2) can’t sort them out by pages, they 3) appear as one list, leaving out the untagged ones.

My Loop Template (derived from this topic):

<?php $pageposts = $wpdb->get_results("SELECT wposts.*
FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
WHERE wposts.ID = wpostmeta.post_id AND wpostmeta.meta_key = 'Year'
ORDER BY wpostmeta.meta_value DESC", OBJECT); ?>
<?php if ($pageposts) : foreach ($pageposts as $post): ?>
<?php setup_postdata($post); ?>
//The Loop
<?php endforeach;?>
<?php endif; ?>


Any help with the mysteries of SQL queries would be greatly appreciated.

I made it! Don’t know why, but this one’s working perfectly:


<?php $pageposts = $wpdb->get_results("SELECT wposts.* FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta WHERE wposts.ID = wpostmeta.post_id AND wpostmeta.meta_key = 'Main' ORDER BY post_date DESC LIMIT 1", OBJECT); ?>
<?php if ($pageposts) : foreach ($pageposts as $post): ?>
<?php setup_postdata($post); ?>
//Content of the Loop//
<?php endforeach;?>
<?php endif; ?>


The code derives from this post with alternative ORDER parameter.

It displays the single latest post with the custom field “Main” set TRUE on my home.php template.