1

I hope someone can help with the following. I'm trying to create a user-specific search feature. When someone is logged in, they can run a search which should return results from either the customer_ref or order_details columns.

I have tried the following, but it returns results from different customers.

SELECT *
FROM `job` 
WHERE c_name = 'John Doe'
AND customer_ref LIKE '%do%'
OR order_details LIKE '%do%'

I want to restrict the results to a specific customer (for obvious reasons). If I remove the bottom line (OR order_details LIKE '%do%'), the results are locked into the customer correctly, however, when it's there I get results including other customer names.

Note that %do% is just a wildcard search term indicating door or Doreen (for testing). In the actual code it would be the value entered in the search box.

Many thanks in advance!

Leigh
  • 28,765
  • 10
  • 55
  • 103
wiggy1977
  • 33
  • 8

2 Answers2

2

You need to include round brackets in your query to clarify on what terms you are searching:

SELECT *
FROM `job` 
WHERE c_name = 'John doe'
AND (customer_ref LIKE '%do%'
OR order_details LIKE '%do%')
Leigh
  • 28,765
  • 10
  • 55
  • 103
Matt Healy
  • 18,033
  • 4
  • 56
  • 56
  • 1
    @wiggy1977 - Fyi, using both the `AND` and `OR` operators creates ambiguity. Parenthesis tell the database how it should process the conditions. Otherwise, the database applies its own rules, which may or may not be what you want. You may be interested in this article: [How Operator Precedence Affects MySQL SELECT Queries](http://www.databasejournal.com/features/mysql/article.php/3904221/How-Operator-Precedence-Affects-)MySQL---SELECT-Queries.htm – Leigh Apr 08 '12 at 01:33
0

try using CONCAT for example:

SELECT *
FROM job 
WHERE job.c_name = 'John doe' AND
(job.customer_ref LIKE CONCAT '%do' 
OR job.order_details LIKE CONCAT '%do')
mykey
  • 575
  • 2
  • 10
  • 24
  • Is there a benefit using concat? – wiggy1977 Apr 08 '12 at 17:27
  • yes if the value entered in the search box is from two columns of the database e.g **door Doreen**. example of usage in this question http://stackoverflow.com/questions/4005251/select-from-tbl-where-clm-like-concat-other-sql-query-limit-1-how – mykey Apr 08 '12 at 19:25