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)
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.
# ---------------------------------------------
# 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)
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
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)
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