Nick Zukin wrote:
I'm trying to do a multitable query and am having problems.
I have three tables: vendors, products, and vendorproducts. The vendorproducts table creates a many to many relationship between the vendors and the products. There is nothing more than the vendor and product ids in the vendorproducts table.
I want to be able to create a query that will find vendors who have certain products. However, I'm trying to make a keyword search (PHP/MySQL) so that using form data I can search multiple columns for the same keyword. Here's how I am currently doing the query:
$query = "SELECT v.vbusiness, v.vcategory, v.vurl, v.vcity, v.vstate, v.vendorid "; $query .= "FROM vendorproducts AS vp "; $query .= "INNER JOIN vendors AS v ON vp.vpvendorid = v.vendorid "; $query .= "INNER JOIN products AS p ON vp.vpvendorid = p.productid ";
^^^^^^^^^^ I'd guess the problem is right there. Shouldn't that be something like
INNER JOIN products AS p ON vp.vpproductid = p.productid
instead?
$query .= "WHERE (p.productname LIKE '%".$_GET['keyword']."%') "; $query .= "OR (p.productfamily LIKE '%".$_GET['keyword']."%') "; $query .= "OR (v.vcategory LIKE '%".$_GET['keyword']."%') "; $query .= "GROUP BY v.vbusiness ";
As an example, it might look like this:
SELECT v.vbusiness, v.vcategory, v.vurl, v.vcity, v.vstate, v.vendorid FROM vendorproducts AS vp INNER JOIN vendors AS v ON vp.vpvendorid = v.vendorid INNER JOIN products AS p ON vp.vpvendorid = p.productid WHERE (p.productname LIKE '%Apples%') OR (p.productfamily LIKE '%Apples%') OR (v.vcategory LIKE '%Apples%') GROUP BY v.vbusiness
Where am I going wrong? The results aren't random, but I can't see how they're coming up with what they're coming up with.
TIA,
Nick
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
