Grab data from WordPress database

add to

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"

Have some feedback? Leave a comment

What Now?

Digg this
Save to Del.ico.us
Stumble This
Subscribe
Leave a Comment

Further Reading

• Wordpress CMS Plugins – Two New Ones to Add to Your Arsenel
• 5 Steps to Make Wordpress an SEO Beast
• Gifts to Give Your Freelance Career in 2010
• Wireframes Are A Web Strategy Tool, Not An Aesthetics Tools
• Why Social Media Succeeds or Fails

More?

Posted on 20. 06. 2008
Filed as Wordpress
By Dejan Cancarevic