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 ";
$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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
