Gossamer Forum
Home : Products : Gossamer Links : Development, Plugins and Globals :

Global to list links without reviews

Quote Reply
Global to list links without reviews
I'm trying to write a global to select 20 random links without reviews. I have worked out the SQL statement required but am having trouble getting it to return anything in Links:

Code:
sub {
# Displays 20 random links with no review
my ($output,$sth,$link);
my $search_db = $DB->table('Links', 'Reviews');
$search_db->select_options ('ORDER BY RAND()', 'LIMIT 20');
$sth = $search_db->select (GT::SQL::Condition->new('isValidated', '=',' Yes', 'Review_LinkID', 'IS', 'NULL'));
while ($link = $sth->fetchrow_hashref) {
if (length $link->{Title} > 26) {
$link->{Titleshort} = substr($link->{Title}, 0, 25) . '...';
}
$output .= Links::SiteHTML::display ('templatename', $link);
}
return $output;
}

I suspect the 'Review_LinkID is NULL' part is the problem, nothing I have tried has worked. Any suggestions appreciated Smile.
Quote Reply
Re: [aus_dave] Global to list links without reviews In reply to
Hi,

The easiest way, would be to make a new .cgi script - say "update_reviews.cgi" in the admin folder, with the following:
Code:
#!/usr/local/bin/perl

use strict;
use lib '/path/to/admin';
use Links qw/$DB $IN $USER $CFG/;
use GT::Mail;
use CGI::Carp qw(fatalsToBrowser);
use Links::Plugins;
use Links;

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

my $sth = $DB->table('Links')->select( ['ID'] ) || die $GT::SQL::error;

while (my $id = $sth->fetchrow) {
my $cnt = $DB->table('Reviews')->count( { Review_LinkID => $id } ) || 0;
$DB->table('Links')->update( { ReviewCount => $cnt }, { ID => $id } ) || die $GT::SQL::error;
print qq|Updated linkID $id with $cnt reviews \n|;
}

Then, setup a cronjob to run each day.

perl /path/to/admin/update_reviews.cgi

Then, be sure to add a new field in the "Links" table via:

Database > Links > Properties > Add Column.

Name: ReviewCount
Type: INT

Then, run this script manually (via SSH), and check to see if the links have had their new ReviewCount field updated correctly.

Then, this global should grab the results you want:

Code:
sub {
# Displays 20 random links with no review
my ($output,$sth,$link);
my $search_db = $DB->table('Links');
$search_db->select_options ('ORDER BY RAND()', 'LIMIT 20');
$sth = $search_db->select ( { isValidated => 'Yes', ReviewCount => '0' } );
while ($link = $sth->fetchrow_hashref) {
if (length $link->{Title} > 26) {
$link->{Titleshort} = substr($link->{Title}, 0, 25) . '...';
}
$output .= Links::SiteHTML::display ('templatename', $link);
}
return $output;
}

Hope that helps :)

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] Global to list links without reviews In reply to
I'll keep that in mind Andy - seems to be a roundabout way to do things though. I can get the exact SQL query I need to work fine in a front end client I use, so I thought it would be simple to rewrite that in Links.

If it's not possible to find NULL records in Links then I'll have to use another way I guess.
Quote Reply
Re: [aus_dave] Global to list links without reviews In reply to
Hi,

What query are you trying to run then? i.e the exact MySQL query. I may be able to help you structure it =)

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] Global to list links without reviews In reply to
Andy, pretty standard apart from the 'is NULL' part:

Code:
select ID, Title from glinks_Links left join glinks_Reviews on Review_LinkID=ID where Review_LinkID is NULL and isValidated='Yes' order by rand() limit 20

I've looked through the Links help and tried a few things like escaping various parts with backslashes, with no luck :(.
Quote Reply
Re: [aus_dave] Global to list links without reviews In reply to
Hi,

This may work:

load_links_without_reviews
Code:
sub {
my $sth = $DB->table('Links')->do_query(qq|select ID,Title from glinks_Links left join glinks_Reviews on Review_LinkID=ID where Review_LinkID is NULL and isValidated='Yes' order by rand() limit 20|);
my @loop;
while (my $hit = $sth->fetchrow_hashref) {
push @loop, $hit;
}

return { links_without_reviews_loop=> \@loop }

}

Code:
<%load_links_without_reviews%>
<%loop links_without_reviews_loop%>
<%include link.html%>
<%endloop%>

Took a while to get it right, but think that will do the job for ya =)

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] Global to list links without reviews In reply to
Thanks Andy, that works perfectly Wink.
Quote Reply
Re: [aus_dave] Global to list links without reviews In reply to
Glad to hear it :)

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!