Page 1 of 1

Wordpress / database / php query question

Posted: Fri Jun 11, 2010 4:40 pm
by designtoday
Hi guys

making a site with wordpress, what im trying to do is query the database so that only one post per author comes up on the home page. That i managed to do, though when i tried to break it down further so i can say have only posts from one category come up my code breaks.

what ive written so far queries the data base looks for a post once its found one user it skips it in the query etc...

the issue i think is to do with the fact that in the database users / posts / authors are in one row/colloum (im new to this so please forgive me :) ) but the categories / tags etc are in another and i cant seem to join them so i can query them together.

this works for pulling one post per author

Code: Select all

<?php


function the_last_post_per_user(){
global $wpdb;
$the_authors = $wpdb->get_results( "
								  SELECT DISTINCT post_author 
								  FROM $wpdb->posts 	
						
								  WHERE 
															   
								  post_status='publish'
								  AND post_type='post' 
								  AND post_author !='1'  
								  ORDER BY post_date DESC
								   
								  LIMIT 0, 5");
return $the_authors;
}


//get all users, iterate through users, query for one post for the user,
//if there is a post then display the post title, author, content info
$blogusers = the_last_post_per_user();
if ($blogusers) {
  foreach ($blogusers as $bloguser) {
    $args = array(
    'author' => $bloguser->post_author,
    'showposts' => 1,
	'caller_get_posts' => 1,
    );
    $my_query = new WP_Query($args);

    if( $my_query->have_posts() ) {
      // $user = get_userdata($bloguser->user_id);
      // echo 'This is one post for author with User ID: ' . $user->ID . ' ' . $user->user_firstname . ' ' . $user->user_lastname;
      while ($my_query->have_posts()) : $my_query->the_post(); ?>
<ul>
	<li>
   				<h2><a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title_attribute(); ?>"><?php the_title(); ?></a></h2>
				<small>
				Source: <a href="<?php the_syndication_source_link(); ?>"><?php the_syndication_source(); ?></a><br />
                <?php the_time('F jS, Y');?> <br />
				<?php the_tags('Location: '); ?><br />
                <?php the_category(); ?>
               </small>
       </li>
</ul>
		
		
		<?php
      endwhile;
    }
  }
}
?>

but when i try something like this in the database query it screws up :(

Code: Select all


function the_last_post_per_user(){
global $wpdb;
$the_authors = $wpdb->get_results( "
								  SELECT DISTINCT post_author 
								  FROM $wpdb->posts 	
								  INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) 
								  INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
								  WHERE 
								  1=1
								  AND post_status='publish'
								  AND wp_term_taxonomy.taxonomy = 'category' 
								  AND wp_term_taxonomy.term_id IN ('4')
								  AND post_type='post' 
								  AND post_author !='1'  
								  ORDER BY post_date  DESC
								   
								  LIMIT 0, 5");
return $the_authors;
}
any ideas ?

Re: Wordpress / database / php query question

Posted: Sat Jun 12, 2010 5:24 am
by designtoday
anyone ?