Gossamer Forum: Products: Gossamer Links: Development, Plugins and Globals: related categories links

User (396 posts)

Aug 4, 2009, 3:30 AM

Post #1 of 25

Shortcut

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.

Aug 4, 2009, 3:51 AM

Veteran / Moderator (18436 posts)

Aug 4, 2009, 3:51 AM

Post #2 of 25

Shortcut

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

Aug 4, 2009, 4:23 AM

User (396 posts)

Aug 4, 2009, 4:23 AM

Post #3 of 25

Shortcut

Re: [Andy] related categories links In reply to

Yes,
To show the links that are in the related categories in the category.html.

Aug 4, 2009, 8:46 AM

Veteran / Moderator (18436 posts)

Aug 4, 2009, 8:46 AM

Post #4 of 25

Shortcut

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

Aug 5, 2009, 1:08 AM

User (396 posts)

Aug 5, 2009, 1:08 AM

Post #5 of 25

Shortcut

Re: [Andy] related categories links In reply to

Thanks it work Smile

, 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.

Aug 5, 2009, 1:52 AM

Veteran / Moderator (18436 posts)

Aug 5, 2009, 1:52 AM

Post #6 of 25

Shortcut

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

Last edited by:

Andy: Aug 5, 2009, 2:43 AM

Aug 5, 2009, 2:03 AM

User (396 posts)

Aug 5, 2009, 2:03 AM

Post #7 of 25

Shortcut

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.

Aug 5, 2009, 2:09 AM

Veteran / Moderator (18436 posts)

Aug 5, 2009, 2:09 AM

Post #8 of 25

Shortcut

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

Aug 5, 2009, 2:16 AM

User (396 posts)

Aug 5, 2009, 2:16 AM

Post #9 of 25

Shortcut

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

Aug 5, 2009, 2:43 AM

Veteran / Moderator (18436 posts)

Aug 5, 2009, 2:43 AM

Post #10 of 25

Shortcut

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};

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

Aug 5, 2009, 5:04 AM

User (396 posts)

Aug 5, 2009, 5:04 AM

Post #11 of 25

Shortcut

Re: [Andy] related categories links In reply to

It give for all the link //index.html without the ID

Aug 5, 2009, 5:12 AM

Veteran / Moderator (18436 posts)

Aug 5, 2009, 5:12 AM

Post #12 of 25

Shortcut

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

Aug 5, 2009, 5:15 AM

User (396 posts)

Aug 5, 2009, 5:15 AM

Post #13 of 25

Shortcut

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'
}
];

Aug 5, 2009, 5:19 AM

Veteran / Moderator (18436 posts)

Aug 5, 2009, 5:19 AM

Post #14 of 25

Shortcut

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

Aug 5, 2009, 5:24 AM

User (396 posts)

Aug 5, 2009, 5:24 AM

Post #15 of 25

Shortcut

Re: [Andy] related categories links In reply to

Its work

, thanks for your time and help

Feb 27, 2017, 3:41 PM

Enthusiast (832 posts)

Feb 27, 2017, 3:41 PM

Post #16 of 25

Shortcut

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

Feb 28, 2017, 12:40 AM

Veteran / Moderator (18436 posts)

Feb 28, 2017, 12:40 AM

Post #17 of 25

Shortcut

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

Feb 28, 2017, 8:00 PM

Enthusiast (832 posts)

Feb 28, 2017, 8:00 PM

Post #18 of 25

Shortcut

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!

Feb 28, 2017, 10:32 PM

Veteran / Moderator (18436 posts)

Feb 28, 2017, 10:32 PM

Post #19 of 25

Shortcut

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

Mar 1, 2017, 1:31 AM

Enthusiast (832 posts)

Mar 1, 2017, 1:31 AM

Post #20 of 25

Shortcut

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!

Mar 1, 2017, 2:20 AM

Veteran / Moderator (18436 posts)

Mar 1, 2017, 2:20 AM

Post #21 of 25

Shortcut

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

Mar 2, 2017, 3:39 AM

Enthusiast (832 posts)

Mar 2, 2017, 3:39 AM

Post #22 of 25

Shortcut

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!

Mar 2, 2017, 3:50 AM

Veteran / Moderator (18436 posts)

Mar 2, 2017, 3:50 AM

Post #23 of 25

Shortcut

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

Mar 2, 2017, 4:33 AM

Enthusiast (832 posts)

Mar 2, 2017, 4:33 AM

Post #24 of 25

Shortcut

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!

Mar 2, 2017, 6:18 AM