Support » Fixing WordPress » Create list of posts showing post meta and title – ordered by meta asc

Viewing 3 replies - 1 through 3 (of 3 total)
  • 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

    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!

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