• Skip to primary navigation
  • Skip to content
  • Skip to primary sidebar
  • Skip to footer
  • Skip to footer navigation
  • Services
  • Our Themes
  • Start a WordPress Blog
  • WordPress Hosting
WPArena

WPArena

WPArena covers all things related to WordPress: Themes, Plugins, Tutorials, Guides, Deals and Beginner tips.

  • News
    • Opinion
  • Tutorials
  • Reviews
    • Themes
    • Plugins
  • Comparisons
  • Collections
    • Education Themes
    • Genesis Child Themes
    • Best Responsive Themes
    • Medical WordPress Themes
    • Finance & Business Themes
    • Crowdfunding Themes
  • Resources
    • Inspiration
  • Deals
WPARENA » Reviews » Plugins

Most Important SQL queries for WordPress Beginners

TwitterFacebookWhatsAppGoogle+BufferLinkedInPin It

Before taking any step make a backup of your current WordPress database. How to make a backup of your WP database? You can use different WordPress Plugins like Smart Backup and WP-DB_Backup. Alternatively, you can use phpMyAdmin.
Smart Backup is a complete WordPress solution for database backup and restores operations. You can create backups of your complete WordPress installation, files only, or database only. Backups can be restored with one click.

Where to run WordPress SQL Queries

Hope you know where to run SQL Queries, If not follow these steps: Login to phpMyAdmin panel and select your WordPress database. After selecting your database, Click on the SQL tab which will bring you to a page with a SQL query box.

run sql query

There are some plugins and script that will increase you SQL experience:

SQL Executionner plugin provides an easy-to-use interface that allows you to run SQL queries directly on your WordPress blog dashboard.

Super MySQL Terminal: Super MySQL Terminal is a PHP MySQL shell emulator. You can work with your share-hosted remote MySQL like your localhost.

SQL Comparer – Synchronize your database versions: Compare and synchronize two SQL database schemas and see the difference between them. A beneficial tool when dealing with different versions of a database.

SQL Document Generator: SQL Document Generator is a set of classes for creating documents in various formats based on SQL queries.

Enhanced SQL Web Console for MySQL: This script helps you helps you fire queries from the database and export the resultset with a browser on the fly.

Features

1). Drag-drop to use, no setup needed.
2). Smart bookmark feature, you can bookmark your favorite scripts and pickup them to use anytime.
3). Export the query resultset to CSV , Excel, XML , HTML formats.
4). Read-only mode avoiding writable scripts executing.
and more…

MySQL Database Navigation and Pagination: If you are working with hosting MySQL databases, and pick up data from them frequently, this script may make your life easy working with them.

CreativeTable: CreativeTable it’s a PHP class that builds a table based on the data retrieved by an SQL query or a 2D Array. It has lots of options, and it’s very, very configurable.

LogPress: This plugin will help you to debug and speedup development of WordPress plugins and themes. Besides logging PHP error it can log a bunch of other things to help you make your website better.

Most Important WordPress SQL Queries:

How to: Manually reset your WordPress password

What to do if you lost your WordPress password? The easier is to use PhpMyAdmin and execute a simple SQL query to update it. Here’s how to proceed.

UPDATE 'wp_users' SET 'user_pass' = MD5('PASSWORD') WHERE 'user_login' ='admin' LIMIT 1;

Change author attribution on all posts at once

Some bloggers makes the mistake to write their first posts under the name “admin”, until they realize that it’s absolutely not personal. But then, modifying author attribution on each post takes a lot of time. Happilly, here’s a recipe to batch modify author attribution.

UPDATE wp_posts SET post_author=NEW_AUTHOR_ID WHERE post_author=OLD_AUTHOR_ID;

Delete post revisions and meta associated to those revisions

Post revisions are very useful, especially in the case of a multi author blog. However, the problem of post revisions is definitely the number of database records it creates.

define('WP_POST_REVISIONS', false);

You can also delete all post revisions by running this query in phpMyAdmin:

DELETE a,b,c
FROM wp_posts a
LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
WHERE a.post_type = 'revision'

Batch delete spam comments

Best way install Akismet! if you want delete more than 6000 spam comments by using this command of line.

DELETE from wp_comments WHERE comment_approved = '0';

Identify Unused Tags

In a WordPress database, if you run a query to delete old posts manually from MySQL, the old tags will remain and appear in your tag cloud/listing. This query allows you to identify the unused tags.

SELECT * From wp_terms wt
INNER JOIN wp_term_taxonomy wtt ON wt.term_id=wtt.term_id WHERE wtt.taxonomy='post_tag' AND wtt.count=0;

Find and Replace Data

This tip isn’t specific to WordPress and is a must know for anyone who’s working with MySQL databases.

UPDATE table_name SET field_name = replace( field_name, 'string_to_find', 'string_to_replace' ) ;

Get a list of your commentators emails

Over a period of time, your blog will have received many comments. These comments will include the email addresses left by the commenter. You can retrieve all these emails for your mailing list without any duplicate.

SELECT DISTINCT comment_author_email FROM wp_comments;

Disable all your plugins at once

Sometimes, for exemple when you have to upgrade your blog, you need to disable all your plugins. Depending to how much plugins you’re using, it can takes a lot of time and be kinda boring.

UPDATE wp_options SET option_value = '' WHERE option_name = 'active_plugins';

SQL Query to delete all tags

This piece of code will remove all tags and relationships between tags and posts, while leaving categories and taxonomies intact.

DELETE a,b,c
FROM
    database.prefix_terms AS a
    LEFT JOIN database.prefix_term_taxonomy AS c ON a.term_id = c.term_id
    LEFT JOIN database.prefix_term_relationships AS b ON b.term_taxonomy_id = c.term_taxonomy_id
WHERE (
    c.taxonomy = 'post_tag' AND
    c.count = 0
    );

List unused post meta

The following query will show you all the records in the postmeta table that doesn’t have corresponding records in the post table.

SELECT * FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;

Disable comments on older posts

This code will automatically close comments on posts which are too old. This SQL query will close comments on all posts published before January 1, 2009.

UPDATE wp_posts SET comment_status = 'closed' WHERE post_date < '2009-01-01' AND post_status = 'publish';

Replace commentator url

This SQL query easily replace the old url by your new site url. Simply run this query and you’ll be done!

UPDATE wp_comments SET comment_author_url = REPLACE( comment_author_url, 'http://oldurl.com', 'http://newurl.com' );

Replace commentator email address

This query will replace the email address provided in the comments field, by a new one.

UPDATE wp_comments SET comment_author_email = REPLACE( comment_author_email, 'old-email@address.com', 'new-email@address.com' );

Delete all comments with a specific url

The following query will delete all comments with a specific url. The “%” signs means that any url containing the string within the % signs will be deleted.

DELETE from wp_comments WHERE comment_author_url LIKE "%sitetobeDel%" ;

Database Queries, SQL query

You Might Also Like These Posts:

  • 10 Things to look for when Selecting a WordPress Theme
  • 4 Tips for Opening the Lines of Communication in the Workplace
  • Design Tips for Building Better Websites
  • Theme Design Complete: Where Do I Promote My WordPress Theme
  • Automatically Post Straight To Facebook After Publishing A Post
Noor Mustafa Raza

Noor Mustafa Raza

I am a WordPress Developer and Designer, author @WPArena. I am providing Free WordPress consultation and can help you to install WordPress in a secure way to small businesses and bloggers.

Previous Post How To Add Multiple Product Categories To WP eCommerce Plugin
Next Post Best Twitter Tips and Tutorials for WebSites Marketing

Reader Interactions

Leave a Reply Cancel reply

Thank you for choosing to leave a comment on WPArena. Please bear in mind that all comments are moderated according to our comment policy. Your email address will never be published but is required. Do NOT use keywords in the name field. First name is fine with us. Lets have a meaningful conversation and respect others opinions.

Primary Sidebar

Over 40,000+ Readers

Get fresh content from WPArena

Recent Posts

  • What Marketing Do Customers Take Notice Of?
  • Ad-Maven Review: One of the best monetization tools out there
  • Four Best WordPress Plugins for Content Marketers
  • CometNine Review – A lesser known web host with great features
  • 7 Must-Have Google Chrome Extensions For Bloggers

Our Services

WordPress
Installation
WordPress
Performance
WordPress
Security
WordPress
SEO
WordPress
Errors
Building an
Online Store

Footer

About

WPArena is a premium online resource site of WordPress and is focused on providing excellent WordPress Tutorials, Guides, Tips, and Collections. The site design is updated at regular intervals with more features. Look here.
You can read more about WordPress Arena here.


WPArena Marketing Material

Resources

  • Best WordPress Plugins
  • WordPress Permalinks Structure
  • Email Management System
  • Envato Free Files

Reviews

  • Beaver Builder Review
  • Beaver Themer Review
  • WP User Frontend Pro
  • Ninja Forms Review
  • MemberPress Review

More Reviews »

Deals

  • Clickright Theme 30% OFF
  • Hostgator Coupon
  • ionMag Theme 30% OFF
  • OIO Publisher 20% OFF

More Deals »

© 2019 · WordPress Arena · All Rights Reserved.
We are not affiliated with Automattic or WordPress.

  • About
  • Advertise
  • Write for us
  • Disclosure
  • Terms
  • Privacy
  • Contact