[ic] HELP! query returns wrong values (but right number)
Bill Eichin
bill at eichin.org
Tue Mar 29 11:45:25 EST 2005
This is just too weird, so I thought I'd share with the rest of the
class ;) I'm dealing with the 'multiple categories per item' issue that
I've seen here over the past few years by creating a separate table to
search. This works, almost....
I use this table and a scan query to build a link for the category list
on the left-hand side of the page. (Code included below.) The tables
are in postgresql, as follows (if you don't mind reading SQL):
CREATE TABLE department
(
id serial NOT NULL,
name varchar(128) NOT NULL,
"desc" text,
CONSTRAINT deptid PRIMARY KEY (id)
)
CREATE TABLE aisle
(
id serial NOT NULL,
name varchar(64) NOT NULL,
"desc" text,
CONSTRAINT aisleid PRIMARY KEY (id)
)
CREATE TABLE prodcat
(
id serial NOT NULL,
sku varchar(64) NOT NULL,
dept int8 NOT NULL,
aisle int8 NOT NULL,
CONSTRAINT prodcatid PRIMARY KEY (id),
CONSTRAINT catprod FOREIGN KEY (sku) REFERENCES products (sku) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT prodaisle FOREIGN KEY (aisle) REFERENCES aisle (id) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT proddept FOREIGN KEY (dept) REFERENCES department (id) ON UPDATE CASCADE ON DELETE CASCADE
)
The first table, "department," replaces the "area" table in
functionality. I don't use the 'left' and 'top' designations;
basically, I'm trying to normalize the structure somewhat. (more on
that later.) The second table, "aisle," is the same, but replacing the
"cat" table.
The third table, "prodcat", is the index showing what products (by sku)
are in what combinations of department and aisle (or, prod_group and
category). The values are the numeric ID fields for the corresponding
tables, so lookups should be fast. In fact, here's the query I use as
an example:
select * from products, prodcat
where prodcat.sku = products.sku
and prodcat.aisle = 3
It happens that "aisle 3" contains exatly three SKUs: CT24, CT25, and CT26.
What I'm trying to do here is emulate the previous functionality (click
on a category on the left, and get a list of items in that category on
the right). It appears I have to convert this into a scan query to make
that happen; this is what I built:
[perl tables="products prodcat department aisle"]
my $outstring;
my $dbh=$Sql{products} or return "Not shared.";
my $sql="SELECT id,name FROM department" or Log("Table unavail");
my $sth=$dbh->prepare($sql);
$sth->execute;
my @deptrow;
while(@deptrow = $sth->fetchrow()) {
$outstring .= "<tr><td valign=top class='categorybar'><b>".$deptrow[1]."
</b></td></tr>\n<tr><td valign=top class='barlink'>";
my $sqm="SELECT DISTINCT id,name FROM aisle WHERE aisle.id=prodcat.aisle
AND prodcat.dept=".$deptrow[0];
my $sti=$dbh->prepare($sqm) or Log("Table unavail");
$sti->execute;
my @airow;
while(@airow = $sti->fetchrow()) {
my $querystring = " <a href=".$Tag->area( { href =>
'scan', arg => 'st=db/sf=prodcat:aisle/se='.$airow[0].'/nu=1' } ).">".$airow[1].
"</a><br>";
# Log($airow[1]."=".$airow[0]);
$outstring .= $querystring;
}
$outstring .= "</td></tr>\n";
}
return $outstring."\n";
[/perl]
The results I see, however, are three different SKUs: K17, K17V, K17P.
These are all in different categories, none of which are category 3.
So, I guess the questions are:
1) is there another way I can write the link so the query occurs in SQL?
2) is there another way to write the scan query so it works?
3) basically, what am I doing wrong here?
Thanks, in advance, for your support and input.
--Bill
Eichin
More information about the interchange-users
mailing list