Gossamer Forum
Home : Products : Gossamer Links : Discussions :

Top User Submitted ?

Quote Reply
Top User Submitted ?
Hello all,

I have completed database for stories section by using Links SQL 2.1.0, is there any global tag/plugins that will list users who has submitted the most stories in my web ?

Please help ...
Quote Reply
Re: [reenee] Top User Submitted ? In reply to
Not that I know of, but the SQL you'd need to execute would be:

select count(LinkOwner) as LOWN, LinkOwner from prefix_Links
group by LinkOwner order by LOWN Desc

Not sure how to stuff this into a set of $DB-> commands, though. I'd have to sit down with the help docs, maybe someone else can.


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.
Quote Reply
Re: [pugdog] Top User Submitted ? In reply to
>>
select count(LinkOwner) as LOWN, LinkOwner from prefix_Links
group by LinkOwner order by LOWN Desc
<<

The column you'd need is Link_Owner. The code, something like:

my $table = $DB->table('Links');
$table->select_options("GROUP BY Link_Owner", "ORDER BY LOWN DESC");
my $sth = $table->select( 'COUNT(Link_Owner) AS LOWN', 'Link_Owner' );

Last edited by:

Paul: Mar 26, 2002, 10:11 AM
Quote Reply
Re: [Paul] Top User Submitted ? In reply to
In my tables it's "LinkOwner" ....


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.
Quote Reply
Re: [pugdog] Top User Submitted ? In reply to
Wonder where I got Link_Owner from.
Quote Reply
Re: [Paul] Top User Submitted ? In reply to
Errrr...Hi guys ? Any solution or idea ? I really dont understand what both of you talking about ? Wink


Please help ...
Quote Reply
Re: [reenee] Top User Submitted ? In reply to
The code sample is in post 3, but as pugdog said you'll need to use LinkOwner.

The code isn't tested but I think it may work, you'll obviously need to add to it (return the output from the query).

Last edited by:

Paul: Mar 27, 2002, 2:23 PM
Quote Reply
Re: [Paul] Top User Submitted ? In reply to
Hi Paul,

Thanks for reply, Is it correct ? i didnt test yet.

Code:
top_submit =>
sub {
my $tags = shift;
my $table = $DB->table('Links');
$table->select_options ("GROUP BY Link_Owner", "ORDER BY LOWN DESC",'LIMIT 5');
my $sth = $table->select('COUNT(Link_Owner) AS LOWN', 'Link_Owner');
my $output = '';
while (my $link = $sth->fetchrow_hashref) {
$output .= Links::SiteHTML::display('link', $link);
}
return $output;
}
Quote Reply
Re: [reenee] Top User Submitted ? In reply to
Yeah that looks like it should work (fingers crossed).
Quote Reply
Re: [Paul] Top User Submitted ? In reply to
Its doent work, Paul Tongue
Please correct me ... thank you ..Smile
Quote Reply
Re: [reenee] Top User Submitted ? In reply to
I can't see anything wrong with it.

Try changing:

my $sth = $table->select('COUNT(Link_Owner) AS LOWN', 'Link_Owner');

to

my $sth = $table->select('COUNT(Link_Owner) AS LOWN', 'Link_Owner') or die $GT::SQL::error;

....to see if the sql query is the problem.
Quote Reply
Re: [Paul] Top User Submitted ? In reply to
Hi Paul,

Still have an error :

Code:
A fatal error has occured:Failed to execute query: 'SELECT COUNT(Link_Owner) AS LOWN,Link_Owner FROM storiesLinks
GROUP BY Link_Owner
ORDER BY LOWN DESC
LIMIT 5' Reason: Unknown column 'Link_Owner' in 'field list' at (eval 15) line 5.

Please enable debugging in setup for more details.

My code is:

Code:
sub {
my $tags = shift;
my $table = $DB->table('Links');
$table->select_options ("GROUP BY Link_Owner", "ORDER BY LOWN DESC",'LIMIT 5');
my $sth = $table->select('COUNT(Link_Owner) AS LOWN', 'Link_Owner') or die $GT::SQL::error;
my $output = '';
while (my $link = $sth->fetchrow_hashref) {
$output .= Links::SiteHTML::display('link', $link);
}
return $output;
}


Please help ...
Quote Reply
Re: [reenee] Top User Submitted ? In reply to
>>Reason: Unknown column 'Link_Owner' <<

Remember I said to use LinkOwner as pugdog pointed out.