Ads Top

5 Useful WordPress Database Queries for Important Tasks



WordPress Database Queries

One of the many reasons why we like WordPress is the power and flexibility that the publishing platform provides. It offers so many customizations, and you can do so much more with it. One of the best features about it is, you can use and interact with WordPress Databases to add your own functionality, and extract some extra data that you can't normally do without installing some third party plugins. For those of our readers who're WordPress users, we'd like to share some simple (and some advanced) MySQL database queries you can run to do some advanced and useful actions.


How to run MySQL queries?



Most web hosting providers will give you access to your cPanel, where you can access your web files, mail, domains, ftp accounts, and so on. Among the many tools, you will see phpMyAdmin, a tool used to access MySQL databases. Open phpMyAdmin, and go to the Databases tab, where you'll see all the databases present on your server. Your WordPress database could be the one named after your domain name, or it might have a 'wp' or 'wrdp' prefix/suffix. Click on the database to see individual tables.





Now, click on the SQL tab. You can run your database queries from this interface. Just copy/paste a query, and hit Go, and you'll see the results right in front of you!




Running SQL Queries in phpMyAdmin



Useful WordPress Database Queries



1. Extracting Emails left by commentators





The WordPress comments data is stored in a table named 'wp_comments'. With each new comment made on a post on your blog, a new entry is added into the table. You can run a simple database query to extract the emails left by commentators.






SELECT DISTINCT comment_author_email


FROM wp_comments;






The DISTINCT is there to remove duplicates. And instead of extracting author email, you can also extract 'comment_author_url', 'comment_author_ip', etc.





You can use such a query to, for example, send a thank you email to all these addresses for contributing to the discussion on your blog. Of course, most of them might be fake, but still, worth a shot, eh?





2. Extract all posts by an author





Each author on your site has a specific ID. You can find this ID by opening a user's profile, and observing the URL. Once you know the ID, you can use that to run a database query to extract all posts by that author.








SELECT ID, post_title




FROM wp_posts




WHERE post_status = 'publish'




AND post_author = 11;







This query fetches the post id, and the post title of all posts. Additional values you can extract are 'post_date', 'post_content', 'guid' etc.





3. Delete Unused Tags





You might accumulate a bunch of empty tags you never used over time. You can do a clean up by looking for unused tags using the following query.








SELECT name, slug




FROM wp_termsWHERE term_id




IN (




SELECT term_id



FROM wp_term_taxonomy


WHERE taxonomy='post_tag'


AND count='0'


);









4. Searching for content







You can use queries to search your posts for a specific keyword, or HTML tag! All you have to do is, modify the bold text given in the query below.








SELECT ID, POST_TITLE




FROM wp_posts




WHERE post_content LIKE '%your_search_term_or_tag%'




AND post_status = 'publish';







You can also run a search inside your comments by using wp_comments instead of wp_posts.








5. Searching posts by date







You can modify the date range given below to anything you want, and search for posts between that date range. For example, the following query searches for all posts written in the month of July.





SELECT ID, POST_TITLE




FROM 'wp_posts'




WHERE 'post_type' = 'post'




AND 'post_date' > '2013-06-30 23:59:00'



AND 'post_date'< '2013-08-01 00:01:00';



Make sure that you get the date syntax right. You can also combine this query with #2 to get all posts by a certain author in the month of July, for example.





These were some of the very basic queries. Did you like them? Please leave your responses in the comments section below. And if you want, we could show you some of the more advanced queries that can you a whole new level of power over your blog! So, what do you say?

Categories

adsense adsense optimization adsense tips Animation Announcement Apps Background backlinks Basic blog Blog Design blogger Blogger Design Blogger Hack blogger pages blogger posts Blogger Security Blogger Special Tricks Blogger Templates Blogger Tips Blogger Tips & Tricks Blogger tools blogger tricks Blogger Widgets Blogger Widgets and plugins Blogging Basic Tips Blogging ethics Blogging tips Blogging Tools Blogging Tricks Btemplates 2013 Btemplates 2014 Bugs and Errors click bombing Comments css CSS and HTMLTricks CSS3 Cursors custom domain Design Drop Down Menu Earning Tips Editing emoticons Events Facebook Facebook tricks Featured Content Slider firebug Flash Free Coupons Free Recharge Gadget Generator Google Google AdSense Google Analytics Google Plus Google Plus Tricks Guest Posts Horizontal Menu How To HowTo Tuts HTML Image Image Effects Increase traffic instagram Interesting invalid clicks Java Script javascript jQuery Label labels Link Building Links LOGO Makers Make Money Make Money Online Marketing Menu bar menus Menus for Blogger Multi Tabbed Navigation navigation menu News Numbered Page Optimizing Other Tricks Page Photoshop Pop Out popular posts Post posts presentations Pure CSS Menu recent comments recent posts related posts Responsive Blogger Templates robots Rss Search box SEO SEO Strategy SEO Tips & Tricks sitemap Slide Show slideshows Social Media social media plugins statcounter static pages Style your Blog swf Tab Menu Template Template Designing Template Tricks Templates templates 2016 threaded comments Tips tools Traffic Tips Twitter Unlock modems Vertical bar Video Wallpapers Web Designing web hosting Webmaster Tools Widget Widget Generators Widgets wordpress WordPress Plugins WordPress Themes WordPress Tricks WP Tips
Powered by Blogger.