Gossamer Forum: Products: Gossamer Links: Discussions: Updating Links field from CatLinks

Dec 1, 2001, 8:46 AM

carfac

Veteran (1240 posts)

Dec 1, 2001, 8:46 AM

Post #1 of 4

Shortcut

Updating Links field from CatLinks

Hi:

In the OLD links, I found I had a need to update whole categories at a time. To that end, Alex wrote me a quick little perl script to look at CatLinks, and extract the Category Number, and insert that into a filed I added to Links (CategoryNum)

Anyway, I have not run that in a while, but I needed to this morning. It did not seem to run under the newest version of Links (I run 2.0.4)

Anyway, I was hoping someone might take a look at this really minor script and see if they can spot why it is not working. It does seem to run- it just does not insert anything into Links:CategoryNum....

Code:
sub update_categories { 
# -------------------------------------------------------- 

        my $link_db = $DB->table('Links'); 
        my $catlink_db = $DB->table('CatLinks'); 
        my $sth = $link_db->select ( ['ID'] ); 
        while ( my ($id) = $sth->fetchrow_array ) { 
                my $sth2 = $catlink_db->select ( { LinkID => $id }, ['CategoryID'] ); 
                my ($cid) = $sth2->fetchrow_array; 
                $link_db->update ( { CategoryNum => $cid }, { ID => $id } ); 
                                return; 

        } 
   print $IN->header(); 
   print "Done Updating Links!\n\n"; 
} 


1;

Thank you!

dave
dave

Big Cartoon DataBase
Big Comic Book DataBase

Dec 1, 2001, 5:08 PM

Alex

Administrator (9387 posts)

Dec 1, 2001, 5:08 PM

Post #2 of 4

Shortcut

Re: [carfac] Updating Links field from CatLinks In reply to

Was the return; line in there before? I think that might be the problem.

Cheers,

Alex
--
Gossamer Threads Inc.

Dec 1, 2001, 11:23 PM

dearnet

Enthusiast (907 posts)

Dec 1, 2001, 11:23 PM

Post #3 of 4

Shortcut

Re: [carfac] Updating Links field from CatLinks In reply to

Hello Dave!

Ah, I was thinking to do something like this. Does this work? How do you run it?

I think it could be a good solution for people who wants to export Links table out to excel or access database in a text format. Then run this little script to sysncronise!

Do you run by inserting it into the existing scripts like admin.cgi or?

This kind of little tools of export - import is very much necessary. It helps to work with other offline programs.

Thanks in advance.

Dec 3, 2001, 9:52 AM

carfac

Veteran (1240 posts)

Dec 3, 2001, 9:52 AM

Post #4 of 4

Shortcut

Re: [rajani] Updating Links field from CatLinks In reply to

Hi:

First off.... to Alex: that did it! That darn return; was the culprit, thank you!

rajani:

OK, first you need to remove that "return;"!!!!! Then, the above code needs to be run in a script, like add.cgi or anything else. It will NOT run in SQLMonitor!

If you do not change anything, the only think you need to do is add the field "CategoryNum" to Links. Then, run the script, and all your links will have the Category Number there. Also keep in mind, you CANNOT have a link in multiple cagegories.... well, you CAN, but you will not get all categories listed in CategoryNum.

OK, if you (like I do!!!) build stuff in Excel, then import it into Links, you will then need to take CategoryNum from Links and stick that into CatLinks. Do that with this (and this can run in SQL Monitor):

Code:
insert into CatLinks (CategoryID,LinkID) 
Select XXXX,ID from Links  
where Links.CategoryNum = 'XXXX'

Where BOTH instances of XXXX are the categoryNum. Note, also, that if you already have 10 links in Category 12, and you add 10 more, you will have 30, not twenty!!!! That is because the code will re-insert the first 10 AGAIN... So, if you are gonna add to an existing category, erase all the links from CatLinks first!!!

Sounds complicated, but do it a couple times, and you will have it down! If you have problems, let me know, I will get you through it.... I do it every day!

Dave
dave

Big Cartoon DataBase
Big Comic Book DataBase