Solved Kunena mass delete users

Hi
I have 35 thousand users in my forum and i would like to delete all users with 0 posts. Could you advise how to make it please?

Goodness me. That's a lot of members.

Yes, it's possible to write an SQL query, run it against your database, and then target those members and delete them.

The first thing you need to do is write a query to obtain a list of members who have zero posts. Kunena stores this information in the table _kunena_users. Using phpMyAdminthe query would look something like:
where "xxxxx" is the prefix of your Joomla table. This will give you the user IDs of those accounts that have not posted messages on your forum.

It should not take you too much trouble to convert the SELECT statement above into a DELETE statement . I'll leave you to figure out the last part for yourself.

Make sure you have a good backup of your Joomla user table before you execute the DELETE, OK? After you delete the Joomla user records, run the Kunena Synch User facility.

sozzled wrote: Goodness me. That's a lot of members.

Yes, it's possible to write an SQL query, run it against your database, and then target those members and delete them.

The first thing you need to do is write a query to obtain a list of members who have zero posts. Kunena stores this information in the table _kunena_users. Using phpMyAdminthe query would look something like:

Code:

SELECT * FROM `xxxxx_users` AS Table1 INNER JOIN `xxxxx_kunena_users` AS Table2 ON Table1.id = Table2.userid WHERE Table2.posts = 0 LIMIT 0,30000

where "xxxxx" is the prefix of your Joomla table. This will give you the user IDs of those accounts that have not posted messages on your forum.

It should not take you too much trouble to convert the SELECT statement above into a DELETE statement . I'll leave you to figure out the last part for yourself.

Make sure you have a good backup of your Joomla user table before you execute the DELETE, OK? After you delete the Joomla user records, run the Kunena Synch User facility.

Thank you pal) This is really nice DB request)

Hey!
For two days try to delete users, but i can not manage to make right sql query.

I do it like that
DELETE FROM xxxxx_users WHERE xxxxx_users.id = xxxxx_kunena_users.userid AND xxxxx_kunena_users.posts = 0

But i get an error #1054 - Unknown column 'xxxxx_kunena_users.userid' in 'where clause'

Please help to delete users

Select works nice and smooth!

[strike]You can delete your users manually.

Open the table *_kunena_users
Select Number of rows: (what is the best for you)
sort by posts
mark and delete all users with 0 posts (it is slightly more work, but it works also).[/strike]

@rich: That will only delete the records in the table _kunena_users. If you use the Synchronize Users facility (Forum Tools in the backend) all the Kunena user profiles will be rebuilt from the Joomla _users table. The task is to delete the records from the _users table, perform the Synchronize Users and then the Kunena users will also be removed.

What you need to do is to open the table _users and delete those records, but the question is, which records do you need to delete?

Instead, you need to create a separate SQL DELETE query to delete records from _users.

There's no go guarantee that what I am about to propose will work (becuase I haven't tried it) but you need something like "DELETE FROM xxxxx_users WHERE (SELECT * FROM `xxxxx_users` AS Table1 INNER JOIN `xxxxx_kunena_users` AS Table2 ON Table1.id = Table2.userid WHERE Table2.posts = 0);" ... and that's just a guess.

You're right I'm an jackass. :blush:
The users must be removed completely from Joomla.

Thank you for support guys! I worked it out!

DELETE `xxxxx_users`.*,`xxxxx_kunena_users`.*
FROM `xxxxx_users`,`xxxxx_kunena_users`
WHERE `xxxxx_users`.`id`=`xxxxx_kunena_users`.`userid`
AND `jku9b_kunena_users`.`posts`=0

A follow up, I am going to change this to a feature request to prune users who have 0 posts after a period of registration. This way if the accounts are dormant accounts, most likely spam accounts, there is an easier methods to prune users and have more accurate count on the website.

We could have this manually done, or maybe automatically done when someone logs into the site.

coder4life wrote: A follow up, I am going to change this to a feature request to prune users who have 0 posts after a period of registration. This way if the accounts are dormant accounts, most likely spam accounts, there is an easier methods to prune users and have more accurate count on the website.

We could have this manually done, or maybe automatically done when someone logs into the site.

Big thanks to all team