Build an advanced results page (PHP)

[shop73]

Hello,

can You help me build an advanced results page in Dreamweaver? I have three tables (products, categories and vendor). Table products has the fields: productID, productName, categoryID, vendorID. Table category has the fields: categoryID, categoryName. Table vendor has the fields: vendorID, vendorName. I put two parameters for search (categoryName and vendorName) on search.php page in two dinamic menu fields (menu category and menu vendor). On the Result Page I make recordset (rsResults) with SQL query:

SELECT products.productID, products.productName, categories.categoryName, vendors.vendorName
FROM products, categories, vendors
WHERE products.categoryID = categories.categoryID AND products.vendorID = vendors.vendorID

This SQL query returns all products. How to display only products that are given by search parameters?

Example1: category = monitor and vendor = Sony. How to show all products where is category monitor and vendor is Sony?

Example2: category = DVD-RW and vendor = Sony.

Thank you very much

 

[traingamer]

Maybe you should use an inner join instead of a cartesian join.
Something like (not tested)

SELECT products.productID, products.productName, categories.categoryName, vendors.vendorName
FROM products
  INNER JOIN categories ON
     products.categoryID = categories.categoryID 
  INNER JOIN vendors 
     ON products.vendorID = vendors.vendorID 
WHERE categories.categoryName = "monitor" 
   AND vendors.vendorName = "SONY"

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard