StylizedWeb is a blog about web trends and tutorials, KnowHow community forum designed and maintained by Dejan Cancarevic who also runs Serbian IT job board at itPoslovi.info

Categories Archive

Popular Posts
Advertisement
My Site is Ready, What Next??? Advertise on Stylized Web Najbolje ponuda IT poslova u Srbiji na itposlovi.info Advertise on Stylized Web
Grab data from WordPress database
add to Save to Delicious Save to Stumble Save to Digg

Previously we have learned some useful WordPress Tricks which gave us new possibilities by turning WordPress into a kind of Content Management System (CMS). Basically it was for beginners level so now we'll do something more advance by learning how to grab various data from WordPress database and then style it etc by using a bit more of pure php.

I'm going to demonstrate this on two examples - display latest comments and latest post by most commented (if you for example need just latest posts check useful WordPress tricks comments

Display latest comments
 
<?php
global $wpdb;
 
	$sql = "SELECT DISTINCT ID, post_title, post_password, comment_ID,
	comment_post_ID, comment_author, comment_date_gmt, comment_approved,
	comment_type,comment_author_url,
	SUBSTRING(comment_content,1,50) AS com_excerpt
	FROM $wpdb->comments
	LEFT OUTER JOIN $wpdb->posts ON ($wpdb->comments.comment_post_ID =
	$wpdb->posts.ID)
	WHERE comment_approved = '1' AND comment_type = '' AND
	post_password = ''
	ORDER BY comment_date_gmt DESC LIMIT 5";
 
	$comments = $wpdb->get_results($sql);
	$output = $pre_HTML;
	$output .= "\n
<ul>";
 
	foreach ($comments as $comment) {
	$output .= "\n
<li>"."<a href=\"" . get_permalink($comment->ID) .
	"#comment-" . $comment->comment_ID . "\" title=\"on " .
	$comment->post_title . "\">" .strip_tags($comment->comment_author)
	.":<br/>
<div>" . strip_tags($comment->com_excerpt)
	."</div>
 
</a></li>
 
";
	}
 
	$output .= "\n</ul>
 
";
	$output .= $post_HTML;
 
echo $output; ?>
 

So, we have puled out some specific fields from WordPress database such as ID, title etc from approved comments and excluded trackbacks and pingbacks. And then printed it unordered list by using simple foreach loop. For this example i have used 5 latest comments (change limit for more/less) with link, excerpt (on 50 chars) and author name. Of course you will style it the way you need it and for example add author link etc.

Latest most commented posts
 
 <?php
$result = $wpdb->get_results("SELECT comment_count,ID,post_title FROM $wpdb->posts ORDER BY comment_count DESC LIMIT 0 , 10");
 
                  foreach ($result as $topten) {
                  $postid = $topten->ID;
                  $title = $topten->post_title;
                  $commentcount = $topten->comment_count;
                  if ($commentcount != 0) {
 
?>
<li><a href="<?php echo get_permalink($postid); ?>"><?php echo $title ?></a></li>
 
<?php } } ?>
 

Now we are using get_results(); but the way stays the same, we have selected fields from database and printed them in loop if there are any comments. You can change order for example by changing "DESC" and the number of them by changing the "LIMIT"

What do you think?





13 Responses so far

By jbj
on June 27, 2008

Great article, it’s always good to have explanations instead of just code snippets to copy/paste!

By mirc
on June 26, 2008

Thanks you very much

By mirc
on June 26, 2008

Thanks Best Regards

By Dejan Cancarevic
on June 20, 2008

you’re all welcome ;)

By Prof Kienstra
on June 20, 2008

Very interesting. This is definitely something i’m going to try out in my new / up and coming webdesign. For now I’ve used plugins to do this, but if i can built in right in, that would be perfect! Thanks for sharing!

By Max
on June 20, 2008

Thanks again. Very useful.

By Aldo
on June 20, 2008

Yeah! Fantastic! Dejan, thumbs up!

By Chad
on June 20, 2008

cool, thanks dude, i was wondering how to get most commented posts
Looking forward to your next articles!

By Anna
on June 20, 2008

awesome, thank a lot!

  1. Jul 7, 2008: word tutorial
  2. Jun 30, 2008: Saturday Link Round-Up 3 | Wireless Forums from AT&T
  3. Jun 21, 2008: Wordpress:最简单的显示最新文章的方法 | Lucifr
  4. Jun 20, 2008: Grab data from WordPress database