Gossamer Forum
Home : Products : Gossamer Links : Discussions :

Delete Users with No Links

Quote Reply
Delete Users with No Links
I'm just wondering if there is an easy way to delete all Users with No Links.

In updating my directory I have been using the "Delete Links" to get rid of the junk. (I know now I should have deleted the User).

Anyway, I'm left with a bunch of Users with no links.

Thanks,

Quote Reply
Re: [Clueless] Delete Users with No Links In reply to
Hi
Did you ever figure out how to delete users who have no links?

Capt*
Quote Reply
Re: [CptPugwash] Delete Users with No Links In reply to
Hi,

No, there isn't an easy way to do this. We'll look at expanding this in the next release.

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] Delete Users with No Links In reply to
Alex, is there any way to delete user with 0 (zero) links. I have a lot of them and would like to drop them of the emaillist. Is there any way of doing this with a SQL Statement. Any help would be great. Thanks. C. hh1966@hotmail.com
Quote Reply
Re: [guz--nuname] Delete Users with No Links In reply to
You do know you can mass mail linkowners only, by clicking "All Links" in the Email area of the admin.

You might be able to do this with a multiple/nested select.

You could also do it a "safe" way, adding a field to the Users table which holds the number of links a user has, such as User_Num_Links.

I'm too groggy to come up with the righ SQL, but you can do an Insert/Select on the Links table, but this might work.

--- code ---

INSERT INTO L2_Links User_Num_Links
SELECT COUNT(*)
FROM L2_Links, L2_Users
WHERE L2_Links.LinkOwner = L2_Users.Username AND
(( L2_Links.Contact_Email LIKE '%_%' OR L2_Users.Email LIKE '%_%' ) AND L2_Users.ReceiveMail = 'Yes')

--- end ---

Then, you'd "DELETE FROM L2_Links where User_Num_Links = 0

The up side of this, is if you keep the SQL, you can prune your database whenever you need to, at the cost of the 1 extra field.


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.
Quote Reply
Re: [pugdog] Delete Users with No Links In reply to
I also wanted to delete users with no links and this SQL code doesn't work. It's error somewhere at his point,
Code:
INSERT INTO L2_Links User_Num_Links
SELECT COUNT(*)

Maybe some other option?

Thanks.

UnReal Network
Quote Reply
Re: [deadroot] Delete Users with No Links In reply to
First, you have to be using L2_ as your database prefix, you'd need to change that to whatever you used.

Second, you'd need to have a field "User_Num_Links" in your users table, set as INT

My solution was a "General" one, not set for any specific site.


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.
Quote Reply
Re: [pugdog] Delete Users with No Links In reply to
I figure that out about L2_ and changed to my prefix.

But didn't set User_Num_Links as INT. Will do that and try again.

Thanks

UnReal Network
Quote Reply
Re: [deadroot] Delete Users with No Links In reply to
Here's a bizare suggestion.

Yesterday, I worked out a global that would keep track of a user's link when they logged in. This modification may work, but you probably would want to change it to a bogus username after running it once.

The working global is here: http://www.gossamer-threads.com/...orum.cgi?post=240773

The modified version is below. You need to install it as in the working version, and if you know your way around the admin, it would take about 2 minutes. Once installed, and you log in with your admin ID, you should have values in the two columns "User_Links_Validated" and "User_Links_Unvalidated". If you do, and you are comfortable the update worked, you can then just

Delete from prefix_Users where (User_Links_Validated = 0 and User_Links_Unvalidated = 0 )

(Always make the "Delete From" a "Select * from" to make sure it works, then change it)

NOTE: Don't use this if you have 1000's of users. Although Links may have built in limiting code. If so, after running this once, _some_ fields will have updated fields with values. You'll have to change the select Users code to exclude selecting users with validated or unvalidated links, and keep logging in <G> It's not elegant, but it _should_ work.




Code:
sub {
## if you add two columns to your Users table, Link_Count_Validated and Link_Count_Unvalidated
## every time a user logs on, their counts will be updated in their user record.
## low overhead call, as long as you only call it on the Logon Success page.
## or, add a test, and another parameter to flag the updates.
##
## my $update_USER_counts = shift;
##
## and wrap the db_user->update call in an 'if $update_USER_counts call.
## pass in <%get_user_link_count (1)%> to flag the update, 0 to ignore.
##
my $update = shift;
($USER) || return (user_link_count => 0, user_link_count_un => 0, user_link_count_val => 0);
my $db = $DB->table('Links');
print "username is: $USER->{Username}";
my $user_link_count_val = $db->count(isValidated => 'Yes', LinkOwner => $USER->{Username});
my $user_link_count_un = $db->count(isValidated => 'No', LinkOwner => $USER->{Username});
my $user_link_count = $user_link_count_val + $user_link_count_un ;

if ($USER->{'Username'} eq 'Your_Admin_ID') {
my $user_db = $DB->table('Users');
my $sth = $user_db->select ( { }, [Username]); ## I'm not sure about this line

while (my $cat = $sth->fetchrow_hashref) {
my $user_name = $cat->{Username};
my $user_link_count_val = $db->count(isValidated => 'Yes', LinkOwner => $cat->{Username});
my $user_link_count_un = $db->count(isValidated => 'No', LinkOwner => $cat->{Username});
my $user_link_count = $user_link_count_val + $user_link_count_un ;
$db_user->update (
{
Link_Count_Validated => $user_link_count_val,
Link_Count_Unvalidated => $user_link_count_un
},
{
Username => $cat->{'Username'}
}
);
}
} elsif ($update) {
my $db_user = $DB->table('Users');
$db_user->update (
{
Link_Count_Validated => $user_link_count_val,
Link_Count_Unvalidated => $user_link_count_un
},
{
Username => $USER->{'Username'}
});

};

return ( {
user_link_count => $user_link_count,
user_link_count_un => $user_link_count_un,
user_link_count_val => $user_link_count_val
})
}


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.