Gossamer Forum
(Advanced Search)
for
Home : Products : Gossamer Links : Discussions :

trouble accessing table from plugin...

Mar 3, 2004, 12:00 AM
Swaylock
User (181 posts)
Mar 3, 2004, 12:00 AM
Post #1 of 5
Views: 1763
Shortcut
Quote Reply
trouble accessing table from plugin...
Hi all,

I'm having the damndest time trying to load up some information from a custom table I've made. The table is active and working and I've accessed it from various other functions in similar ways.

The table is a basic reference table similar to the CatLinks table. It has two columns LinkID and VerticalID (references a custom Vertical industry table.)

Anyway, I'm writing a plugin for the admin view so admins can seach and list all of Links by Vertical. Here's the basics of my Plugin:


Code:
# ==================================================================
# Plugins::search_verts - Auto Generated Program Module
#
# Plugins::search_verts
# Author : MP
# Version : 1
# Updated : Tue Mar 2 13:35:44 2004
#
# ==================================================================
#

package Plugins::search_verts;
# ==================================================================

use strict;
use GT::Base;
use vars qw/$VERSION $DEBUG $NAME $FONT/;
use GT::Plugins qw/STOP CONTINUE/;
use Links qw/$CFG $IN $DB/;
use GT::Dumper;
use GT::SQL;
require Links::Link;

# Inherit from base class for debug and error methods
@Plugins::search_verts::ISA = qw(GT::Base);

# Your code begins here! Good Luck!


# ADMIN MENU OPTIONS
# ===================================================================

sub print_form {
# -------------------------------------------------------------------
# This subroutine will get called whenever the user clicks
# on 'Search Verticals' in the admin menu. Remember, you need to print
# your own content-type headers; you should use
#
my $vert = "";
my $results = "what the fuck";
my $db = new GT::SQL '/var/www/clients.kbpwest.com/cisco/content_dev/admin/defs';

# Do something useful here
print $IN->header;
print Dumper(\@args);

# create the form element
my $output = Links::Link::HTML::disp_verticals_form();

# get the verts searched
my $vert_db = $DB->table ('Verticals');
my $vertlink_db = $DB->table('VertLinks');
my $linkdb = $DB->table ('Links');

if ($IN->param('VerticalID') != ""){
my @vert_ids = $IN->param('VerticalID');
foreach my $vert_id (@vert_ids) {

# locate the vertical name being searched
my @vert_names = $vert_db->select ( { ID => $vert_id } , ['Name'] )->fetchall_list;
foreach my $item(@vert_names){
$vert .= "You searched: $item\n";
}

# locate the links
my @links = $vertlink_db->select (['LinkID'], { VerticalID => $vert_id })->fetchall_list;
foreach my $link(@links){
# do something
my $results .= "link id found: $link\n";
}
}
return $results;
}
}

For some weird reason, NO MATTER WHAT I TRY, I just can't access the VertLinks table from the plugin. I've tried to feed the select function conditional statements, a plain number variable, and nothing works. I've also tried to call a similar Link ID list using the CatLinks table and no joy. Is there something I should know about accessing a reference table like this from a plugin? What's going on here?Crazy

Thanks,
Mike
Mar 3, 2004, 2:12 AM
Andy
Veteran / Moderator (18441 posts)
Andy's avatar
Mar 3, 2004, 2:12 AM
Post #2 of 5
Views: 1704
Shortcut
Quote Reply
Re: [Swaylock] trouble accessing table from plugin... In reply to
Code:
my $db = new GT::SQL '/var/www/clients.kbpwest.com/cisco/content_dev/admin/defs';

You shouldn't need to do the above. Try something like;

Code:
my $table = $DB->table('Verticals') || die $GT::SQL::error;

$DB already holds the GT::SQL object, so there is no need to re-make this.

Also, be sure that in your /admin/defs/ folder you have prefix_Verticals.def , otherwise LSQL has no way to know which fields exist in the table (for speed purposes, LSQL holds the tables values in these files).

Hope that helps.

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!
Mar 3, 2004, 9:20 AM
Swaylock
User (181 posts)
Mar 3, 2004, 9:20 AM
Post #3 of 5
Views: 1691
Shortcut
Quote Reply
Re: [Andy] trouble accessing table from plugin... In reply to
Andy,

Thanks, I did what you suggested -- the database select throws out no error. Also my prefixes are ok.

It's just so WEIRD Unsure -- select is working fine on the Verticals table but the VertLinks reference table gives up nothing.

I've done a bit more snooping around and for the hell of it ran dumps on the tables at the end of the entire script. I'm sorry to throw so much code out but perhaps a DB expert can see something here.

(I've taken out some security sensitive code).
Code:
my (@args) = $DB->table('VertLinks');
print $IN->header;
print Dumper(\@args);


$VAR = [
bless(
{
'last_hits' => '10',
'sel_opts' => [],
'_index' => '0',
'_err_pkg' => 'GT::SQL::Table',
'name' => 'cis_VertLinks',
'driver' => bless(
{
'dbh' => bless(
{},
'DBI::db'
),
'sth' => bless(
{},
'DBI::st'
),
'_err_pkg' => 'GT::SQL::Table',

I thought the problem might be on this next line -- but you can see that the condition is being set up fine lower down in the dump...

Code:
'query' => 'SELECT LinkID FROM cis_VertLinks WHERE VerticalID = ? ',
'name' => 'cis_VertLinks',
'driver' => 'mysql',
'_debug' => '1',
'connect' => {
},
'schema' => {
'pk' => [],
'cols' => {
'LinkID' => {
'not_null' => '1',
'form_type' => 'TEXT',
'form_size' => '10',
'unsigned' => '1',
'pos' => '1',
'zerofill' => '0',
'default' => '0',
'type' => 'INT'
},
'VerticalID' => {
'not_null' => '1',
'form_type' => 'TEXT',
'form_size' => '10',
'unsigned' => '1',
'pos' => '2',
'zerofill' => '0',
'default' => '0',
'type' => 'INT'
}
},
'ai' => '',
'index' => {
'vertlnndx' => [
'VerticalID'
],
'lndx' => [
'LinkID'
]
},
'fk_tables' => [],
'fk' => {
'cis_Links' => {
'LinkID' => 'ID'
},
'cis_Verticals' => {
'VerticalID' => 'ID'
}
},
'subclass' => {
'table' => {},
'html' => {}
},
'unique' => {}
}
},
'GT::SQL::Driver::MYSQL'
),
'_file' => '0',
'last_where' => bless(
{
'bool' => 'AND',
'not' => '0',

See here -- it seems like the where statement is making it through on both queries. It just doesn't work on this table!

Code:

'cond' => [
[
'VerticalID',
'=',
'4'
]
]
},
'GT::SQL::Condition'
)


my (@args) = $DB->table('Verticals');
print $IN->header;
print Dumper(\@args);

$VAR = [
bless(
{
'last_hits' => '1',
'sel_opts' => [],
'_index' => '0',
'_err_pkg' => 'GT::SQL::Table',
'name' => 'cis_Verticals',
'driver' => bless(
{
'dbh' => bless(
{},
'DBI::db'
),
'sth' => bless(
{},
'DBI::st'
),
'_err_pkg' => 'GT::SQL::Table',
'query' => 'SELECT Name FROM cis_Verticals WHERE ID = ? ',
'name' => 'cis_Verticals',
'driver' => 'mysql',
'_debug' => '1',
},
'schema' => {
'pk' => [
'ID'
],
'cols' => {
'ID' => {
'not_null' => '1',
'pos' => '1',
'zerofill' => '0',
'type' => 'INT',
'default' => '',
'unsigned' => '1'
},
'Name' => {
'not_null' => '1',
'form_type' => 'TEXT',
'form_size' => '30',
'weight' => '',
'regex' => '',
'binary' => '0',
'form_display' => 'Name',
'file_save_in' => '',
'file_save_scheme' => 'HASHED',
'pos' => '2',
'file_max_size' => '',
'size' => '255',
'type' => 'CHAR',
'default' => ''
}
},
'ai' => 'ID',
'index' => {},
'fk_tables' => [],
'fk' => {},
'subclass' => {
'table' => {},
'html' => {}
},
'unique' => {}
}
},
'GT::SQL::Driver::MYSQL'
),
'_file' => '0',
'last_where' => bless(
{
'bool' => 'AND',
'not' => '0',
'cond' => [
[
'ID',
'=',
'4'
]
]
},
'GT::SQL::Condition'
)


Again sorry for so much code -- but here's the whole plugin.pm code for reference.

Code:
# ==================================================================
# Plugins::search_verts - Auto Generated Program Module
#
# Plugins::search_verts
# Author : MP
# Version : 1
# Updated : Tue Mar 2 13:35:44 2004
#
# ==================================================================
#

package Plugins::search_verts;
# ==================================================================

use strict;
use GT::Base;
use vars qw/$VERSION $DEBUG $NAME $FONT/;
use GT::Plugins qw/STOP CONTINUE/;
use Links qw/$CFG $IN $DB/;
use GT::Dumper;
use GT::SQL;

# Inherit from base class for debug and error methods
@Plugins::search_verts::ISA = qw(GT::Base);

# Your code begins here! Good Luck!


# ADMIN MENU OPTIONS
# ===================================================================

sub print_form {
# -------------------------------------------------------------------
# This subroutine will get called whenever the user clicks
# on 'Search Verticals' in the admin menu. Remember, you need to print
# your own content-type headers; you should use
#

require Links::Link;
my $vert = "";
my $output2 = "results:";

my $vert_db = $DB->table ('Verticals')|| die $GT::SQL::error;
my $vertlink_db = $DB->table('VertLinks')|| die $GT::SQL::error;
my $linkdb = $DB->table ('Links')||die $GT::SQL::error;

# create the form element
my $form = Links::Link::HTML::disp_verticals_form(); # this is my hack in Links::Link.pm :(

if ($IN->param('VerticalID') != ""){
my @vert_ids = $IN->param('VerticalID');
foreach my $vert_id (@vert_ids) {

#if ($IN->param('VertLinks.VerticalID') != ""){
# my @vert_ids = $IN->param('VertLinks.VerticalID');
# foreach my $vert_id (@vert_ids) {

# locate the vertical name being searched
my @vert_names = $vert_db->select ( { ID => $vert_id } , ['Name'] )->fetchall_list;
foreach my $item(@vert_names){
$vert .= "You searched: $item\n";
}

# locate the links
my @results = $vertlink_db->select (['LinkID'], { VerticalID => $vert_id })->fetchall_list;
foreach my $result(@results){
# do something
my $output2 .= "link id found: $result\n";

#foreach my $linkID(@linkIDs){
#my $rec = $db->get ($linkID);
#$output .= Links::SiteHTML::display ('link', $rec);
#}
#my $sth = $linkdb->select( {ID => $link });
#while (my $result = $sth->fetchrow_hashref) {
# print "<p>", Links::Link::HTML::display ( { mode => 'search_results', values => $result });
#}
}
}
}

# Do something useful here
my (@args) = $DB->table('Verticals');
print $IN->header;
print Dumper(\@args);

print qq~
<html>
<head>
<title>Verticals Search</title>
</head>
<body bgcolor="#FFFFFF">

<p><b>RESULTS:</b><br>
$vert<br>
$output2</p>
<form action="admin.cgi" method="GET">
<input type=hidden name="do" value="plugin">
<input type=hidden name="func" value="print_form">
<input type=hidden name="plugin" value="search_verts">
<p>$form</p>
<input type=submit name="action" value="search">
</form>
</body>
</html>~;
}
# Always end with a 1.
1;

So that's as far as I'm gotten. The plugin works in that no errors are being thrown out. The form is working ok as I can access what is being passed. It's just that I can't pull LinkID's out of the LinkIDxVerticalID table based on VerticalID.

I need wisdom...Pirate

Thanks,
Mike
Mar 3, 2004, 12:23 PM
afinlr
Veteran (1921 posts)
Mar 3, 2004, 12:23 PM
Post #4 of 5
Views: 1681
Shortcut
Quote Reply
Re: [Swaylock] trouble accessing table from plugin... In reply to
First suggestion: remove the 'my' in front of $output2

my $output2 .= "link id found: $result\n";
Mar 3, 2004, 12:36 PM
Swaylock
User (181 posts)
Mar 3, 2004, 12:36 PM
Post #5 of 5
Views: 1688
Shortcut
Quote Reply
Re: [afinlr] trouble accessing table from plugin... In reply to
UUUUGGGHHHH!!!!!!

That was the problem EXACTLY!!!
I'm so damn SLOPPY!!!

Well now, isn't banging your head afainst the wall for 5 hours on the simplest of problems the best way to learn something?

Many MANY thanks...
Mike

Last edited by:

Swaylock: Mar 3, 2004, 12:39 PM