I have three tables, the first two fairly standard:
1) PRODUCTS table:
2) CART table:
The third is designed to let people save products they buy and keep notes on them.
3) MYPRODUCTS table:
PRODUCTS.prod_id = CART.cart_prodid = MYPRODUCTS.myprod_pid
When a user orders, they are presented with a list of products on their order, and can optionally add that product to myproducts. I am getting the info necessary for them to do this, with a query something like this for each order:
SELECT cart.pid, products.pname, products.pid
FROM products, cart
WHERE products.pid = cart_prodid
AND cart_orderid=orderid
This is fine the first time they order.
However, if they subsequently reorder a product which they have already added to myproducts, then it should NOT be possible for them to add it to myproducts again - basically instead of 'Add to MyProducts' they need to see 'View in MyProducts'.
I am thinking I can separate the products using two queries:
Products never added to MyProducts
By somehow identifying whether the user has the product in MyProducts already, and if so excluding it from the query above.
Products already in MyProducts
By reversing the process above.
I need some pointers on how to do this though.
Products they've ordered before:
SELECT cart.pid, products.pname, products.pid
from products p
inner join cart c on p.prodid = c.cart_prodid
WHERE cart_orderid=orderid
Products they've never ordered before
SELECT products.pname, products.pid
from products p
left join myproducts mp ON p.prodid = mp.prodid
WHERE mp.prodid IS NULL
The above query may read more naturally as a NOT IN statement, which is to follow. I've prioritized the above because it is most likely a faster operation.
SELECT products.pname, products.pid
from products p
WHERE p.prodid NOT IN
(
SELECT prodid
FROM myproducts
)
Don't you need a User ID in your myproducts table?
This is not going to answer your specific problem, but have you considered a change in the user interface? Perhaps the user shouldn't control their list of purchased products. This seems like you are unnecessarily complicating your interface. You could automatically add every product they ordered to "My Products".
Also, I think you should keep track of how many products the user ordered of the same product ID. Put a "count" field in the myproducts table to keep track of this. You don't have to actually use this field, but you may eventually regret not adding this. Or even better - add a new row for every added product so you can keep track of the date/time as well.