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

Need help with Drop Down subcategory link list

Quote Reply
Need help with Drop Down subcategory link list
I am trying to make a drop down list for each category that shows all the links in that category's sub-categories.

From reading the boards and resource area I came up with this global:

----------------------------------------------------

sub {
my $tags = shift;
my $cat = $tags->{'Full_Name'};
my $output;
my $sth;
my $link;

use GT::SQL::Condition;

my $search_db = $DB->table('Links','CatLinks','Category');
$search_db->select_options ('GROUP BY LinkID ORDER BY Add_Date DESC Limit 5');
$sth = $search_db->select (['Links.ID', 'Links.Description', 'Links.Title', 'Links.Add_Date', 'Category.Full_Name', 'Category.Name'], GT::SQL::Condition->new(['Full_Name', 'LIKE', $cat .'%'], ['isValidated', '=', 'Yes']));

while ($link = $sth->fetchrow_hashref)
{


# Set the detailed_url
my $detailed_url = "http://mylink.com/cgi-bin/page.cgi?g=Detailed-$link->{'ID'}.html&d=1";

$output .= qq~<option value="$detailed_url"> $link->{'Name'} :: $link->{'Title'} ~;
}

return $output;
}

------------------------------------

This only shows the last 5 links in the category and subcategories. I need it to show all in only the subcategories. The limit 5 makes it so only 5 show up, but if I take it out I get ALL links in the whole database. If I make it limit 20 then it shows the last 20 links in the whole database, you get the idea.

Anyone know how to fix this... basicly I just need it to spit out a list of all subcategory links and url's for whatever category I am in.

Thanks for any help you can give.
Quote Reply
Re: [tyranny] Need help with Drop Down subcategory link list In reply to
Try this:

sub {
my ($all_ids,@list,$output);
my $tags = shift;

$cat_id = $tags->{'ID'};
$all_ids = $DB->table('Category')->children($cat_id);
my $link_db = $DB->table('Links','CatLinks','Category');
$link_db->select_options ("ORDER BY Add_Date DESC"); #Change this to change the order
my $condition = GT::SQL::Condition->new( 'isValidated','=','Yes','CategoryID', 'IN', \@$all_ids);
my $sth = $link_db->select($condition);
while (my $link = $sth->fetchrow_hashref) {
push @list, $link;
}
foreach my $link (@list) {
my $detailed_url = "$CFG->{build_detail_url}/$link->{'LinkID'}$CFG->{build_extension}";

$output .= qq~<option value="$detailed_url">$link->{'Title'}~;
}

return $output;
}

You will need to put this within your <select></select> tags for the drop-down list.

Laura.
The UK High Street
Quote Reply
Re: [afinlr] Need help with Drop Down subcategory link list In reply to
Ok, tried this and got nothing. :(

I've tried everything..... At this point I would be happy with a drop down that just showed the sub-categories of the categories and not the links.

How about that? Anyone have any sub that lists the sub-categories of the category you are in?

Going nuts, tried about 100 different things with nothing working.



:(
Quote Reply
Re: [tyranny] Need help with Drop Down subcategory link list In reply to

Oops, sorry, missed a 'my' out.

Just replace

$cat_id = $tags->{'ID'};

with

my $cat_id = $tags->{'ID'};

Should work now.

Laura.


The UK High Street
Quote Reply
Re: [tyranny] Need help with Drop Down subcategory link list In reply to
If you want the first level subcategories and the list of links in each one you could modify this global:

http://www.gossamer-threads.com/...i?post=222918#222918

I think that there are several others dotted around the forum which are similar.

Laura.
The UK High Street
Quote Reply
Re: [afinlr] Need help with Drop Down subcategory link list In reply to
Nope, got this error...

A fatal error has occured:
GT::SQL::Table (88720): Invalid category id: 960 at /usr/home/clubsite/usr/local/etc/httpd/cgi-bin/linkssql/admin/Links/Category.pm line 295.
I appreciate you trying to help, it's making me crazy. Really if it's easier to just make a list of subcategories in that category that would be better. This goes on a details page if that makes any difference, not on the category page.
Quote Reply
Re: [tyranny] Need help with Drop Down subcategory link list In reply to
Mm, that makes quite a difference. Links can be in several categories so deciding which category you want to show the subcategories of is not straightforward. There is no tag on the detailed page for a category so you need to look up the link in the CatLinks table and see which categories it is in. If you only ever list links in a single category then it should make this simpler. You could try this:

sub {
my ($all_ids,@list,$output);
my $tags = shift;

my $id = $tags->{'ID'};
my $db = $DB->table ('Category','CatLinks');
my $sth = $db->select ( { 'CatLinks.LinkID' => $id }, ['Category.ID', 'Category.Name','Category.Full_Name'] );
while (my ($cat_id,$name,$full_name) = $sth->fetchrow_array) {
$all_ids = $DB->table('Category')->children($cat_id);
my $link_db = $DB->table('Links','CatLinks','Category');
$link_db->select_options ("ORDER BY Add_Date DESC"); #Change this to change the order
my $condition = GT::SQL::Condition->new( 'isValidated','=','Yes','CategoryID', 'IN', \@$all_ids);
my $sth = $link_db->select($condition);
while (my $link = $sth->fetchrow_hashref) {
push @list, $link;
}
foreach my $link (@list) {
my $detailed_url = "$CFG->{build_detail_url}/$link->{'LinkID'}$CFG->{build_extension}";
$output .= qq~<option value="$detailed_url">$link->{'Title'}~;
}
}
return $output;
}

Hope that works! You should have $name available as the name of the category if you want to include this in the dropdown list above the list of links. I'm not quite sure why you would want this - I could see that you might want a list of all the other links in the category on the detail page but all the other links in the subcategories of that category seems a bit strange.

Laura.
The UK High Street
Quote Reply
Re: [afinlr] Need help with Drop Down subcategory link list In reply to
This works! Thank you.



The reason I wanted the subcategory drop down list was that the subcategories for my categories actually are just a list of details about the category item. :)

Let's see if I took you code and made it to list aubcategories only would this work:

sub {
my ($all_ids,@list,$output);
my $tags = shift;

my $id = $tags->{'ID'};
my $db = $DB->table ('Category','CatLinks');
my $sth = $db->select ( { 'CatLinks.LinkID' => $id }, ['Category.ID', 'Category.Name','Category.Full_Name'] );
while (my ($cat_id,$name,$full_name) = $sth->fetchrow_array) {
$all_ids = $DB->table('Category')->children($cat_id);
my $link_db = $DB->table('Links','CatLinks','Category');
$link_db->select_options ("ORDER BY Add_Date DESC"); #Change this to change the order
my $condition = GT::SQL::Condition->new( 'isValidated','=','Yes','CategoryID', 'IN', \@$all_ids);
my $sth = $link_db->select($condition);
while (my $link = $sth->fetchrow_hashref) {
push @list, $link;
}
foreach my $name (@list) {
my $detailed_url = "$CFG->{build_cgi_url}/$name";
$output .= qq~<option value="$detailed_url">$link->{'Full_Name'}~;
}
}
return $output;
}




Don't think this will work.... would it be foreach $cat_id instead?
Quote Reply
Re: [tyranny] Need help with Drop Down subcategory link list In reply to
This is a quick look - haven't tested it so could still be buggy.

sub {
my ($all_ids,@list,$output);
my $tags = shift;
my $id = $tags->{'ID'};
my $db = $DB->table ('Category','CatLinks');
my $sth = $db->select ( { 'CatLinks.LinkID' => $id }, ['Category.ID', 'Category.Name','Category.Full_Name'] );
while (my ($cat_id,$name,$full_name) = $sth->fetchrow_array) {
my $cat_db = $DB->table('Category');
$all_ids = $cat_db->children($cat_id);
$cat_db->select_options('ORDER BY Full_Name');
my $cond = GT::SQL::Condition->new('ID', 'IN', \@$all_ids);
my $sth = $cat_db->select ( $cond, ['Full_Name','Name'] );
while (my ($cat,$heading) = $sth->fetchrow_array) {
my $url = $cat_db->as_url($cat);
$output .= qq~~<option value="$CFG->{build_root_url}/$url">$heading~;
}
}
return $output;
}
The UK High Street
Quote Reply
Re: [afinlr] Need help with Drop Down subcategory link list In reply to
Yeah, still nothing. I keep playing with it. I just can't seem to grasp it. It's right there at the tip of my computer's tongue!

It's not giving an error it's just not pulling up nothing....



sigh. I'm about to give up.
Quote Reply
Re: [tyranny] Need help with Drop Down subcategory link list In reply to
Warned you it might be buggy - sorry! You need to remove the second '~' then hopefully it should work.

Laura.
The UK High Street
Quote Reply
Re: [afinlr] Need help with Drop Down subcategory link list In reply to
Brilliant!

It works... thanks a bunch. Now to just tweak it a bit.

Thanks for the help, I am in your debt.
Quote Reply
Re: [afinlr] Need help with Drop Down subcategory link list In reply to
hi
i really think this is a great global

sub {
my ($all_ids,@list,$output);
my $tags = shift;
my $id = $tags->{'ID'};
my $db = $DB->table ('Category','CatLinks');
my $sth = $db->select ( { 'CatLinks.LinkID' => $id }, ['Category.ID', 'Category.Name','Category.Full_Name'] );
while (my ($cat_id,$name,$full_name) = $sth->fetchrow_array) {
my $cat_db = $DB->table('Category');
$all_ids = $cat_db->children($cat_id);
$cat_db->select_options('ORDER BY Full_Name');
my $cond = GT::SQL::Condition->new('ID', 'IN', \@$all_ids);
my $sth = $cat_db->select ( $cond, ['Full_Name','Name'] );
while (my ($cat,$heading) = $sth->fetchrow_array) {
my $url = $cat_db->as_url($cat);
$output .= qq~<option value="$CFG->{build_root_url}/$url">$heading~;
}
}
return $output;
}



how would i alter it the not show subcat, but all categories the link is associated with?
thanks
Quote Reply
Re: [ajiimd] Need help with Drop Down subcategory link list In reply to
Untested:

sub {
my ($all_ids,@list,$output);
my $tags = shift;
my $id = $tags->{'ID'};
my $db = $DB->table ('Category','CatLinks');
my $sth = $db->select ( { 'CatLinks.LinkID' => $id }, ['Category.ID', 'Category.Name','Category.Full_Name'] );
while (my ($cat_id,$name,$full_name) = $sth->fetchrow_array) {
my $url = $DB->table('Category')->as_url($full_name);
$output .= qq~<option value="$CFG->{build_root_url}/$url">$name~;
}
return $output;
}
Quote Reply
Re: [afinlr] Need help with Drop Down subcategory link list In reply to
perfect, thanks.

how do i make it show in list form

cheers
Quote Reply
Re: [ajiimd] Need help with Drop Down subcategory link list In reply to
Just change this bit

<option value="$CFG->{build_root_url}/$url">$name

to something like this

<li><a href="$CFG->{build_root_url}/$url">$name</a>
Quote Reply
Re: [afinlr] Need help with Drop Down subcategory link list In reply to
perfect, thanks