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
awesome, thank a lot!
cool, thanks dude, i was wondering how to get most commented posts
Looking forward to your next articles!
Pingback: Grab data from WordPress database
Yeah! Fantastic! Dejan, thumbs up!
Thanks again. Very useful.
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!
you’re all welcome
Pingback: Wordpress:最简单的显示最新文章的方法 | Lucifr
Thanks Best Regards
Thanks you very much
Great article, it’s always good to have explanations instead of just code snippets to copy/paste!
Pingback: Saturday Link Round-Up 3 | Wireless Forums from AT&T
Pingback: word tutorial
great art. great help!
solved a problem I didn’t even knew how to start with.
thnx
\f
Pingback: Feed Subscriptions, Stats and Management « Feet up, eyes closed, head back
Fantastic info! Thank you! This will be useful for solving a bunch of problems and even creating some new features on my blog!
will be useful for solving a bunch of problems and even creating some new features on my blog!
Pingback: Futile » links for 2009-10-03
Pingback: Stupid WordPress Tricks | Serita
Pingback: Stupid Wordpress tricks | Linux Shtuff
Pingback: WordPress Tricks Last Part | [ XenriTech ].com
idasil basur hapı
Pingback: WordPress code snippets, tips and tricks « Mex714's Blog
Pingback: Plugin – “Inline PHP” Author kukukuan – Project TZ
I’m trying to come up with a way to pull data from a MySql database and have the data appear in an animated sequence on the front – end of my WordPress site
Great article, it’s always good to have explanations instead of just code snippets to copy/paste!
IT was very useful thnks
Great Work indeed !!! but the saddest part is i am a newbie, and i don’t understand where to write this code. I am building a standard website using wordpress, not a blog.I need to display data from a table in mysql database(the same where wordpress is installed). How can i get this done? It would be a great help if you could throw light on this. Thanks in advance.