KNOWNHOST KNOWLEDGE BASE

Hosting Question? Find the Solution - Browse our Guides, Articles, and How-To's

WordPress Frequently Asked Questions

Category: WordPress
Tags: # # # # #

Table of Contents

What’s the #1 Rule About Working with the WordPress Database / Tables?

The most important WordPress rule is to backup the database before making changes!

What’s the #2 Rule About Working with the WordPress Database / Tables?

Make sure you work on the table you’re concerned with as well as the meta tables that contain important linked information.

Examples of linked tables include:

  wp_comments -> wp_commentmeta
  wp_users -> wp_usermeta
  wp_psots -> wp_postmeta

You should remove entries in both the table and metadata table that relates to it, whenever removing records in the table.

For example, rather than just removing rows in wp_comments, to avoid leaving orphaned rows that make the database larger and slower than necessary, you would remove rows in wp_commentmeta as well as wp_comments.

What is PHPMyAdmin?

It’s free software that enables you to administer SQL databases over the web. Databases are typically MySQL or MariaDB. Functions include everything from changing permissions to modifying/adding/removing databases, tables, rows within tables, relationships between tables, and other common admin functions.

How to Connect to the WordPress Database?

Look in the file wp-config.php. That file has all the connection information.

What’s my WordPress database user and password?

See the section on wp-config.php. That file, in the main folder of your WordPress install, contains database name and database server, username and password.

What’s My Database Prefix?

The default prefix is wp_ and is set via wp-config.php in the $table_prefix line.

  $table_prefix = 'wp_';

Is it Okay if My Database Prefix Isn’t wp_?

Yes, you can set a different, more secure, default database prefix in wp-config.php.

How to Create a Working wp-config.php?

Rather than hand editing your config file, the good folks at GenerateWP have created a tool to do the heavy lifting. Click on each tabbed section, fill in the blanks, click update code and you’ll get a complete wp-config.php tailored to your specifications.

wp-config.php Generator

What Languages are Used by WordPress?

WordPress files rely on PHP to execute, along with JavaScript and CSS for scripting actions and styling the presentation in web browsers. SQL is used to communicate with the database.

How to Backup Entire WordPress Database Using PHPMyAdmin?

Please remember that backing up your database doesn’t include file backups such as WordPress, themes, plugins, images and other media. You’ll need a full backup, not just a database backup, in order to preserve all these things.

To backup your database:

Login to your web hosting control panel (cPanel or whatever panel you use).

» Open PHPMyAdmin » Select the WordPress database » Click: Export » Choose SQL format » Click: Go

The download prompt should appear in your web browser before saving to your local machine.

How to Backup or Export WordPress Comments Using PHPMyAdmin?

While we’d recommend backing up the entire site, there can be occasions when you’d just like to get all the comments out of the database, making them portable, perhaps as CSV or tab delimited text.

FYI, all comments in WordPress are stored in the wp_comments table of the WordPress database.

Here’s the quick guide to exporting WordPress comments using PHPMyAdmin:

  • Login to your web hosting control panel (cPanel or whatever panel you use).
  • Open PHPMyAdmin
  • Scroll through the list of tables, then select wp_comments and wp_commentmeta
  • Click: Export
  • Choose whatever output format you’d prefer
  • Click: Save as file
  • Click: Go

The download prompt should appear in your web browser before saving to your local machine in the format specified.

How to Export WordPress Comments as CSV (or XML) Using a Plugin?

WordPress comments can be exported via phpMyAdmin by simply selecting the comments table and exporting into the format of your choice. However, there can be times when you either don’t have access to control panel tools, such as phpMyAdmin, or when you’d like to have this capability via the WordPress admin area, such as when tasks have been delegated to other people and cPanel access isn’t going to be acceptable.

In this case, a plugin may be the answer you’re seeking.

Note: This, and other similar plugins will be able to perform comment updates only after upgrading to premium features (paid software). Sometimes this is necessary if you want to get the functionality for which you’re seeking.

If you’d like to export comments for free, refer to our guidance on how to export using phpMyAdmin – it won’t cost you anything!

Continue the steps below, keeping in mind you will need the pro version of this plugin [we don’t receive compensation for recommending such]…

Install the WP All Export plugin using our How to Install Plugins procedure: Export any WordPress data to XML/CSV

Once you’ve installed and activated the plugin, navigate to:

  WP Admin -> All Export

Then click on the Specific Post Type button and in the dropdown select Comments.

What is the WordPress Table wp_commentmeta?

Having the foresight to know software developers might want to extend WordPress comments in hundreds of different directions, the WordPress database has had an additional table added that goes along with wp_comments table.

In the interest of performance and extensibility, WordPress comments functionality can expand, without needing to add columns to the wp_comments table for each and every addition. Keeping the core wp_comments as is, then adding meta information via the wp_commentmeta table is a tactic similar to other sections of the database.

If you’re wondering what might go into wp_commentmeta…. Perhaps you’d like to install a plugin that allows voting on each comment, a bit like Reddit or Stack Overflow. Keeping the voting details in wp_commentmeta keeps wp_comments lean and meta information all in one separate table.

Note: If you’ve used custom table name prefixes, your WordPress database tables might not start with wp_. In that case, you’d still be looking for commentmeta, just with a different prefix at the beginning.

How to Delete All WordPress Comments with phpMyAdmin?

Before making major changes such as removing all your blog comments, make a database backup!

Deleting all comments via phpMyAdmin is a quick way of getting rid of a massive spam accumulation, particularly when you haven’t got any quality comments to worry about retaining.

WordPress comments are stored in the database table wp_comments (replacing wp_ with whatever alternative prefix you may have used at installation time, if you used an alternative prefix). Typically, a large amount of metadata is stored in another table wp_commentmeta. This means that clearing comments will require cleaning up both wp_comments and wp_commentmeta tables.

Deleting all can be accomplished by opening your WordPress database in phpMyAdmin, selecting the tick boxes next to wp_comments and wp_commentmeta, then selecting the dropdown under the table list “With selected” and choosing Empty from the list of choices.

You’ll be prompted to TRUNCATE both tables, with phpMyAdmin making sure you really, really want to do that. Pick yes, and your comments will disappear.

How to Delete Spam or Trash WordPress Comments with phpMyAdmin?

Before making major changes such as removing all your blog comments, make a database backup!

WordPress comments are stored in the database table wp_comments (replacing wp_ with whatever alternative prefix you may have used at installation time, if you used an alternative prefix). Typically, a large amount of metadata is stored in another table wp_commentmeta. This means that clearing comments will require cleaning up both wp_comments and wp_commentmeta tables.

Comments with a status of “spam” can be identified in the wp_comments table with a value of “comment_approved = ‘spam’”. Trash comments will be “comment_approved = ‘trash’”.

Deleting either can be accomplished by opening your WordPress database in phpMyAdmin and clicking the SQL tab.

Spam

You can delete all comments flagged as spam by pasting this into the SQL text box and then clicking go:

  DELETE from wp_comments WHERE comment_approved = 'spam';

Trash

To delete all comments flagged as trash, paste this into the SQL text box and then click go:

  DELETE from wp_comments WHERE comment_approved = 'trash';

How to Trash or Delete All Comments from One Post?

Make a backup of your database before executing queries directly.

If you’d like to flag all the comments on a particular post as being trash, you’ll need the post ID. You can either browse the WordPress database, which could take some time if you’ve got a number of posts, or you can find it via WP admin.

To find the post ID, click on Posts then find the post you want to work on and click Edit.

Now, look at the URL bar. It will have a number of bits of information that are URL encoded, but you should easily be able to spot the “post=XXX” where XXX is the post ID.

If the post ID was 123, you’d be able to use phpMyAdmin SQL tool to execute:

  UPDATE wp_comments SET comment_approved='trash' WHERE comment_post_ID=123;

The result would be all comments associated with that post would be marked as trash.

If you’d instead like to delete all the comments associated with the post, you could delete comments from post 123 with:

  DELETE from wp_comments WHERE comment_post_ID=123;

How to Delete All Unapproved Comments In WordPress?

Make a backup of your database before executing queries directly. Executing this SQL statement will result in your entire queue of unapproved comments awaiting moderation to be blanked entirely.

Comments that haven’t been approved are stored in the wp_comments table of your database with a comment_approved status, or value, of “0”.

  DELETE FROM wp_comments WHERE comment_approved = "0";

What is a SQL Select Statement?

When you’re looking to get data from a database table, a SQL select statement is how you ask the database (query it) for information. The result of that query is a result-set.

The statement should be

SELECT ____from____;

In these you are asking for certain information from a particular table in the database.

When you SELECT *, you are asking for everything to be outputted from a particular table or tables.

Examples:

Show all the comments in the WordPress database:

  SELECT * FROM wp_comments;

Show all the users who are registered in the system:

  SELECT * FROM wp_users;

How to Select Some of the Information from a Table?

  SELECT comment_author, comment_author_email, comment_content FROM wp_comments;

How to Select All the Information from Multiple Tables?

  SELECT * FROM wp_comments, wp_commentmeta;

How to Select Related Information From Two Tables?

  SELECT wp_comments.comment_ID, wp_comments.comment_author,
  wp_comments.comment_content , wp_commentmeta.meta_key,
  wp_commentmeta.meta_value
  FROM wp_comments
  INNER JOIN wp_commentmeta ON
  wp_comments.comment_ID=wp_commentmeta.comment_id;

In the above example, the select statement is designed to pull all the authors and their comments, with linked metadata.

What is a SQL Update Statement?

When you’re looking to modify data in one or more database tables, a SQL update statement is how you tell the database to change information.

The statement should be

  UPDATE ___ SET ____=_____;

Examples:

Update all comments in the WordPress database so that their status is set to trash Execute:

  UPDATE wp_comments SET comment_approved='trash';

Update comments in the WordPress database so that their status is set to trash, based on a particular post ID. If the post ID was 123, you’d be able to use phpMyAdmin SQL tool to execute:

  UPDATE wp_comments SET comment_approved='trash' WHERE comment_post_ID=123;

What is a SQL Delete Statement?

When you’re looking to delete data in one or more database tables, a SQL delete statement is how you tell the database to remove information.

The statement should be

  DELETE FROM ___ WHERE ____;

Examples:

Delete all approved comments

Execute:

  DELETE FROM wp_comments WHERE comment_approved = '1';

Delete comments and associated commentmeta at the same time

Execute:

  DELETE wp_comments, wp_commentmeta FROM wp_comments INNER JOIN
  wp_commentmeta WHERE wp_comments.comment_id=wp_commentmeta.comment_ID AND
  wp_comments.user_id = 0;
  DELETE FROM wp_comments WHERE user_id = 0;

The above will remove comments and metadata from unregistered users (user_id=0).

Delete commentmeta, then comments for any comment which has text “ABC” within the comment content

  DELETE wp_commentmeta WHERE comment_id IN
    (SELECT comment_ID FROM wp_comments WHERE comment_content LIKE '%ABC%');

Then run this:

  DELETE FROM wp_comments WHERE comment_content LIKE '%ABC%';

How to Import a WordPress Database?

Assuming you’ve got a blank database created in PHPMyAdmin named “mywpdb”….

Select the database from within PHPMyAdmin.

Click Import at the top.

Choose UTF-8, or whatever character set was used in creating the backup file you’re about to import.

Click on the Browse Your Computer “Choose File” button, find the file and pick okay.

Once it completes, you’ll see a message that Import has been successfully finished.

What are the WordPress Database Tables Associated with Posts?

wp_posts

  ID: post unique reference number
  comment_count: comment + pingback + trackback count
  comment_status: whether or not comments are allowed
  guid: Global Unique Identifier - full URL, not permalink, of the post
  menu_order: used for menu display number
  pinged: list of URLs that got pingback notifications to when updated.
  ping_status: whether or not pingbacks and trackbacks are allowed to post
  post_author: ID of the wp_user ID who created the post
  post_content: post content
  post_content_filtered: cached version for plugin usepost_date: post creation date and time
  post_date_gmt: post creation date and time in GMT
  post_excerpt: post shortened introduction to the content
  post_mime_type: attachment MIME type
  post_modified: when the post was last modified, date and time
  post_modified_gmt: when the post was last modified in GMT
  post_name: post title slug (for URLs)
  post_parent: links to another post when revising or attaching
  post_password: if password protected, this is the password used for access
  post_status: post status such as whether it is published, draft or awaiting publishing
  post_title: post title
  post_type: what content type is the post
  to_ping: pingback list of URLs to notify on update or publishing

wp_postmeta

  meta_id: ID for the post metadata
  post_id: the ID for the wp_posts associated post
  meta_key: an identifying key for the piece of data.
  meta_value: the actual piece of data.

What are the WordPress Database Tables Associated with Comments?

wp_comments

  comment_ID: ID for the comment
  comment_post_ID: The post for which the comment is posted
  comment_author: Comment author name
  comment_author_email: Author name for the comment
  comment_author_url: Author url
  comment_author_IP: Author IP address
  comment_date: Comment Date
  comment_date_gmt: GMT time of the comment
  comment_content: Contents of the comment (comment body)
  comment_karma: unused
  comment_approved: Flag for the comment status.
  comment_agent: browser agent
  comment_type: comment (null?), pingback or trackback
  comment_parent: For threaded comment
  user_id: user id if comment author is a registered user of the site.

wp_commentmeta

  meta_id: ID for the comment metadata
  comment_id: ID for the comment associated
  meta_key: What kind of metadata
  meta_value: The metadata

What are the WordPress Database Tables Associated with Users?

wp_users

  ID: unique ID for the user
  display_name: shown name of user
  user_activation_key: key to activate user registration on lost password requests
  user_email: user email address
  user_login: user login name
  user_nicename: user alternative display name
  user_pass: user login password
  user_registered: date and time the user registered
  user_status: deprecated user status no longer used
  user_url: user site URL

wp_usermeta

  umeta_id: ID for the user metadata
  user_id: ID for the user associated
  meta_key: What kind of metadata
  meta_value: The metadata

What are the WordPress Database Tables Associated with Terms?

wp_terms

  term_id – ID for the term
  name – term name
  slug – slug of the name (URL)
  term_group – group name for pooling terms together

wp_term_taxonomy

  term_taxonomy_id – ID for the term taxonomy
  term_id – term ID from wp_terms
  count – post object count of all assigned this term in the taxonomy
  description – taxonomy description of each term
  parent – ID of parent terms such as in hierarchical taxonomies such as categories
  taxonomy – slug of the taxonomy

wp_term_relationships

  object_id – ID for the post object in wp_posts
  term_taxonomy_id – ID for the term taxonomy pair in wp_term_taxonomy
  term_order – ordering of object terms

wp_termmeta

  meta_id – ID for the term metadata
  term_id – term ID from wp_terms
  meta_key: What kind of metadata
  meta_value: The metadata

What is the WordPress Table Associated with Admin Options?

wp_options

  option_id – ID for each option
  autoload – whether or not option is automatically loaded
  option_name – friendly name of each option
  option_value – option data (serialized data most often)

wp_links

  link_id – unique ID for each link entry
  link_description – link description giving added details
  link_image – image URL that relates to the link
  link_name – link friendly name
  link_notes – link notes
  link_owner – user ID of link creator from wp_users table
  link_rating – 0-10 rating score for the link
  link_rel – link relationship
  link_rss – link RSS address
  link_target – link target such as _blank or _none
  link_updated – link update date and time
  link_url – link URL
  link_visible – whether link is private or public

How to Find Comments by a Certain User?

  SELECT * FROM wp_comments WHERE comment_author_email = ‘email address’;

Note: replace ‘email address’ with the single quote encapsulated actual email address like: ‘someuser@googlemail.com’

How to Delete Comments and Metadata from a Certain User?

  DELETE wp_commentmeta WHERE comment_id IN
    (SELECT comment_ID FROM wp_comments WHERE comment_author_email = 'email address');

Then run this:

  DELETE FROM wp_comments WHERE comment_author_email = 'email address';

Note: replace ‘email address’ with the single quote encapsulated actual email address like: ‘someuser@googlemail.com’

How to Find Comments on a Particular Post ID?

  SELECT * FROM wp_comments WHERE comment_post_ID = 123;

Note: replace 123 with the actual post ID.

How to Delete Comments on a Particular Post ID?

  DELETE wp_commentmeta WHERE comment_id IN
    (SELECT comment_ID FROM wp_comments WHERE comment_post_ID = 123);

Then run this:

  DELETE FROM wp_comments WHERE comment_post_ID = 123;

Note: replace 123 with the actual post ID.

How to Find All Spam, Trash or Unapproved Comments?

Spam:

  SELECT * from wp_comments WHERE comment_approved = 'spam';

Trash:

  SELECT * from wp_comments WHERE comment_approved = 'trash';

Unapproved:

  SELECT * FROM wp_comments WHERE comment_approved = "0";

How to Delete All Spam, Trash or Unapproved Comments and Associated Metadata?

Spam:

  DELETE wp_commentmeta WHERE comment_id IN
    (SELECT comment_ID FROM wp_comments WHERE comment_approved = ‘spam’);

Then run this:

  DELETE FROM wp_comments WHERE comment_approved = ‘spam’;

Trash:

  DELETE wp_commentmeta WHERE comment_id IN
    (SELECT comment_ID FROM wp_comments WHERE comment_approved = ‘trash’);

Then run this:

  DELETE FROM wp_comments WHERE comment_approved = ‘trash’;

Unapproved

  DELETE wp_commentmeta WHERE comment_id IN
    (SELECT comment_ID FROM wp_comments WHERE comment_approved = ‘0’);

Then run this:

  DELETE FROM wp_comments WHERE comment_approved = ‘0’;

How to Find WordPress Metadata Based on the meta_key?

Users

  SELECT * FROM wp_usermeta WHERE meta_key = ‘job_title’;

Posts

  SELECT * FROM wp_postmeta WHERE meta_key = ‘post_rating’;

Comments

  SELECT * FROM wp_commentmeta WHERE meta_key = ‘comment_vote’;

The above queries will find all the metadata associated with particular users, posts or comments, where the meta_key field matches the text list.

How to Find WordPress meta_key Based on Fuzzy Matching of the Key?

When you don’t know the exact key but have a general idea, you can find a list of meta_key information so that you can then use that in further searches.

Comments

  SELECT meta_key FROM wp_commentmeta WHERE meta_key like ‘%vote%’;

By using this approach you’ll see a list of keys with ‘vote’ in the key. Then, you can drill more data out of the database with further select queries. For example, if you found the key of interest to be ‘comment_vote’, you could use that in further queries to get all entries in the table where the key matches ‘comment_vote’.

Comments

  SELECT * FROM wp_commentmeta WHERE meta_key = ‘comment_vote’;

Or combine the query with pulling in comment text information and metadata at the same time for even more power by joining the two tables and including a where clause.

  SELECT wp_comments.comment_ID, wp_comments.comment_content, wp_commentmeta.meta_key, wp_commentmeta.meta_value
  FROM wp_comments
    INNER JOIN wp_commentmeta ON
      wp_comments.comment_ID=wp_commentmeta.comment_id
    WHERE wp_commentmeta.meta_key=’comment_vote’;

How to Delete WordPress Metadata Based on the Key?

Users

  DELETE FROM wp_usermeta WHERE meta_key = ‘job_title’;

Posts

  DELETE FROM wp_postmeta WHERE meta_key = ‘post_rating’;

Comments

  DELETE FROM wp_commentmeta WHERE meta_key = ‘comment_vote’;

How to Find WordPress Metadata Based on the meta_value?

Refer to the section on how to find metadata based on meta_key. The only change is substituting the meta_value in place of the meta_key in the select queries.

How to Delete WordPress Metadata Based on the meta_value?

Refer to the section on how to find metadata based on meta_key. The only change is substituting the meta_value in place of the meta_key in the delete queries.

How to Find WordPress Administrators?

Sometimes you’ll encounter situations where there are unexpected users who have admin authority. Later, you’ll want to remove them (after making a new admin user for yourself).

Find Admins

  SELECT * FROM wp_usermeta WHERE meta_value LIKE ‘%administrator%’;
  SELECT * FROM wp_usermeta WHERE meta_key=’wp_user_level’ AND meta_value=’10’;

This should display lists of admins and superadmins.

How to Remove Some WordPress Administrators?

Make sure that you have a valid admin/superadmin login that works before proceeding.

Run the query above in how to find WordPress administrators and make note of the results. Browse the wp_users table, highlight the admins to be removed and delete.

How to Remove WordPress Users with No Posts or Comments?

Make sure that you have made posts or comments with your admin accounts before running something like this!

Run all three to clear users, meta and links associated with those users who are inactive. This from WPWhackedHelp.com:

  DELETE FROM wp_users WHERE ID> 1 AND ID NOT IN 
    (SELECT DISTINCT post_author FROM wp_posts) AND ID NOT IN (SELECT DISTINCT user_id FROM wp_comments);
  
  DELETE FROM wp_usermeta WHERE user_id> 1 AND user_id NOT IN 
    (SELECT DISTINCT post_author FROM wp_posts) AND user_id NOT IN 
      (SELECT DISTINCT user_id FROM wp_comments);

  DELETE FROM wp_links WHERE link_owner> 1 AND link_owner NOT IN 
    (SELECT DISTINCT post_author FROM wp_posts) AND link_owner NOT IN 
      (SELECT DISTINCT user_id FROM wp_comments);

How to Delete Comments and Comment Meta Without Joining Tables?

A simple approach is to delete comments based on criteria, then delete all commentmeta that isn’t associated with any comments. This works because the deleting of comments effectively makes commentmeta orphaned (an easy thing to spot in the database).

Example – Deleting Comments Made Within a Date Range

  DELETE FROM wp_comments WHERE comment_date 
    BETWEEN '2000-01-01 00:00:00' AND '2019-01-01 00:00:00';

Then – Delete the Comment Meta of Orphaned Entries

  DELETE FROM wp_commentmeta WHERE comment_ID NOT IN 
    (SELECT comment_ID FROM wp_comments);

How to Delete Comments and Meta Made Within a Date Range (alternative)?

  DELETE FROM wp_comments 
    WHERE comment_date >= '2000-01-01 00:00:00' 
    AND comment_date <= '2019-01-01 00:00:00';

Then – Delete the Comment Meta of Orphaned Entries

  DELETE FROM wp_commentmeta WHERE comment_ID NOT IN 
    (SELECT comment_ID FROM wp_comments);

Note: This alternative approach is the same as the example before it, since the MySQL between operator is inclusive.

How to Disable Comments on All Posts?

You can turn off comments within the WP admin section of WordPress or you can do it via a SQL one-liner. This one will turn off comments and pingbacks for all posts that have comments enabled.

  UPDATE wp_posts SET comment_status = 'closed', ping_status = 'closed' 
  WHERE comment_status = 'open';

How to Disable Comments Only on Older Posts?

It’s easy to turn off comments and pingbacks on older posts with this one-liner. Alternatively, you could set a date range as well.

  UPDATE wp_posts 
  SET comment_status = 'closed', ping_status = ‘closed’ 
  WHERE post_date < '2019-10-10’ AND post_status = 'publish’;

How to Set a New Admin Password?

The below update statement assumes that you’ll change the user_login to your adminusername, whatever that might be, and your new password to the ‘whateverismynewpassword text below.

  UPDATE wp_users SET user_pass = MD5( 'whateverismynewpassword' ) 
  WHERE wp_users.user_login = "adminusername";

How to Delete All Approved Comments?

Not sure why they’d all be approved, but hey – sometimes you need a massive cleaning.

  DELETE FROM wp_comments WHERE comment_approved = '1';

Then – Delete the Comment Meta of Orphaned Entries

  DELETE FROM wp_commentmeta 
  WHERE comment_ID NOT IN 
    (SELECT comment_ID FROM wp_comments);

How to Get Comment Counts by Author?

This one is handy for finding out who your most prolific commenters (or spammers) are – based on activity since the beginning of 2019.

  SELECT COUNT(*) AS comcount, comment_author, user_id FROM wp_comments
  WHERE comment_approved = 1 AND comment_date >= '2019-01-01'
  GROUP BY user_id
  ORDER BY comcount DESC;

How to Find Comment Spammers by Activity of IP Address?

  SELECT COUNT(*) AS comcount, comment_author, comment_author_IP 
  FROM wp_comments
  WHERE comment_approved = 1 AND comment_date > '2019-01-01'
  GROUP BY comment_author_IP
  ORDER BY comcount DESC

How to Extract Contact Details of Commenters for Email Marketing List Building?

Pull key contact details so you can use it in an email campaign.

  SELECT DISTINCT comment_author, comment_author_email
  FROM wp_comments
  WHERE comment_approved = 1
  ORDER BY comment_author_email ASC

How to Reset WordPress Theme to Default When Site is Crashing using SQL?

There can be times when things have gone horribly wrong with a theme, or theme plus plugins not cooperating, yielding crashes and problems that just don’t seem to clear up.

Resetting the default theme to Twenty Eleven using SQL is one way to get it loaded with something that should work. This is particularly handy when you can’t login to the WordPress admin screens and need a way of getting things back to working properly.

While just changing the template option may be enough, it’s likely smarter to go ahead and set the stylesheet and current_theme as well, like so:

  UPDATE wp_options 
  SET option_value = 'Twenty Eleven' 
  WHERE option_name = 'template';
  
  UPDATE wp_options 
  SET option_value = 'twentyeleven' 
  WHERE option_name = 'stylesheet';
  
  UPDATE wp_options 
  SET option_value = 'twentyeleven' 
  WHERE option_name = 'current_theme';

How to Change WordPress Pages to Posts using SQL?

In case you’ve inadvertently started off creating pages for all your content, thinking you were making posts, and would now like to change all those pages to posts, you can either do them all at once or one at a time with SQL.

Change All Posts to Pages:

  UPDATE wp_posts 
  SET post_type = 'post' 
  WHERE post_type = 'page';

Change Only Certain Posts to Pages (replace 123 with the post ID you want changed):

  UPDATE wp_posts 
  SET post_type = 'post' 
  WHERE post_type = 'page' and ID = ‘123’;

How to Find WordPress Category ID via Category Name using SQL?

There are a dozen or more ways of doing this, but using the category name to get the ID isn’t too terrible.

  SELECT wp_term_taxonomy.term_taxonomy_id
  FROM wp_term_taxonomy JOIN wp_terms ON (wp_term_taxonomy.term_id = wp_terms.term_id)
  WHERE wp_term_taxonomy.taxonomy = 'category' AND wp_terms.name = 'yourcategoryname';

When Setting to_ping or pinged in WordPress wp_posts, What Delimiter Should be Used?

When loading a list of URLs into the database, they should be delimited by a “\n”;

What are Valid Post Types in WordPress?

In wp_posts, the post_type can be set to “post”, “page” or “attachment”, plus any additional user-defined types.

What 4 Languages Represent the Database Commands for MySQL?

Data Definition Language or DDL

The DDL is used in defining the database itself. DDL creates database schema and deals with database objects, like tables. With DDL, you can make wholesale changes:

  Alter - change the database structure and object definitions
  Comment - create documentation for objects
  Create - makes objects
  Drop - removes objects
  Rename - changes object name
  Truncate - cleans out all records in a table

DDL works with objects such as:

  Functions
  Indexes
  Procedures
  Tables
  Triggers
  Views

Data Manipulation Language or DML

The DML comes into play when you manipulate the database data. With DML you can:

  Call - executes a stored procedure
  Delete - remove rows from a table
  Explain Plan - advises on how it will be handling a query request
  Insert - add data to a table
  Lock Table - prevents other potentially conflicting accesses to a table
  Merge - Inserts/Updates [upserts] tables depending on keys
  Update - modifies rows in a table

Data Control Language or DCL

DCL is how you control which user has privileges to impact database objects. With DCL you can:

  Grant - give ability for user to do something 
  Revoke - withdraw ability for user to do something

Transaction Control Language or TCL

TCL is for manipulating transactions. With TCL you can:

  Commit - writes transaction data to the database since last commit or rollback
  Rollack - undo transactions since last commit/rollback
  Savepoint - lets you pick a point where you can partially rollback to
  Release Savepoint - removes savepoints
  Set Transaction - names a transaction