Gossamer Forum
Home : Products : Gossamer Links : Discussions :

List links in a category, with extra info grabbed from another link?

Quote Reply
List links in a category, with extra info grabbed from another link?
Hi all,

I want to take a new approach for my "buy now" link implementation (see first post here), and could really use some help in creating a global. I could also use anyone's comments on whether what I'm proposing is even an intelligent path to take...

I've created some categories and links that follow this format:

Products (cat)

- Specific Product Name (link)

Buy Now Links (cat)

- Specific Product Name (sub-cat)

- - Links to retailers that sell that specific product (links - Title = Retailer name, URL = URL of specific product page at retailer's site)

Retailers (cat)

- Country name (sub-cat)

- - Links to retailers in that country (links - Title = Retailer name, contains custom columns - campaign # and banner #)

As you can see, with the Buy Now Links category, I've created a tree that's similar to the Products category, just listing what's a link in Products as a sub-category in Buy Now Links. This was the simplest solution I could find to allow a product to have multiple links associated with it. note: My original implementation did this with one custom column in each link, containing comma-separated values and newlines (Retailer1,URL1\nRetailer2,URL2\n), which were brought out by using split() twice - once for the newlines, and a second time for the commas, to get those values out. This worked, but would not allow non-moderators (ie Retailers) to add/modify buy now links.

The Retailers category exists as a central place to store information needed for links to retailers (the retailer's country, description, campaign #, and banner #), without having it exist redundantly in all the links in Buy Now Links. Buy Now Links will contain many links of the same Title (retailer name) with different URLs (product page on retailer's site), since each retailer presumably sells lots of different products!

When a user looks at the detailed page for a Specific Product page under Products, I'd like to call a global that does the following:

1. Search the Buy Now Links category for a sub-category that contains the Specific Product Name (the Title of the current link).

2. If it finds a match, then for each of the links under that sub-category, use it's Title (Retailer name) to search the Retailers category for the Retailer (link) it refers to.

3. When it finds the retailer in the Retailers category, return the sub-category it's located in (Country name), it's description, it's campaign #, and it's banner # (at this point, we should also return the Title and URL of the link found in the Buy Now Links category (the URL of the link in Buy Now Links category will be different from the URL of the link in the Retailers category)).

I'd like to be able to access the global with something like this in the detailed template:

<%buynowlinks('<%Title%>')%>
<%loop Buy_Now_Links_Loop%>
<%title%><%country%><%description%><%campaign_num%><%banner_num%><%URL%>
<%endloop%>

Of course I'll display things differently inside the loop, but those are the variables I want to display and need for generating my buy now link code that works with AdvertPro.

What I particularly like about this approach is that I can easily allow editors (even retailers and regular visitors/volunteers!) to add/remove/modify buy now links just as easily as adding a link to the directory, and have it go through the regular validation process. I also like that the retailer's country, description, banner #, and campaign # are stored in one database entry, rather than manually inserted in every single buy now link. The only pain I foresee will be adding a new category in Buy Now Links every time I add a new product in Products (well, assuming I want to have buy now links for that new product). Perhaps that could be automated?

I hope to have around 250 products, 40 retailers, and about 10 retailers (buy now links) for any given product. When I get lots of hits in the Products category, will this global cause performance problems? It seems like a lot of database access, but I'm no expert at mySQL... Should these queries be rather quick? Is there a better way of acheiving the end result I'm looking for?

Thank you in advance for your help!!

Stephen

Last edited by:

biketrials: Jul 9, 2003, 12:36 AM
Quote Reply
Re: [biketrials] List links in a category, with extra info grabbed from another link? In reply to
I just noticed GT::SQL::Relation in the Module Index... Should I try to break out the Retailer category into it's own database so that I can create a relation between the new Retailer database and the regular Links database? This would make it just like the 'Company' and 'Employees' relationship example in the documentation.

Is there any way to create relations within one table?

I feel my brain is about to explode... No more late night programming for me. Anybody have a clue as to how to put this together properly?

Thanks!
Quote Reply
Re: [biketrials] List links in a category, with extra info grabbed from another link? In reply to
Hi,

I'm not really sure what the best way is to tackle this. I suppose that if you keep all your different links in the links table and use a lot of <%if...%> statements in your templates then you have the advantage that you don't need to do much extra programming to allow your shops to add their information and individual product information - you will still be able to use all the core functions that are built into Links (it never even occurred to me to do this but I do think that there are other people in the forum using this method). If you put the different links in different tables (which is what I would do) then some bits you might need will no longer work for the other tables - eg. validate, search - without quite a bit of work. There has been some discussion about being able to easily add new tables and I think that Pugdog? may have been doing some work on this but I don't think anything is ready to use.

I have used the Links table for the Retailers and have added a Products database which has a foreign key relation to the Links database. If you go this way I would be happy to help you with any bits that you get stuck on that I've already tackled. However, I don't allow retailers to add products themselves - I import them using xml.

Laura.
The UK High Street
Quote Reply
Re: [biketrials] List links in a category, with extra info grabbed from another link? In reply to
Hi,

It would be better to do it in separate tables I think. Create a 'Retailers' table, and a 'RetailerLinks' table. Retailers would look like:

retail_id
retail_name
retail_url
retail_address
..

RetailerLinks would look like:

rl_id
rl_url
retail_id_fk
link_id_fk

where rl_id is an auto increment primary key, rl_url is a url specific to that product, retail_id_fk is the id to the retailer, and link_id_fk is the id to the link.

If you set them up with foreign keys, then when you add/remove retailers or links, it will properly clean up the retailerlinks table.

Then edit the db_nav.html template so that your new tables shows up in the editor (in order to manage things and add/remove retailers).

Finally, you'll just need a global that does:

my $retailers = $DB->table('Retailers', 'RetailerLinks')->select( { link_id_fk => $link_id })->fetchall_hashref;
foreach my $retailer (@$retailers) {
$output .= "Buy it at $retailer->{retail_name} with this url $retailer->{rl_url}";
}
return $output;

Hope this gives you a place to start from. =)

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] List links in a category, with extra info grabbed from another link? In reply to
Thanks, Alex, your approach makes a lot of sense to me. I have some more questions, though:

1. How do I create the new tables with the foreign key relations? I'm familiar with both mySQLMan and creating tables through SSH using mysql (piping the table in from a file), but not with foreign keys. Looking at mysql.com didn't shed too much more light on the subject.

2. How do I get LinksSQL to recognize the tables? Isn't there some kind of DEF file that needs to be created?

Is the answer to both of these questions to use GT::SQL::Creator? It looks really complicated. Where would I put the code to then call it, since I'd only be doing this once per table?

If you did the RetailerLinks table for me, I'm sure I could handle the Retailers table! :-)

Thank you for your help, you're incredible!

Stephen
Quote Reply
Re: [biketrials] List links in a category, with extra info grabbed from another link? In reply to
Hi,

Yes, you want to use GT::SQL::Creator to do this. Create something like the following:

Code:
use lib '/path/to/admin';
use Links qw/$DB/;

Links::init('/path/to/admin');
my $c = $DB->creator('RetailerLinks');
$c->cols([
rl_id => { type => 'INT', not_null => 1 },
rl_url => { type => 'CHAR', size => 255 },
retail_id_fk => { type => 'INT', not_null => 1 },
link_id_fk => { type => 'INT', not_null => 1}
]);
$c->pk('rl_id'); # Set primary key
$c->ai('rl_id'); # Make it auto incremenet
$c->index({
retail_ndx => ['retail_id_fk'],
link_ndx => ['link_id_fk']
}); # Setup indexes for faster searches
$c->fk({
Links => { ID => 'link_id_fk' },
Retailers => { retail_id => 'rl_id' },
}); # Setup foreign keys
$c->create or die "Create failed: $GT::SQL::error";

Have a look at admin/Links/SQL.pm for examples on how to use GT::SQL::Creator (as that is where all the Links SQL tables are setup).

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] List links in a category, with extra info grabbed from another link? In reply to
Thanks for your help, Alex,

There's no question in here for you, I'm just letting you (and anyone else who might use this code) know that you had a goof in the table creation code and the global.

You wrote:

$c->fk({
Links => { ID => 'link_id_fk' },
Retailers => { retail_id => 'rl_id' },

}); # Setup foreign keys

When it should've been:

$c->fk({
Links => { link_id_fk => 'ID' },
Retailers => { rl_id => 'retail_id' },
}); # Setup foreign keys


The format on fk() goes...

$FOREIGN_TABLE_NAME => {$LOCAL_TABLE_COL_1 => $FOREIGN_TABLE_COL_1}


Another healthy thing to mention is that the table without the foreign keys needs to be created first, so the table with the foreign keys can properly link to it. I'm sure this is obvious to mySQL experts, but I'm not one of 'em! :-)

Also, the global Alex wrote needs to initialize $output with something like:

my $output = '';

before foreach. The global won't compile otherwise.

If anyone's interested, to create the tables, I created a PL file starting off with:

#!/usr/bin/perl -w
print "Content-type: text/html\n\n";

print qq~
<html>
<body>
<center><h4>Trying to create tables...</h4></center>

~;

.. and then the code for my table, and then the code for the second table that Alex posted (with my foreign key fix!).

It's all working beautifully, and all I need to do is some further customization and coding based on this foundation.

Thank you so much for your help!

Stephen