Work with MySQL – Basic WordPress Queries

This article was born, as I was searching for ways to handle thousands of registered WordPress users, who never do anything, never return to the site, and never even change their default password.

 

            Table of Topics

-1030- List all registered users sorted by date
-1060- Group registered users by email domain name
-1090- Users who ever posted articles
-1120- List of registered users who ever posted comments
-1150- List of users, who posted messages in your bbPress Forum
-1180- Group users by year and month of registration
-1210- Count users in different roles
-1240-  Users who never changed their default password
-1270-  Users who changed their default password
-1300- Discover database orphan records

-1400- Count all types of posts [2013-02-25 MO 19:58]
-1430- Users who ever created a record in wp_posts

 

 

-1030- List all registered users sorted by date

use YourDB;
select user_email, user_registered from wp_users
order by user_registered DESC;

 

 

-1060- Group registered users by email domain name emails

Users are stored in a table wp_users, and user emails are stored in a field user_email.  It is 100 positions long.  Can we group together all users with the same domain name in email and display a count, if over a certain threshold?

Let’s start with a simple task of displaying every user from a given domain:

SELECT USER_EMAIL FROM WP_USERS WHERE USER_EMAIL LIKE ‘%DOMAIN.COM%’;

Is it possible to group records together based on results of a string search function? Yes!

Here is how:

select right(user_email, length(user_email)- instr(user_email,"@"))
as Domain, count(1) from wp_users group by Domain;

This command will return 2 columns:

First, the domain name extracted from your users emails;

Second, the count of all instances of these domains.

This statement allows one more way to look at your registered users, and where they are coming from.

 

 

-1090- Users who ever posted articles

Linking 2 tables –  wp_users and wp_posts:

sselect wp_users.ID, user_login, user_email, count(*) from wp_users,
wp_posts where wp_users.ID =  post_author group by ID, user_login,
user_email;

This is usually a very short list, unless you ave tons of editors and contributors.

 

-1120- List of registered users who ever posted comments

select user_id, comment_author_email, count(*) from wp_comments
where user_id > 0 group by user_id, comment_author_email;

This query will return 3 columns: User ID, Email and Comment Count – how many comments user posted on your site.

 

 -1150- List of users, who posted messages in your bbPress Forum

If you need to identify inactive users, start with listing users who ever posted a message in your blog:

select ID, user_login, user_email, count(*) from wp_users, bb_posts
where ID = poster_id group by ID, user_login, user_email
order by count(*) DESC;

 

-1180- Group users by year and month of registration

If you need to understand trends in users registration on your site, here is a perfect statement:

select year(user_registered), month(user_registered), count(*)
from wp_users group by year(user_registered), month(user_registered);

This SQL statement returns 3 columns: Year, Month and User count.

 

-1210- Count users in different roles 

User role in berried deep inside wp_usermeta table.

show columns from wp_usermeta;
select meta_value, count(*) from wp_usermeta where
meta_key = 'wp_capabilities' group by meta_value;

 

 

-1240-  Users who never changed their default password

Expect a long list.  With robot register, expect hundreds on meaningless registrations.

With the help of our reader Martin (see comment bellow), we are correcting the sintax of the SQL statement.  Old clause meta_value != ‘1’ was incorrect:

select ID, user_login, user_email, meta_value AS Default_Password
from wp_users, wp_usermeta where ID = user_id and meta_key =
'default_password_nag' and meta_value = '1';

You can search Internet for “default_password_nag” for an explanation.

 

 

-1270-  Users who changed their default password

I really love  this one.  I had to do a sub-query into a wp_usermeta table that I already joining with wp_users table.

select ID, user_login, user_email, user_registered from wp_users, wp_usermeta
where user_id not in (select user_id from wp_usermeta where meta_key =
'default_password_nag' ) and ID = user_id group by user_id;

All other users are pretty much a deadbeats.

 

-1300- Discover database orphan records

 

This is useful in situation, where you suspect that you lost some records in one table, but some remnants still exits in other table:

select user_ID from wp_usermeta where user_id not in (select ID from
wp_users);

select ID from wp_users where ID not in (select user_id from
wp_usermeta);

 

-1400- Count all types of posts

 

select post_type, count(*) from wp_posts group by post_type;

Here are examples of some post types: attachment, forum, nav_menu_item, page, post, reply, revision, topic, wpcf7_contact_form.   Make sure you understand, what each type of post is for.

-1430- Users who ever created a record in wp_posts

Here you get an ID, user login name and cpont of records affected:

select a.post_author, b.user_login, count(*) from wp_posts a join wp_users b
on a.post_author = b.ID group by a.post_author, b.user_login;

 

. . .