×
Kunena 5.2.6 released and Blue eagle 1.6.6 released (25 Jul 2021)

The Kunena team has announce the arrival of Kunena 5.2.6 [K 5.2.6] which is now available for download as a native Joomla extension for J! 3.9.x. This version addresses most of the issues that were discovered in K 5.2 and issues discovered during the development stages of K 5.2.6

× Topics that are moved into this category are generally considered to be closed. Users may want to add additional information but these topics should not be resurrected in order to discuss new problems or unrelated matters.

Solved Kunena mass delete users

More
8 years 7 hours ago #1 by mecenat
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?

Please Log in or Create an account to join the conversation.

More
8 years 6 hours ago - 8 years 6 hours ago #2 by sozzled
Replied by sozzled on topic Kunena mass delete users
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:
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.
Last edit: 8 years 6 hours ago by sozzled.

Please Log in or Create an account to join the conversation.

More
7 years 11 months ago #3 by mecenat
Replied by mecenat on topic Kunena mass delete users

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:
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)

Please Log in or Create an account to join the conversation.

More
7 years 11 months ago #4 by mecenat
Replied by mecenat on topic Kunena mass delete users
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 :unsure:

Select works nice and smooth!

Please Log in or Create an account to join the conversation.

More
7 years 11 months ago - 7 years 11 months ago #5 by rich
Replied by rich on topic Kunena mass delete users
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).
Last edit: 7 years 11 months ago by rich.

Please Log in or Create an account to join the conversation.

More
7 years 11 months ago - 7 years 11 months ago #6 by sozzled
Replied by sozzled on topic Kunena mass delete users
@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.
Last edit: 7 years 11 months ago by sozzled.

Please Log in or Create an account to join the conversation.

More
7 years 11 months ago #7 by rich
Replied by rich on topic Kunena mass delete users
You're right I'm an jackass. :blush:
The users must be removed completely from Joomla.

Please Log in or Create an account to join the conversation.

More
7 years 11 months ago #8 by mecenat
Replied by mecenat on topic Kunena mass delete users
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

Please Log in or Create an account to join the conversation.

More
7 years 11 months ago #9 by coder4life
Replied by coder4life on topic Kunena mass delete users
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.
The following user(s) said Thank You: mecenat

Please Log in or Create an account to join the conversation.

More
7 years 11 months ago #10 by mecenat
Replied by mecenat on topic Kunena mass delete users

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 ;)

Please Log in or Create an account to join the conversation.

  • Not Allowed: to create new topic.
  • Not Allowed: to reply.
  • Not Allowed: to edit your message.
Time to create page: 0.101 seconds