Gossamer Forum
Home : Products : DBMan SQL : Discussion :

relational query using Globals - Help

Quote Reply
relational query using Globals - Help
Hi,

I'm having a heck of time getting a relational query going in DBSQL 2.02 - (I'm trying to learn object-oriented Perl but it is slow-going at this point)Crazy

Basically what follows is the code that works fine in DBSQL 1.x to return a list of related sub-categories for any specific Category. I just can't seem to do it properly for a DBSQL 2.02 global.

Code:
sub build_select_Sub_Category {
# ---------------------------------------------
# Builds a list from fields in the database.
#

my $category = $in{'Category'};
my $name = 'Sub_Category_ID';
my $table1 = 'catalog';
my $table2 = 'catalog_sub_category';
my $column1 = 'Sub_Category_ID';
my $column2 = 'Sub_Category';
my $category_ID = $in{'Category_ID'};

my $query = qq!
SELECT DISTINCT $table1.$column1, $table2.$column2 FROM $table1, $table2 WHERE Category_ID = $category_ID AND $table2.$column1 = $table1.$column1 ORDER BY Sub_Category
!;
my $sth = $DBH->prepare ($query) or &cgierr("Unable to query database. Reason: $DBI::errstr. Query: $query");
$sth->execute or &cgierr("Unable to query database. Reason: $DBI::errstr. Query: $query");

# Make a list
while (($column1, $column2) = $sth->fetchrow_array) {
($output .= "<a href=$db_script_url?db=$db_setup&Category_ID=$category_ID&Sub_Category_ID=$column1&sb=Title&so=asc&view_records=View&nh=1>$column2</a><br><br>\n");
}
$sth->finish;
return $output;
}

What the above does (at least in DBSQL 1.x it works) is return a list of available sub-categories within a specific category. Here's what I have for now for a DBSQL 2.02 global to accomplish the same thing (it's gone through many iterations but nothing I seem to do works)
Code:
sub {
my $tags = shift;
my $id = $tags->{Category_ID};
my $table = $DB->table('catalog', 'catalog_sub_category');
$table->select_options ("DISTINCT", "ORDER BY Sub_Category");
my $sth = $table->select (['Sub_Category_ID', 'Sub_Category'], { Category_ID => $id });
my $output;
my $num;
while (my $row = $sth->fetchrow_hashref) {
$num = $num + 1;
($output .= "<font face=\"verdana,arial,helvetica\" size=\"-2\" color=red>$num.</font> <font face=\"verdana,arial,helvetica\" size=\"-2\"><a href=db.cgi?&sid=$tags->{session_id}>$row->{Sub_Category}</a></font><br><br>\n");
}
return $output;
}

The part I can't seem to get right is the actual $table->select statement - no matter what permutation of variables etc. I put there, I get errors/failure - most often a "GT::SQL::Relation (5539): Bad columns / column clash: columns named 'Sub_Category_ID' have been found in current relation, please qualify your expression. at (eval 10) line 6."

I realize any/or all of my syntax for this global are wrong - can someone give me a nudge in the right direction? Thanks.

easy does it
Subject Author Views Date
Thread relational query using Globals - Help Bearwithme 6236 Nov 12, 2001, 1:31 PM
Thread Re: [Bearwithme] relational query using Globals - Help
Alex 6163 Nov 13, 2001, 11:06 AM
Thread Re: [Alex] relational query using Globals - Help
Bearwithme 6126 Nov 13, 2001, 11:56 AM
Post Post deleted by Bearwithme
Bearwithme 6067 Nov 14, 2001, 9:09 AM
Thread Re: [Bearwithme] relational query using Globals - Help
Alex 6067 Nov 16, 2001, 3:34 PM
Thread Re: [Alex] relational query using Globals - Help
Bearwithme 6026 Nov 16, 2001, 3:56 PM
Post Re: [Bearwithme] relational query using Globals - Help
Bearwithme 6051 Nov 18, 2001, 4:11 AM
Thread Re: [Bearwithme] relational query using Globals - Help
Bearwithme 5991 Nov 22, 2001, 9:48 PM
Thread Re: [Bearwithme] relational query using Globals - Help
jean 5960 Nov 23, 2001, 9:57 AM
Thread Re: [jean] relational query using Globals - Help
Bearwithme 5914 Nov 23, 2001, 12:06 PM
Thread Re: [Bearwithme] relational query using Globals - Help
jean 5978 Nov 23, 2001, 12:16 PM
Thread Re: [jean] relational query using Globals - Help
jean 5919 Nov 23, 2001, 6:20 PM
Thread Re: [jean] relational query using Globals - Help
Bearwithme 5949 Nov 23, 2001, 8:47 PM
Post Re: [Bearwithme] relational query using Globals - Help
Halito 5755 Mar 5, 2002, 8:13 AM
Post Re: [Alex] relational query using Globals - Help
Bearwithme 6093 Nov 14, 2001, 9:21 AM
Post Re: [Bearwithme] relational query using Globals - Help
Bearwithme 6099 Nov 14, 2001, 10:35 PM
Post Re: [Bearwithme] relational query using Globals - Help
Bearwithme 6038 Nov 16, 2001, 1:07 AM