The MU forums have moved to WordPress.org

Help with a query syntax (3 posts)

  1. tomleads
    Member
    Posted 13 years ago #

    I'm need to edit a plugin written which selects the latest from a certain category

    $query = "SELECT * FROM " . $wpdb->base_prefix . "site_posts WHERE blog_id > 6 ORDER BY post_published_stamp DESC LIMIT 10";
    				$posts = $wpdb->get_results( $query, ARRAY_A );
    				if (count($posts) > 0){
    					echo '<ul>';
    					foreach ($posts as $post){
    						echo '<li>';
    							echo '<a href="' . $post['post_permalink'] . '">' . get_avatar( $post['post_author'], 16, '' ) . substr( $post['post_title'], 0, 30 ) . '</a>';
    							echo '';
    
    							//echo '<a href="' . $post['post_permalink'] . '">' . substr( $post['post_title'], 0, 30 ) . '</a>';
    						echo '</li>';

    What I need to do is display the date and the author name/link. Been trying loads of different syntax and wordpress tags but it just isn't working for me. Think it is something to do with the $posts.

    Is there a site or a file where I can see what is in the site_posts table?
    Thanks

  2. SteveAtty
    Member
    Posted 13 years ago #

    This is how I did it, this is part of a site search option I wrote:

    $sql = "select post_title,post_content_stripped,post_permalink,post_published_stamp,post_author,blog_id,post_id, wpu.display_name from wp_site_posts ,wp_users wpu where match(post_title,post_content_stripped) against('%$term%' in boolean mode) and wpu.id=post_author order by post_published_gmt desc";
    
    		$res = mysql_query($sql);
    		if (($res) && (mysql_num_rows($res) > 0)) {
                            print "<h2>Search Results for ".$term."</h2><br /><hr>";
    			while ($row = mysql_fetch_array($res)) {
    				format_the_post($row);
    				}} else {
    			echo "<br /> No search results found for $term2".strlen($term2);
    		}
    function format_the_post($row){
    	$link = $row[post_permalink] ;
    	$blogname =  get_blog_option($row[blog_id],'blogname');
    	$blogurl =  get_blog_option($row[blog_id],'siteurl');
    	$title = $row[post_title];
    	$date = date('l jS \of F Y',$row[post_published_stamp]);
    	$thiscontent = $row[post_content_stripped];
    	$thispermalink=get_blog_permalink($row[blog_id],$row[post_id]);
    	preg_match("/([\S]+\s*){0,100}/", $thiscontent, $regs);
    	$trunc_content = explode( ' ', trim($regs[0]) , 6 );
    	$author=$row[display_name];
    	// build the excerpt html block, capitalize first five words
    	$thisexcerpt = ''
    	.strtoupper($trunc_content[0]).' '
    	.strtoupper($trunc_content[1]).' '
    	.strtoupper($trunc_content[2]).' '
    	.strtoupper($trunc_content[3]).' '
    	.strtoupper($trunc_content[4]).' '
    	.$trunc_content[5].'............ '
    	.'<a href="'.$thispermalink.'">'
    	.'&raquo;&raquo; MORE'.'</a>'
    	.'';
    	$thisexcerpt = str_replace(chr(10), "<br />", $thisexcerpt);
    	?>
    	<div class="post" id="post-<?php print $row[post_id]; ?>">
            <h2><a href="<?php print $thispermalink; ?>" rel="bookmark" title="Permanent Link to <?php print $title; ?>"><?php print $title; ?></a></h2>
           <small>Posted <?php print $date; ?> by <?php print $author; ?> </small>
    	<div class="entry">
    	<?php print $thisexcerpt; ?>
    	</div>
            </div>
    	<?php
    };

    A lot of the format_row code was from the recent_posts plugin, I wanted to use similar formatting.

  3. tomleads
    Member
    Posted 13 years ago #

    Cheers Steve. Managed to get the date out of it but can't get it display the name of the Author (and a link to the author).

About this Topic

  • Started 13 years ago by tomleads
  • Latest reply from tomleads