Create list of posts showing post meta and title – ordered by meta asc

  • Hi there.

    I’ve been trying to work out how to do this to no avail mainly as I am having trouble understanding the following two support posts:

    https://www.ads-software.com/support/topic/142787?replies=16

    https://www.ads-software.com/support/topic/96851?replies=5

    I think I am a bit out of my depth with the php and sql query end of this.

    I have a feeling that I am attempting to do something very similar to the first support link above.

    I am working on a website for a non-profit organisation in Ireland and they have a resource library of downloads relating to preventing gender based violence.

    You can see a sample of it here:

    https://www.gbv.ie/category/resource-library/make-prevention-and-protection-central-to-programming/

    As you can see, I have a list of all posts in each category. They have requested that these posts are ordered by Organisation/Author, then year, then title.

    So I am using post_meta to get the necessary information associated with each post

    <ul>
<?php while (have_posts()) : the_post(); ?>
  <li><?php $key="authors";	echo get_post_meta($post->ID, $key, true);?> (<?php the_time('Y');?>) <a href="<?php the_permalink() ?>" rel="bookmark" title="Link to <?php the_title(); ?>"><?php the_title(); ?></a></li>
<?php endwhile; ?>
</ul>

    This gives a display of Author (Year) Title

    However, using:

    $posts = query_posts($query_string . '&orderby=title&order=asc&posts_per_page=-1');

    to order the posts will obviously order by the post titles.

    So my basic understanding of the previously mentioned support topics is that I need to do some custom queries to $wpdb and join the necessary meta_key to the post title, thereby allowing my “&orderby=title” to work as planned?

    I think I’m probably waaaay off here, but this is my guess as to what I should be looking to do!

    <ul>
<?php if (in_category('7')) {
$pageposts = $wpdb->get_results("SELECT wp_posts.* FROM wp_posts INNER JOIN wp_postmeta author ON (wp_posts.meta_key = 'author') INNER JOIN wp_postmeta year ON (wp_posts.meta_key = 'year') ORDER BY author.meta_value ASC;
if ($pageposts) : foreach ($pageposts as $post): setup_postdata($post); ?>
<li><?php $key="authors";	echo get_post_meta($post->ID, $key, true);?> (<?php the_time('Y');?>) <a href="<?php the_permalink() ?>" rel="bookmark" title="Link to <?php the_title(); ?>"><?php the_title(); ?></a></li>
<?php endforeach;?>
<?php endif; } ?>
</ul>

    Additional Information:

    Category Layout:
    Resource Library
    – sub categories
    — some sub sub categories

    All posts are in_category(‘7’) which is the resource library, as well as whichever sub, or sub-sub category is necessary.

    This allows me to use one file “category.php” which has an:

    if (in_category('7')) {//use special code } else {//use standard category display};

    So yeah. I’m hugely confused, and, well, they’re having a charity dinner thing on Friday and the site needs to be sorted by then so I’m starting to stress out! Any help would be hugely appreciated.

Viewing 3 replies - 1 through 3 (of 3 total)
  • Thread Starter alexleonard

    (@alexleonard)

    It’s just occurred to me that to hopefully simplify things I don’t need to use meta_key for the date, as obviously I’ll use the standard the_date(); function

    Thread Starter alexleonard

    (@alexleonard)

    Ok. Well I’ve been playing around with this some more. Well, a friend of mine has been throwing out some suggestions, but it’s not working out right yet.

    He suggested the following code:

    <?php
$querystr = "SELECT * FROM wp_posts P JOIN wp_postmeta M ON P.ID=M.post_id ORDER BY M.meta_value ASC, P.post_date DESC, P.post_title ASC";
$pageposts = $wpdb->get_results($querystr, OBJECT);
?>

<?php if ($pageposts): ?>
 <h1 class="pagetitle"><?php echo single_cat_title(); ?></h1>
 <div class="cat-desc"><?php echo category_description(); ?></div>
 <h3>Resource Downloads</h3>
 <ul>

 <?php foreach ($pageposts as $post): ?>
  <li><?php $key="authors"; echo get_post_meta($post->ID, $key, true);?> - <?php the_time('Y');?> - <a href="<?php the_permalink() ?>" rel="bookmark" title="Link to <?php the_title(); ?>"><?php the_title(); ?> - <?php the_time('Y');?></a></li>

 <?php endforeach; ?>
 </ul>

<?php else : ?>
 // show search box etc
<?php endif; ?>

    This seems to pretty much grab everything that has been posted or listed on the site. So I’m still baffled.

    Oh, and in the previous comment I said the_date function, and of course I meant “the_time” – I was on the phone at the time and my head wasn’t focussed!

    Thread Starter alexleonard

    (@alexleonard)

    So I got a PHP wizard friend of mine to help me out and he dug around in the WordPress codex and worked out a solution for me (as I was getting nowhere fast). I thought I’d post up the workings here in case anyone else was trying the same thing and also to look for feedback in case there is a faster method of doing this.

    Any feedback much appreciated, and shouts out to Ian for helping me out of a hole.

    Oh you can see the results of this usage at:
    https://www.gbv.ie/category/resource-library/support-gbv-programming/

    <?php
$category = substr($_SERVER['REQUEST_URI'],27);

if (! empty($category)) {
	$catArray = explode("/", $category);
	$catA = array_pop($catArray);
	$catStr = end($catArray);
}
else {
	$catStr = "resource-library";
}

$querystr = "
SELECT * FROM wp_term_relationships WTR
JOIN wp_term_taxonomy WTT ON WTT.term_taxonomy_id=WTR.term_taxonomy_id
JOIN wp_terms WT ON WT.term_id=WTT.term_id
JOIN wp_posts WP ON WP.ID=WTR.object_id
INNER JOIN wp_postmeta WPM ON WPM.post_id=WP.ID
WHERE WT.slug=\"$catStr\" AND WTT.taxonomy=\"category\" AND WPM.meta_key=\"organisation\"
ORDER BY WPM.meta_value ASC, WP.post_date DESC, WP.post_title ASC";

$pageposts = $wpdb->get_results($querystr, OBJECT);
?>
<div class="cat-cont">
<?php if ($pageposts): ?>
 <h1 class="pagetitle"><?php echo single_cat_title(); ?></h1>
<div class="cat-desc"><?php echo category_description(); ?></div><!-- end .cat-desc -->
 <h3>Resource Downloads</h3>
 <ul class="res-list">

<?php foreach ($pageposts as $post): ?>
  <li>
  <?php $organisation = get_post_meta($post->ID, "organisation", true); if (! empty($organisation)) { echo $organisation; } ?> (<?php the_time('Y');?>)<br /><a href="<?php the_permalink() ?>" rel="bookmark" title="Link to <?php the_title(); ?>"><?php the_title(); ?></a></li>
<?php endforeach; ?>
 </ul>
<?php else : ?>
<!-- page not found stuff here -->
<?php endif; ?>
Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘Create list of posts showing post meta and title – ordered by meta asc’ is closed to new replies.