Gossamer Forum
Quote Reply
related categories links
How I can get list in a category of all related links, I mean the category have some Related category and I need the link of the Related categories.
Quote Reply
Re: [nir] related categories links In reply to
You mean you want to get the related categories, and then "grab" the links that are in that category, and show them on category.html too?

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] related categories links In reply to
Yes,
To show the links that are in the related categories in the category.html.
Quote Reply
Re: [nir] related categories links In reply to
Ok, took a while to get right (I thought we could just get a list of values as an arrayref direct from $DB, but seems we can't - so had to do it a more long winded way.

Anyhow, this should work:

get_links_from_related_cats
Code:
sub {

my $cat_id = $_[0];

my $sth = $DB->table("CatRelations")->select( ['RelatedID'], { CategoryID => $cat_id } );
my $cats;
while (my $hit = $sth->fetchrow_hashref) {
push @$cats, "$hit->{RelatedID}";
}

my $db_obj = $DB->table('Links','CatLinks','Category');
$db_obj->select_options ("ORDER BY " .$CFG->{build_sort_order_category});

my $cond = GT::SQL::Condition->new('CategoryID', 'IN', $cats);
my $cond2 = GT::SQL::Condition->new('isNew','=','Yes','isValidated','=','Yes');
my $sth = $db_obj->select (['Links.*'], $cond, $cond2 ) || die $GT::SQL::error;

my @loop;
while (my $hit = $sth->fetchrow_hashref) {

if ($CFG->{build_detailed}) { $hit->{detailed_url} = $CFG->{build_detail_url} . "/" . $DB->table('Links')->detailed_url( $hit->{ID} ); }

if ($hit->{isNew} eq "Yes") { $hit->{isNew} = 1; } else { $hit->{isNew} = 0; }
if ($hit->{isPopular} eq "Yes") { $hit->{isPopular} = 1; } else { $hit->{isPopular} = 0; }
if ($hit->{isChanged} eq "Yes") { $hit->{isChanged} = 1; } else { $hit->{isChanged} = 0; }

push @loop, $hit;

}

return { related_cat_links => \@loop }

}


Code:
<%get_links_from_related_cats($ID)%>
<%if related_cat_links.length%>
<%loop related_cat_links%>
<%include link.html%>
<%endloop%>
<%endif%>

Obviously this will only work in category.html.

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] related categories links In reply to
Thanks it workSmile, and Thanks again
Can you show me how I can get the list on the related categories with a loop so I can control its look.
Quote Reply
Re: [nir] related categories links In reply to
Glad it worked. To get a list of the categories that are related to the current category, you would do something like (untested)

work_out_related_cats
Code:
sub {
my $cat_id = $_[0];

my $sth = $DB->table("CatRelations")->select( { CategoryID => $cat_id } );
my @related_cats;
while (my $hit = $sth->fetchrow_hashref) {
my $cat = $DB->table('Category')->select( { ID => $hit->{RelationID} } )->fetchrow_hashref;
$cat->{URL} = $CFG->{build_root_url} . "/" . $DB->table('Category')->as_url( $cat->{Full_Name} ) . "/" . $CFG->{build_index};
if (length $hit->{RelationName} > 0) {
$cat->{Name} = $hit->{RelationName};
}
push @related_cats, $cat;
}

return { related_cat_loop => \@related_cats };
}

Then in category.html:

Code:
<%work_out_related_cats($ID)%>
<%if related_cat_loop.length%>
<%loop related_cat_loop%>
<a href="<%URL%>"><%Name%></a> <br />
<%endloop%>
<%endif%>

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!

Last edited by:

Andy: Aug 5, 2009, 2:43 AM
Quote Reply
Re: [Andy] related categories links In reply to
The results return the name and ID of the current category, I try to change it to RelationName and RelatedID but it didn’t know this value.
Quote Reply
Re: [nir] related categories links In reply to
Sorry, my mistake - please try the edited versions above (I made a few mess ups in the last version =))

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] related categories links In reply to
It return the correct name but it does not return the correct URL , the URL have no ID
Quote Reply
Re: [nir] related categories links In reply to
Oops, please change:

Code:
$cat->{URL} = $CFG->{build_root_url} . "/" . $DB->table('Category')->as_url( $hit->{Full_Name} ) . "/" . $CFG->{build_index};

to

Code:
$cat->{URL} = $CFG->{build_root_url} . "/" . $DB->table('Category')->as_url( $cat->{Full_Name} ) . "/" . $CFG->{build_index};

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] related categories links In reply to
It give for all the link //index.html without the ID
Quote Reply
Re: [nir] related categories links In reply to
What do you see when you put:

<%DUMP related_cat_loop%>

?

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] related categories links In reply to
Dumped value of 'related_cat_loop':
[
{
'Name' => 'aaa',
'URL' => '//index.html'
},
{
'Name' => 'sss',
'URL' => '//index.html'
},
{
'Name' => 'ddd',
'URL' => '//index.html'
}
];
Quote Reply
Re: [nir] related categories links In reply to
Ah, I see the problem - try this:

Code:
sub {
my $cat_id = $_[0];

my $sth = $DB->table("CatRelations")->select( { CategoryID => $cat_id } );
my @related_cats;
while (my $hit = $sth->fetchrow_hashref) {
my $cat = $DB->table('Category')->select( { ID => $hit->{RelatedID} } )->fetchrow_hashref;
$cat->{URL} = $CFG->{build_root_url} . "/" . $DB->table('Category')->as_url( $cat->{Full_Name} ) . "/" . $CFG->{build_index};
if (length $hit->{RelationName} > 0) {
$cat->{Name} = $hit->{RelationName};
}
push @related_cats, $cat;
}

return { related_cat_loop => \@related_cats };
}

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] related categories links In reply to
Its workSmile, thanks for your time and help
Quote Reply
Re: [Andy] related categories links In reply to
Andy,

I tried this "get_links_from_related_cats" global above, but gives an error in many categories as follows (only works for some categories but shows new, updated, popular for all listings).

For example, if you click on "Computer-Technology" category on my site, it will show errors in the browser.

Code:



Failed to execute query: 'SELECT glinks_Links.* FROM glinks_CatLinks,glinks_Category,glinks_Links WHERE glinks_CatLinks.LinkID = glinks_Links.ID AND glinks_CatLinks.CategoryID = glinks_Category.ID AND ((glinks_CatLinks.CategoryID IN NULL) AND (glinks_Links.isNew = 'Yes' AND glinks_Links.isValidated = 'Yes')) ORDER BY Links_PPC_Bid DESC, Links_PPC_Bid DESC, isFeatured DESC,isPick DESC,isNew DESC,isPopular DESC,Title': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL) AND (glinks_Links.isNew = 'Yes' AND glinks_Links.isValidated = 'Yes')) ORD' at line 1 at (eval 55) line 16.

Thanks!

Last edited by:

socrates: Feb 27, 2017, 3:46 PM
Quote Reply
Re: [socrates] related categories links In reply to
Are you passing the category ID along to the global? Doesn't look it to me:

Code:
glinks_CatLinks.CategoryID IN NULL

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] related categories links In reply to
I use the following loop in the category page that will pass the category ID to pull the results - isn't it?

<%get_links_from_related_cats($ID)%>
<%if related_cat_links.length%>
<%loop related_cat_links%>
<%include link.html%>
<%endloop%>
<%endif%>

Thanks!
Quote Reply
Re: [socrates] related categories links In reply to
Mmm yeah, that looks ok. Can you add some debug in so I can see the value of $ID being passed in?

Code:
my $cat_id = $_[0];

Add:

Code:
print $IN->header;
print "CAT ID: $cat_id <br>\n";

Then go back to the category page (in dynamic), and run it to see what gets outputted (at the very top of the page)

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] related categories links In reply to
If prints the ID's like so "CAT ID: 5" - for all categories including the categories for which it shows that above error message I posted.

Also, I noticed in that in the above error I posted why is it saying it is MySQL error - did you notice that?
Code:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL) AND (glinks_Links.isNew = 'Yes' AND glinks_Links.isValidated = 'Yes')) ORD' at line 1 at (eval 55) line 18.
Thanks!
Quote Reply
Re: [socrates] related categories links In reply to
Can you try running a Database > Repair Tables, and see what that brings up? I'm wondering if your data is corrupt (you had a related category ID, that doesn't exist any more). You could also try this global that would do a bit more checking:

Code:
sub {
my $cat_id = $_[0];

my $sth = $DB->table("CatRelations")->select( { CategoryID => $cat_id } );
my @related_cats;
while (my $hit = $sth->fetchrow_hashref) {
my $cat = $DB->table('Category')->select( { ID => $hit->{RelatedID} } )->fetchrow_hashref || {};
if (!$cat->{ID}) {
print $IN->header;
print "CANT FIND RELATION FOR $hit->{RelatedID}, $cat_id <br>\n";
} else {
$cat->{URL} = $CFG->{build_root_url} . "/" . $DB->table('Category')->as_url( $cat->{Full_Name} ) . "/" . $CFG->{build_index};
if (length $hit->{RelationName} > 0) {
$cat->{Name} = $hit->{RelationName};
}
}
push @related_cats, $cat;
}

return { related_cat_loop => \@related_cats };
}

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] related categories links In reply to
Thanks, Andy - I repaired the tables and the message said that it repaired one of the categories which had 205 links and now it has 202 or something like that. After that, I first tried again with that original global get_links_from_related_cats and still showed the same errors. Then I posted your new code in the global - good news is that it no longer shows that MySql error anymore for the same categories where it showed the error earlier but now what - how do I get it to show the related links? Thanks!
Quote Reply
Re: [socrates] related categories links In reply to
Hi,

Good to hear :)

Quote:
how do I get it to show the related links

This is for related *categories*, not *links* Whistle You can't really load the related links, apart from going through each related category, and then getting the links from it (and passing it back as a global). However, I wouldn't recommend that - as if you have 10 related categories, with 10 links in each, that would add another 100 links to the page!

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] related categories links In reply to
Andy, No - look above, there are 2 globals you provided for Nir above - see all the way above where Nir first asked for specifically to show links from related cats and even the name of the global - the global you gave first (way up) "get_links_from_related_cat" did actually work for me and it showed links from related categories whenever it worked in some categories (but it gave that MySql error in some other categories).

Then, there is another global you gave after a few posts down where he asked for a global to show related category name - work_out_related_cats which only provided the related category name (that is working fine for me too and showing related category name) but I wanted the first one on top "get_links_from_related_cat" to show some links from related categories. However, you are right in that on some pages it did show many links from related categories.

Anyway, my idea is/was to show on any category page, if it is related to another category then show a few links from that related category like so - CategoryA is related to CategoryB and on the CategoryA page, I want to show 3-5 links from CategoryB and vice versa. Thanks!
Quote Reply
Re: [socrates] related categories links In reply to
Ooooh ok. Well in that case, in the other global find:

Code:
while (my $hit = $sth->fetchrow_hashref) {
push @$cats, "$hit->{RelatedID}";
}

...and change to:

Code:
while (my $hit = $sth->fetchrow_hashref) {
push @$cats, $hit->{RelatedID};
}
if (int @$cats < 1) { return }

The reason you were getting the error, is because not all categories would have related categories to find, thus $cats is empty (and gives an error, as you are passing NULL through)

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!