Gossamer Forum
(Advanced Search)
for
Sep 29, 2007, 11:23 AM
Matthias70
Enthusiast (893 posts)
Sep 29, 2007, 11:23 AM
Post #1 of 6
Views: 4191
Shortcut
Quote Reply
User Toplist
Hi,
I'm using the following global to show the users sorted by the number of link entries each user has
Code:
sub {

my $db = $DB->table('Links');
$db->select_options( "GROUP BY LinkOwner","ORDER BY link_count DESC" );
my $sth = $db->select("LinkOwner", "COUNT(*) as link_count") or die "Query Error: $GT::SQL::error";

my $back;
while (my ($s, $c) = $sth->fetchrow_array) {
if ($c < 7) { next; }
my $url = $CFG->{db_cgi_url} . "/search.cgi?query=;LinkOwner=$s";
$back .= qq|> <a href="$url">$s</a> ($c Einträge) <br />|;
}

return $back;

}

Can this code be modified to show the users sorted by the number of hits all their links have.
I have the number of hits each user has in this variable <%users_hits($Username)%>
Output should be something like this
1. User A: 1000 hits
2. User B: 900 hits
3. User C: 500 hits

Thanks
Matthias

Matthias
gpaed.de
Sep 29, 2007, 7:20 PM
tandat
Enthusiast (809 posts)
tandat's avatar
Sep 29, 2007, 7:20 PM
Post #2 of 6
Views: 4086
Shortcut
Quote Reply
Re: [Matthias70] User Toplist In reply to
Hi,

I guess you want this.

Code:
sub {

my $db = $DB->table('Links');
$db->select_options( "GROUP BY LinkOwner","ORDER BY link_count DESC" );
my $sth = $db->select("LinkOwner", "SUM(Hits) as link_count") or die "Query Error: $GT::SQL::error";

my $back;
while (my ($s, $c) = $sth->fetchrow_array) {
if ($c < 7) { next; }
my $url = $CFG->{db_cgi_url} . "/search.cgi?query=;LinkOwner=$s";
$back .= qq|> <a href="$url">$s</a> ($c Einträge) <br />|;
}

return $back;

}

Cheers,

Cheers,

Dat

Programming and creating plugins and templates
Blog
Sep 30, 2007, 12:03 AM
Matthias70
Enthusiast (893 posts)
Sep 30, 2007, 12:03 AM
Post #3 of 6
Views: 4051
Shortcut
Quote Reply
Re: [tandat] User Toplist In reply to
Hi Dat,
that's it. Works fine.
Thanks
Matthias

Matthias
gpaed.de
Sep 30, 2007, 3:03 AM
Matthias70
Enthusiast (893 posts)
Sep 30, 2007, 3:03 AM
Post #4 of 6
Views: 4070
Shortcut
Quote Reply
Re: [tandat] User Toplist In reply to
What do you think?
Is this output possible too

1. User A: 1000 hits in 20 links
2. User B: 900 hits in 35 links
3. User C: 500 hits in 19 links

Thanks
Matthias


Matthias
gpaed.de
Sep 30, 2007, 3:14 AM
tandat
Enthusiast (809 posts)
tandat's avatar
Sep 30, 2007, 3:14 AM
Post #5 of 6
Views: 4045
Shortcut
Quote Reply
Re: [Matthias70] User Toplist In reply to
Hi,

Can you try this?

Code:

sub {

my $db = $DB->table('Links');
$db->select_options( "GROUP BY LinkOwner","ORDER BY link_count DESC" );
my $sth = $db->select("LinkOwner", "SUM(Hits) as hits_count","Count(*) as link_count") or die "Query Error: $GT::SQL::error";

my $back;
while (my ($s, $h,$c) = $sth->fetchrow_array) {
if ($c < 7) { next; }
my $url = $CFG->{db_cgi_url} . "/search.cgi?query=;LinkOwner=$s";
$back .= qq|> <a href="$url">$s</a> ($h hits in $c link(s)) <br />|;
}

return $back;

}

Cheers,

Cheers,

Dat

Programming and creating plugins and templates
Blog
Sep 30, 2007, 3:22 AM
Matthias70
Enthusiast (893 posts)
Sep 30, 2007, 3:22 AM
Post #6 of 6
Views: 4036
Shortcut
Quote Reply
Re: [tandat] User Toplist In reply to
Hi Dat,
this code works fine

just changed
if ($c < 7) { next; }
to
if ($h < 7) { next; }

to change the cut off for number of links and not hits

Thanks
Matthias


Matthias
gpaed.de

Last edited by:

Matthias70: Sep 30, 2007, 3:27 AM