Hi Andy,
forgot, you are looking for ideas
get links by owner
my $user = $_[0];
my $sth = $DB->table('Links')->select( { LinkOwner => $user, isValidated => 'Yes', Build_Detailed_Page => 1 } ) || return $GT::SQL::error;
my $back;
while (my $hit = $sth->fetchrow_hashref) {
$back .= qq|<p><a href="$CFG->{build_root_url}/Detailed/$hit->{ID}.html">$hit->{Title}</a></p>|;
}
return $back;
}
Sitemap
# -------------------------------------------------------------------
my $tags = shift;
my $cat_db = $DB->table('Category');
$cat_db->select_options ('ORDER BY priority');
my $sth = $cat_db->select ( { FatherID => 0}, ['Full_Name','ID','Name'] );
my $output=qq~<table><tr valign=top><td width="25%"><table>~;
my $i=0;
while (my ($root_cat,$ID,$heading) = $sth->fetchrow_array) {
$i++;
my $url1 = $cat_db->as_url($root_cat);
$output.= qq~<tr><td colspan=2><font size=-1><b><a href="$CFG->{build_root_url}/$url1/index.html" class="toc"><br/>$heading</a></b></font></td></tr>~;
$cat_db->select_options ('ORDER BY Full_Name');
my $sth2 = $cat_db->select ( { FatherID => $ID}, ['Full_Name','ID','Name'] );
while (my ($cat,$id,$heading1) = $sth2->fetchrow_array) {
# my $link_count = $DB->table('CatLinks')->count ( {CategoryID => $id});
my $url = $cat_db->as_url($cat);
$output .= qq~<tr><td> </td><td><font size=-2><a href="$CFG->{build_root_url}/$url/index.html" class="toc">$heading1</a></font></td></tr>~;
$cat_db->select_options ('ORDER BY Full_Name');
my $sth3 = $cat_db->select ( { FatherID => $id}, ['Full_Name','ID','Name'] );
while (my ($cat2,$id2,$heading2) = $sth3->fetchrow_array) {
}
}
if (($i eq '13')or ($i eq '19') or ($i eq '33') or ($i eq '40')){ $output.=qq~</table></td><td width="25%"><table align=top>~;}
}
$output.="</table></td></tr></table>";
return $output;
}
Top 100 Links
my $tags = shift;
my $table = $DB->table('Links');
$table->select_options ('ORDER BY Hits DESC', 'LIMIT 100');
my $sth = $table->select;
my @output;
while (my $link = $sth->fetchrow_hashref) {
push (@output, $link);
}
return { top100_loop => \@output };
}
User download Top
my $db = $DB->table('Links');
$db->select_options( "GROUP BY LinkOwner","ORDER BY hits_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 ($h < 500) { next; }
my $url = $CFG->{db_cgi_url} . "/search.cgi?query=;LinkOwner=$s";
$back .= qq|> <a href="$url">$s</a> - $h Downloads in $c Einträgen <br />|;
}
return $back;
}
User Review Top 10
my $db = $DB->table('Reviews');
$db->select_options( "GROUP BY Review_Owner","ORDER BY review_count DESC","LIMIT 5");
my $sth = $db->select("Review_Owner", "COUNT(*) as review_count") or die "Query Error: $GT::SQL::error";
my $back;
while (my ($s, $c) = $sth->fetchrow_array) {
my $url = $CFG->{db_cgi_url} . "/review.cgi?username=$s";
$back .= qq| <a href="$url">$s</a> ($c Kommentare) <br />|;
}
return $back;
}
User listings
# normal links
my $link_db = $DB->table ('Links');
$link_db->select_options ('ORDER BY ID DESC','LIMIT 3') || die $GT::SQL::error;
my $sth = $link_db->select( { isValidated => "Yes", LinkOwner => $USER->{Username} } ) || die $GT::SQL::error;
my @links;
# just normal links.
while (my $link = $sth->fetchrow_hashref) {
push @links, $link;
}
# ratings
my $link_db = $DB->table ('Links');
$link_db->select_options ('ORDER BY Rating DESC', 'LIMIT 10') || die $GT::SQL::error;
my $sth = $link_db->select( { isValidated => "Yes", LinkOwner => $USER->{Username} } ) || die $GT::SQL::error;
my @links2;
# just top rated links.
while (my $link = $sth->fetchrow_hashref) {
push @links2, $link;
}
# ratings
my $link_db = $DB->table ('Links');
$link_db->select_options ('ORDER BY Hits DESC', 'LIMIT 10') || die $GT::SQL::error;
my $sth = $link_db->select( { isValidated => "Yes", LinkOwner => $USER->{Username} } ) || die $GT::SQL::error;
my @links3;
# just top rated links.
while (my $link = $sth->fetchrow_hashref) {
push @links3, $link;
}
return { user_links => \@links , user_links_by_hit => \@links3, user_links_by_rating => \@links2 }
}
random text
my @list = ('text1', 'text2');
return $list[rand @list];
}
Matthias
gpaed.de
forgot, you are looking for ideas
get links by owner
Code:
sub { my $user = $_[0];
my $sth = $DB->table('Links')->select( { LinkOwner => $user, isValidated => 'Yes', Build_Detailed_Page => 1 } ) || return $GT::SQL::error;
my $back;
while (my $hit = $sth->fetchrow_hashref) {
$back .= qq|<p><a href="$CFG->{build_root_url}/Detailed/$hit->{ID}.html">$hit->{Title}</a></p>|;
}
return $back;
}
Sitemap
Code:
sub { # -------------------------------------------------------------------
my $tags = shift;
my $cat_db = $DB->table('Category');
$cat_db->select_options ('ORDER BY priority');
my $sth = $cat_db->select ( { FatherID => 0}, ['Full_Name','ID','Name'] );
my $output=qq~<table><tr valign=top><td width="25%"><table>~;
my $i=0;
while (my ($root_cat,$ID,$heading) = $sth->fetchrow_array) {
$i++;
my $url1 = $cat_db->as_url($root_cat);
$output.= qq~<tr><td colspan=2><font size=-1><b><a href="$CFG->{build_root_url}/$url1/index.html" class="toc"><br/>$heading</a></b></font></td></tr>~;
$cat_db->select_options ('ORDER BY Full_Name');
my $sth2 = $cat_db->select ( { FatherID => $ID}, ['Full_Name','ID','Name'] );
while (my ($cat,$id,$heading1) = $sth2->fetchrow_array) {
# my $link_count = $DB->table('CatLinks')->count ( {CategoryID => $id});
my $url = $cat_db->as_url($cat);
$output .= qq~<tr><td> </td><td><font size=-2><a href="$CFG->{build_root_url}/$url/index.html" class="toc">$heading1</a></font></td></tr>~;
$cat_db->select_options ('ORDER BY Full_Name');
my $sth3 = $cat_db->select ( { FatherID => $id}, ['Full_Name','ID','Name'] );
while (my ($cat2,$id2,$heading2) = $sth3->fetchrow_array) {
}
}
if (($i eq '13')or ($i eq '19') or ($i eq '33') or ($i eq '40')){ $output.=qq~</table></td><td width="25%"><table align=top>~;}
}
$output.="</table></td></tr></table>";
return $output;
}
Top 100 Links
Code:
sub { my $tags = shift;
my $table = $DB->table('Links');
$table->select_options ('ORDER BY Hits DESC', 'LIMIT 100');
my $sth = $table->select;
my @output;
while (my $link = $sth->fetchrow_hashref) {
push (@output, $link);
}
return { top100_loop => \@output };
}
User download Top
Code:
sub { my $db = $DB->table('Links');
$db->select_options( "GROUP BY LinkOwner","ORDER BY hits_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 ($h < 500) { next; }
my $url = $CFG->{db_cgi_url} . "/search.cgi?query=;LinkOwner=$s";
$back .= qq|> <a href="$url">$s</a> - $h Downloads in $c Einträgen <br />|;
}
return $back;
}
User Review Top 10
Code:
sub { my $db = $DB->table('Reviews');
$db->select_options( "GROUP BY Review_Owner","ORDER BY review_count DESC","LIMIT 5");
my $sth = $db->select("Review_Owner", "COUNT(*) as review_count") or die "Query Error: $GT::SQL::error";
my $back;
while (my ($s, $c) = $sth->fetchrow_array) {
my $url = $CFG->{db_cgi_url} . "/review.cgi?username=$s";
$back .= qq| <a href="$url">$s</a> ($c Kommentare) <br />|;
}
return $back;
}
User listings
Code:
sub { # normal links
my $link_db = $DB->table ('Links');
$link_db->select_options ('ORDER BY ID DESC','LIMIT 3') || die $GT::SQL::error;
my $sth = $link_db->select( { isValidated => "Yes", LinkOwner => $USER->{Username} } ) || die $GT::SQL::error;
my @links;
# just normal links.
while (my $link = $sth->fetchrow_hashref) {
push @links, $link;
}
# ratings
my $link_db = $DB->table ('Links');
$link_db->select_options ('ORDER BY Rating DESC', 'LIMIT 10') || die $GT::SQL::error;
my $sth = $link_db->select( { isValidated => "Yes", LinkOwner => $USER->{Username} } ) || die $GT::SQL::error;
my @links2;
# just top rated links.
while (my $link = $sth->fetchrow_hashref) {
push @links2, $link;
}
# ratings
my $link_db = $DB->table ('Links');
$link_db->select_options ('ORDER BY Hits DESC', 'LIMIT 10') || die $GT::SQL::error;
my $sth = $link_db->select( { isValidated => "Yes", LinkOwner => $USER->{Username} } ) || die $GT::SQL::error;
my @links3;
# just top rated links.
while (my $link = $sth->fetchrow_hashref) {
push @links3, $link;
}
return { user_links => \@links , user_links_by_hit => \@links3, user_links_by_rating => \@links2 }
}
random text
Code:
sub { my @list = ('text1', 'text2');
return $list[rand @list];
}
Matthias
gpaed.de