I need to write a SQL script to delete users with no posts associated with them in a WordPress database. I tried this script after doing some searching:
DELETE FROM wp_users WHERE ID NOT IN (SELECT DISTINCT post_author FROM wp_105_posts)
but this deleted all the users. Can someone let me know what I am doing wrong please?
Seems like either none of the users made posts, or wp_105_posts.post_author doesn't link to wp_users.ID. Are you sure wp_105_posts.post_author is a Foreign Key of wp_users.ID?
wp_105_posts should have one column that references who the user is in the wp_users table. Since your GUID Primary Key for wp_users is ID, this should be the one column you use to reference the user from your post tables. Here is a small example:
user_table
ID | name | age | email | etc.
1 | nick | 24 | [email protected] | ..
2 | bob | 30 | [email protected] | ...
3 | sue | 35 | [email protected] | ...
a_post_table
ID (ID of post) | User_ID (ID of user) | title | date | body
........1..............|...1 (post by nick)........ | help | 1/1/11
........2..............|...1 (post by nick)........ | help | 3/2/11
........3..............|...2 (post by bob)........ | help | 5/6/11
As you can see, the post table knows everything about the author by simply holding its ID. You can use a join query to get all user info from the post table just by knowing its ID. Using this query now
DELETE FROM user_table
WHERE ID NOT IN
(SELECT DISTINCT User_ID FROM a_post_table)
Will delete Sue, and Sue only, given the data above.
HTH
ID), KEY user_login_key (user_login), KEY user_nicename (user_nicename and the primary keys for my wp_105_posts table are: PRIMARY KEY (ID), KEY post_name (post_name), KEY type_status_date (post_type,post_status,post_date,ID), KEY post_parent (post_parent), KEY post_author (post_author) - Should I be using one of these? Apologies, I am a beginner.