I'm sure that many of you long standing site owners, know that there are many author accounts in your database with no penname associated with them. Here is a SQL query that will delete them.
You'll have to work in your myphpadmin section of your database for this ... but a php script could be written to do this as well.
***NOTE***
Its always a very good idea to backup your database before attempting any queries!!!!
In your myphpadmin, for your AD database, select SQL to run a query.
The first query below will give you a list of all the author accounts that have signed up with your directory but have no penname associated with that account.
- Code: Select all
SELECT * FROM prefix_users WHERE user_id not in
(SELECT user_id FROM prefix_penname)
Note that if you are not using the "prefix_" beginning for your table names, just substitute what you ARE using.
Now as mentioned the above will give you all the accounts without pennames.
To delete all of those accounts at once ... simply do another SQL query and run the following:
- Code: Select all
DELETE FROM prefix_users WHERE user_id not in
(SELECT user_id FROM prefix_penname)
Now all of those accounts are gone.
A word of warning ... if you do the above query, you will delete ALL accounts without pennames ... but you may have some recent signups that have not had a chance to put in their penname yet.
To remedy that, look at the user_id of the last author that signed up recently ... say its 1253. Do a similar query to delete authors without pennames, but only delete the one's with an id less than 1000. This still allows the last 253 accounts a chance to add a penname. The query that you will use would then be:
- Code: Select all
DELETE FROM prefix_users WHERE user_id not in
(SELECT user_id FROM prefix_penname) AND user_id < 1000
You can use whatever user_id limiter value you wish by just changing that 1000 number to a larger or smaller value ... depending upon how much you want to clear from your database.
Question:
I'm just curious... Why would you want to delete authors just because they don't have a pen name?
Answer: Of course there are a variety of reasons ...
They take up space in the database.
If you rarely email authors you'd probably like to be contacting the ones that have at least made a proper penname for their account.
Question:
Could you provide one that would delete authors with no articles?
Answer:
What one has to keep in mind that it may be a two step process. Remember that articles have a penname and userid (author account) associated with them. You would want to first delete pennames that do not have an article written under them first. Then after that process, delete authors without the pennames (as written in the first post).
This way, an author may have 5 pennames, of which 2 have no articles ... those 2 pennames could be deleted ... but the author account would not be deleted afterwards, because it has 3 pennames with articles.
Delete pennames with no articles associated
- Code: Select all
DELETE FROM prefix_penname WHERE pen_id not in
(SELECT penname_id FROM prefix_articles)
But as before you may wish to put a limit on pennames deleted so you are not deleting your latest pennames that haven't had a chance to create an article yet.
- Code: Select all
DELETE FROM prefix_penname WHERE pen_id not in
(SELECT penname_id FROM prefix_articles) AND pen_id < xxxx
Just replace the xxxx with a number ... let's say your latest penname has a pen_id of 1000 ... you may want to delete pennames up to the pen_id of 900 giving the last pennames a chance to input an article.
If you so choose, after deleting these pennames, you can then delete the author accounts by using the previous query above (first post of this topic).
