retrieve all data from a previous purchase

[SeenGee]

I am trying to query two tables, products(which contains all details about actual products) and salesproducts which holds a userid(from users), a productid(from products), an ordernumber(from salesorders) and a quantity value for that product.

I want to query the tables so that i can retrieve all information related to a specific purchase by the user. i am passing across $userid and $orderid, these vars are accessible to a sql query that i call that looks like this

SELECT products.brand, products.code, products.price, products.rrp, salesproducts.quantity, salesproducts.ordernumber, salesproducts.productid, salesproducts.userid
FROM `products` , `salesproducts` 
WHERE salesproducts.userid = '1' AND salesproducts.ordernumber = '14' LIMIT 0 , 30 

However, this query is not running properly, i just want it to use orderid and userid to identify all products asssociated with a specific visit (orderid), and display them as a group, i.e. all products with an orderid of 14, the quantities should also remain true, as it is the query i'm using seems to be seperating the quantities from each other.

Thanks.

[nemonoman]

Your code could be providing you a confusing cartesian product because you have not established the JOIN condition for the product and sales table. This will result in every line of the order * every product in the database.

add this code:

AND salesproduct.productid=product.id

SELECT products.brand, products.code, products.price, products.rrp, salesproducts.quantity, salesproducts.ordernumber, salesproducts.productid, salesproducts.userid
FROM products , salesproducts
WHERE salesproducts.userid = '1' AND salesproducts.ordernumber = '14'
AND salesproduct.productid=product.id