Gossamer Forum
Home : Products : Gossamer Links : Discussions :

Search for users that don't have links?

Quote Reply
Search for users that don't have links?
I realized after I started deleting links from the Verify Links pages that I was leaving behind Users that may not have any links attached to them any longer. I tried to search on Users, but there isn't a column that specifies the number of links.

Anyone know of a way I can do this without going through 20,000 users one by one?

Thanks!

Debbie
Quote Reply
Re: [loxly] Search for users that don't have links? In reply to
You can issue the following query in the SQL Monitor. This assumes your tables are named without a prefix.

Select Username from Users
left join Links ON Users.Username = Links.Linkowner
where Links.Linkowner is null

I hope that helps.



- Scott
Quote Reply
Re: [OnlineMlm] Search for users that don't have links? In reply to
Thanks, it worked Smile

Debbie
Quote Reply
Re: [OnlineMlm] Search for users that don't have links? In reply to
Hi, I know this is an old thread, but.... How would I take that outputted list and simply remove everyone on it? Remove all users with no links?
Quote Reply
Re: [Evoir] Search for users that don't have links? In reply to
I would like to 'send an email' to users that have registered, but have not submitted a link

Is there a way to 'search for users without links' and use the 'Email: Selected Users' function to remind them that they have no link submitted.

Thanks
Colin Thompson
Quote Reply
Re: [colintho] Search for users that don't have links? In reply to
Hi,

I don't think there is a built in way of doing this - but you could do it with a script, that would work out who hasn't got any links (but is registered), and then output a list of email addresses, which you could pass into the "custom email" system that GLinks has (i.e a custom list)

Something like this should work:

Code:
#!/usr/local/bin/perl

use strict;
use lib '/path/to/your/admin';
use Links qw/$IN $DB $CFG/;
use GT::SQL::Condition;
use CGI::Carp qw(fatalsToBrowser);

local $SIG{__DIE__} = \&Links::fatal;

Links::init('/path/to/your/admin');

print $IN->header();

my $user_sth = $DB->table('Users')->select( { Status => 'Registered' } ) || die $GT::SQL::error;
while (my $hit = $user_sth->fetchrow_hashref) {
if ($DB->table('Links')->count( { LinkOwner => $hit->{Username} } ) < 1) {
print qq|$hit->{Email}<br />|;
}
}

(totally untested, but should work fine)

BTW, be sure to edit the /path/to/your/admin bit to your GLinks admin path =)

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Search for users that don't have links? In reply to
Andy

that worked just fine.

Thanks, I owe you another 'beer'
Colin Thompson
Quote Reply
Re: [colintho] Search for users that don't have links? In reply to
MMm.. something really odd going on with the forum :/

I made a reply to this about 10 mins ago - but its not there ;/ It said it was posted ... (I even viewed it , on this thread).

Had a similar problem yesterday too - and a few days before (with the Thumb_Images_GD reply I made)

Pirate
Quote Reply
Re: [Evoir] Search for users that don't have links? In reply to
Evoir wrote:
Hi, I know this is an old thread, but.... How would I take that outputted list and simply remove everyone on it? Remove all users with no links?
I'd be interested in this too. Can anyone help, please? I have a list of 200 users I'd like to delete in bulk. Is that possible?
Quote Reply
Re: [iplay] Search for users that don't have links? In reply to
Hi,

Pretty simple =)

Try this (save in your admin folder, as delete_unused.cgi)

Code:
#!/usr/local/bin/perl

use strict;
use lib './';
use Links qw/$IN $DB $CFG/;
use GT::SQL::Condition;
use CGI::Carp qw(fatalsToBrowser);

local $SIG{__DIE__} = \&Links::fatal;

Links::init('./');

print $IN->header();

my $user_sth = $DB->table('Users')->select( { Status => 'Registered' } ) || die $GT::SQL::error;


while (my $hit = $user_sth->fetchrow_hashref) {

if ($DB->table('Links')->count( { LinkOwner => $hit->{Username} } ) < 1) {
print qq|$hit->{Email}<br />|;
$DB->table('Users')->delete( { Username => $hit->{Username} } ) || die $GT::SQL::error;
}

}

CHMOD to 755, then run from your browser with:

http://www.domain.com/cgi-bin/links/admin/delete_unused.cgi

It will output the users being deleted.

NB: PLEASE BACKUP YOUR DATABASE BEFORE RUNNING! It should do what you want - but you can never be too careful when doing delete's like this Tongue

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Search for users that don't have links? In reply to
Very many thanks Andy. I'll do that, and also add it to Quick Links in GL Admin for future use.
Quote Reply
Re: [iplay] Search for users that don't have links? In reply to
This is nice and something I wanted but will it not delete all users who signedup and have bookmarks in their account? Meaning, they just signed-up as users of the site only but not to submit any links.

How about a way to remove those who have not logged in for 6 months or 1 year - that would be better.

Thanks

Last edited by:

socrates: Apr 25, 2008, 8:01 PM
Quote Reply
Re: [socrates] Search for users that don't have links? In reply to
Hi,

The only problem with trying to check only users who havn't logged in for xx months, is there is no "log" of when they were last logged in (so you don't have anything to work with)

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Search for users that don't have links? In reply to
It would be nice if GT make something regarding that and so we can separate vistor accounts from linkowner accounts - that way we can delete all the unused user accounts (and not end up deleting linkowner accounts). Otherwise over a period of time there will be hundreds-thousands of unused accounts from users who signup and don't use the account.