Ok, I've fifured a few possible solutions that seem really
inefficient, but I'm wondering if anyone else has a better idea...
running php/mysql
while displaying product in a store site you click on a category,
this brings up a page of manufacturers of stuff in the category as
well as a link to all of the items by every manufacturer in that
category.
if i select a manufacturer i am supposed to display all items in the
selected category first, then all items not in that category below.
i tried using two queries
SELECT DISTINCT p.Name, p.Prod_ID
FROM Product p, Product_Category pc
WHERE p.In_Stock = 1 AND p.Prod_ID = pc.Prod_ID
AND pc.Cat_ID = '$Cat_ID' AND p.Man_ID = '$Man_ID'
then display results in the selected category
SELECT DISTINCT p.Name, p.Prod_ID
FROM Product p, Product_Category pc
WHERE p.In_Stock = 1 AND p.Prod_ID = pc.Prod_ID
AND pc.Cat_ID != '$Cat_ID' AND p.Man_ID = '$Man_ID'
display rest of the manufacturer's products *not* in the selected category...
the problem then comes when a product is in more than one category
(which is why there is a "Product_Category" table in the first place:)
it still shows up in the bottom query (sure, it is not equal to the
selected category, but it *is* equal to another category)
my two solutions -
3 queries, first query creates a temp table from the result of the
first query above, 2nd query to pull info from the temp table and
display it, 3rd query to do the second query from above, but instead
of "pc.Cat_ID != '$Cat_ID'", compare
p.Prod_ID != temp.Prod_ID.
the second solution would be similar, but using arrays. run the first
query from above and as it is displayed, build an array of Prod_IDs
used. then run the second query and put a condition in that only
displays if the returned Prod_ID is not an element of the array.
which of these solutions would be more efficient? and are there any
more elegant/more efficient ways of doing this?
thanks in advance,
--
__ Tom B ____________
/ /\ /\ _________\
/ / \ http://www.stypica.org \ \ \______ /
/ / /\ \ \ \ \ / / /
/ / /\ \ \ "It's ok honey, Just go in \ \ \/ / /
/ /_/__\ \ \ my bathroom and find \ \/ / /
/________\ \ \ something sticky" - Suriel \ / /
\___________\/ \/_/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
