StylizedWeb

Subscribe to our updates:

A Design, WordPress and Tutorials Blog.

Dedicated to helping you learn the art and science of the web.

Grab data from WordPress database

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
  1.  
  2. <?php
  3. global $wpdb;
  4.  
  5. $sql = "SELECT DISTINCT ID, post_title, post_password, comment_ID,
  6. comment_post_ID, comment_author, comment_date_gmt, comment_approved,
  7. comment_type,comment_author_url,
  8. SUBSTRING(comment_content,1,50) AS com_excerpt
  9. FROM $wpdb->comments
  10. LEFT OUTER JOIN $wpdb->posts ON ($wpdb->comments.comment_post_ID =
  11. $wpdb->posts.ID)
  12. WHERE comment_approved = '1' AND comment_type = '' AND
  13. post_password = ''
  14. ORDER BY comment_date_gmt DESC LIMIT 5";
  15.  
  16. $comments = $wpdb->get_results($sql);
  17. $output = $pre_HTML;
  18. $output .= "\n<ul>";
  19.  
  20. foreach ($comments as $comment) {
  21. $output .= "\n<li>"."<a href=\"" . get_permalink($comment->ID) .
  22. "#comment-" . $comment->comment_ID . "\" title=\"on " .
  23. $comment->post_title . "\">" .strip_tags($comment->comment_author)
  24. .":<br/><div>" . strip_tags($comment->com_excerpt)
  25. ."</div></a></li>";
  26. }
  27.  
  28. $output .= "\n</ul>";
  29. $output .= $post_HTML;
  30.  
  31. echo $output; ?>
  32.  

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
  1.  
  2. <?php
  3. $result = $wpdb->get_results("SELECT comment_count,ID,post_title FROM $wpdb->posts ORDER BY comment_count DESC LIMIT 0 , 10");
  4.  
  5. foreach ($result as $topten) {
  6. $postid = $topten->ID;
  7. $title = $topten->post_title;
  8. $commentcount = $topten->comment_count;
  9. if ($commentcount != 0) {
  10.  
  11. ?>
  12.  
  13. <li><a href="<?php echo get_permalink($postid); ?>"><?php echo $title ?></a></li>
  14. <?php } } ?>
  15.  

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"

Leave a comment on Stylized Web Have some feedback? Leave a comment

36 Comments So Far

  1. By Anna posted on June 20, 2008 at 5:36 pm

    awesome, thank a lot!

  2. By Chad posted on June 20, 2008 at 5:37 pm

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

  3. Pingback: Grab data from WordPress database

  4. Yeah! Fantastic! Dejan, thumbs up!

  5. By Max posted on June 20, 2008 at 8:51 pm

    Thanks again. Very useful.

  6. 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!

  7. By Dejan Cancarevic posted on June 20, 2008 at 9:43 pm

    you’re all welcome ;)

  8. Pingback: Wordpress:最简单的显示最新文章的方法 | Lucifr

  9. Thanks Best Regards

  10. Thanks you very much

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

  12. Pingback: Saturday Link Round-Up 3 | Wireless Forums from AT&T

  13. Pingback: word tutorial

  14. great art. great help!
    solved a problem I didn’t even knew how to start with.

    thnx

    \f

  15. Pingback: Feed Subscriptions, Stats and Management « Feet up, eyes closed, head back

  16. Fantastic info! Thank you! This will be useful for solving a bunch of problems and even creating some new features on my blog!

  17. will be useful for solving a bunch of problems and even creating some new features on my blog!

  18. Pingback: Futile » links for 2009-10-03

  19. Pingback: Stupid WordPress Tricks | Serita

  20. Pingback: Stupid Wordpress tricks | Linux Shtuff

  21. Pingback: WordPress Tricks Last Part | [ XenriTech ].com

  22. idasil basur hapı

  23. Pingback: WordPress code snippets, tips and tricks « Mex714's Blog

  24. Pingback: Plugin – “Inline PHP” Author kukukuan – Project TZ

  25. 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

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

  27. By Deepen posted on December 15, 2011 at 1:32 pm

    IT was very useful thnks

  28. By Akhilesh posted on December 16, 2011 at 12:15 pm

    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.

  29. By ramya posted on April 5, 2012 at 2:11 pm

    can you tell me where to place this code, i created a page thru admin, provided link in front end, using exec-php plugin to execute the code. but nothing is getting displayed.
    kindly reply back, very urgent

  30. Nice job of you!!! Although I have little chance to use this functions!!! But tnks!!!

  31. Looking to pull,display, and select-activate, data from non wordpress database.
    Building menu system where menu items are derived from table entries. Needs to be 5 layers deep or more. When final, gives choice of data set as normal WP page.

  32. Pingback: WordPress Tricks,,cuongvu marketing tips | cuongvu marketing tips

  33. Plz any one help me
    how to build select box that contains list to be fetched from database table

  34. Thanks, it helps me very much.

  35. ceraskallar

  36. matbaa, kartvizit, cepli dosya, sunum dosyası, katalog

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>