WordPress.org

Forums

Create list of posts showing post meta and title - ordered by meta asc (4 posts)

  1. alexleonard
    Member
    Posted 7 years ago #

    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:

    http://wordpress.org/support/topic/142787?replies=16

    http://wordpress.org/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:

    http://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.

  2. alexleonard
    Member
    Posted 7 years ago #

    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

  3. alexleonard
    Member
    Posted 7 years ago #

    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!

  4. alexleonard
    Member
    Posted 7 years ago #

    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:
    http://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; ?>

Topic Closed

This topic has been closed to new replies.

About this Topic