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]

Reply via email to