Gossamer Forum
Home : Products : Gossamer Links : Discussions :

Short Script Not Updating Database

Quote Reply
Short Script Not Updating Database
I wrote a short script to pull the data from a field ("Director") in Links to populate a new table called People. Only it is not pulling the field Director. I am not sure what the correct syntax is. Can you take a look and see what is wrong?

#!/usr/bin/perl
# ==================================================================
# Links SQL - enhanced directory management system


###### Sends all Directors names to db People

use strict;
use lib '/hd2/web/b/bcdb/public_html/bcdb/admin';
use Links qw/$IN $DB $CFG $USER/;
Links::init ('/hd2/web/b/bcdb/public_html/bcdb/admin');
Links::init_user();

local $SIG{__DIE__} = \&Links::fatal;

update_people();

sub update_people {
# --------------------------------------------------------

my $link_db = $DB->table('Links');
my $catlink_db = $DB->table('People');
my $sth = $link_db->select ( ['ID'] );
while ( my ($id) = $sth->fetchrow_array ) {

my $desc = $sth->{'Director'};
$catlink_db->add ( { Name => $desc }, { Job => "Director" } );

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


1;


FWIW, yes, I know I can just copy this across.... I need to manipulate the data before writting it to People, which is why I want to run a script. I have just left that part out until I get it writing correctly!

Thanks!

Dave
dave

Big Cartoon DataBase
Big Comic Book DataBase
Quote Reply
Re: [carfac] Short Script Not Updating Database In reply to
Hi,

So what exactly are you trying to do? Grab all the "distinct" Directors values from the Links table, and then put them into the "People" table?

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] Short Script Not Updating Database In reply to
Does not HAVE to be distinct- I have a filter to do that. I just want to grab the whole field, and write that over to the new table. Once I do that, once I know I am grabbing the Director field, and then writing that to People, I will put the fliters I need in between grabbing and writing, so I get the info I need.

Specifically, the Director filed will have more than one name per entry, so I will need to split by my delimiter, and then make each name a new entry into People. I have code that will do that, no problem. My problem here seems to be that "my $desc = $sth->{'Director'};" does NOT seem to be grabbing the director field. In my tests, the loop is working, but the "Name" field in People is blank.
dave

Big Cartoon DataBase
Big Comic Book DataBase
Quote Reply
Re: [carfac] Short Script Not Updating Database In reply to
You probably want something like this:#

Code:
sub update_people {

my $sth = $DB->table('Links')->select( ['Director'] );

while (my $director = $sth->fetchrow) {

foreach (split /,/, $director) {
if ($DB->table('People')->count( { Director => $_, Job => "Director" } ) < 1) {
$DB->table('People')->add( { Director => $_, Job => "Director" } ) || die $GT::SQL::error;
}
}
}

}

This assues the "Director" field is split up with , (i.e for when you have more than 1 director)

Hope that helps

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] Short Script Not Updating Database In reply to
Andy:

As always, ytou are immensly helpful! I will make this work! Thank you!
dave

Big Cartoon DataBase
Big Comic Book DataBase
Quote Reply
Re: [carfac] Short Script Not Updating Database In reply to
Andy:

Hey- I got it working- thanks. However, it only runs for the first 255 records then stops. ANy idea why?
dave

Big Cartoon DataBase
Big Comic Book DataBase
Quote Reply
Re: [carfac] Short Script Not Updating Database In reply to
Hi,

Mmm weird - you running from SSH or the browser?

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] Short Script Not Updating Database In reply to
SSH

Also, I am running this on 4 different fields (Director, ANimator, Writer and Producer)- which is why I have to put "Job" in there. All 4 do the same- only run the first 255 names....
dave

Big Cartoon DataBase
Big Comic Book DataBase
Quote Reply
Re: [carfac] Short Script Not Updating Database In reply to
I figgered it out. It dumps is there is no data in that field.
dave

Big Cartoon DataBase
Big Comic Book DataBase
Quote Reply
Re: [carfac] Short Script Not Updating Database In reply to
OK, I tried this and it does not work-

Code:
sub update_director {
# --------------------------------------------------------

my $sth = $DB->table('Links')->select( ['Producer'] );

while (my $director = $sth->fetchrow) {

if ($director) {

foreach (split /, /, $director) {
if ($DB->table('People')->count( { Name => $_, Job => "Producer" } ) < 1) {
$DB->table('People')->add( { Name => $_, Job => "Producer" } );

}
}
}
}
}

1;

what else should I try?
dave

Big Cartoon DataBase
Big Comic Book DataBase
Quote Reply
Re: [carfac] Short Script Not Updating Database In reply to
Try with LENGTH ..i.e

my $sth = $DB->table('Links')->select( ['Producer'], GT::SQL::Condition->new("LENGTH(Producer)",'>','0') );

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] Short Script Not Updating Database In reply to
Wow- cool fix. I was not thining that way.

Thanks Andy!
dave

Big Cartoon DataBase
Big Comic Book DataBase