MYSQL Query Across Multiple Columns and Tables

[koopkoop]

I'm really new at MySQL and having a tough time with a query problem across multiple coulmns and tables:

 

I'm trying to create a "easy to use" search box on an e-commerce website. When a search term is plugged in, it'll search the "Products" table in columns "product names", "product ID numbers" and "product categories". If the term matches in any of those columns, it'll then pull the image location from the same table and then pull pricing information from a second "price" table based on the common product ID. The search results are not as expected though. If I search for a term that I know matches (eg. a productID of "123"), it'll return a huge amount of hits with nothing but the same product repeating over and over.

 

I troubleshot it by slowly eliminating terms from my query and it turns out it's the "OR"s that are fouling the search. I know this fixes it, but I don't understand why. Obviously, I'm using the wrong query string, but what do I replace it with to get the functionality I described above? Queries below:

 

 

BROKEN QUERY:

 

SELECT products.itemnumber,products.name,products.midimagename,prices.bestprice

FROM products,prices

WHERE products.category LIKE '%fall%' 

OR products.itemnumber LIKE '%fall%'

OR products.name LIKE '%fall%'

AND products.itemnumber = prices.itemnumber

 

 

 

 

SEMI-FIXED QUERY (It now doesn't do what I need, but at least it doesn't repeat the same search hit over and over):

 

SELECT products.itemnumber,products.name,products.midimagename,prices.bestprice

FROM products,prices

WHERE products.category LIKE '%fall%' 

prices.itemnumber

[Mchl]

You should probably use () to group our conditions in a logic way. It seems you might want it like this:

 

SELECT products.itemnumber,products.name,products.midimagename,prices.bestprice

        FROM products,prices

        WHERE

          (products.category LIKE '%fall%'

          OR products.itemnumber LIKE '%fall%'

        OR products.name LIKE '%fall%')

        AND products.itemnumber = prices.itemnumber