CODEWITHSUNDEEP
mysqlsql
tatty27
tatty27

Reputation: 1554

MySQL multiple arguments query

I am trying to write a query for the following...

SELECT SQL_CALC_FOUND_ROWS * FROM calls 
WHERE id IS NOT NULL
AND account_name LIKE '%$account_search%'
AND name LIKE '%$adv_term_det%' OR description LIKE '%$adv_term_det%'
AND type != 'online'
AND marked_completed_by = ''
ORDER BY $sort_by DESC LIMIT $page_position, $item_per_page

But the results are not the expected values. The line I suspect is causing the issue is..

AND name LIKE '%$adv_term_det%' OR description LIKE '%$adv_term_det%'

Which I suspect should be a subquery but after several attempts to use IN I haven't been able to make it work.

SELECT SQL_CALC_FOUND_ROWS * FROM calls 
WHERE call_ref IN
  (SELECT call_ref FROM calls
   WHERE name LIKE '%$adv_term_det%' OR description LIKE '%$adv_term_det%'
  )
AND id IS NOT NULL
AND account_name LIKE '%$account_search%'
AND type != 'online'
AND marked_completed_by = ''
ORDER BY $sort_by DESC LIMIT $page_position, $item_per_page;

Basically I want to return all records that meet all the other criteria and have $adv_term_det in either name or description as well.

If I use just AND name LIKE '%$adv_term_det%' I get the results I expect for that query and likewise if I use just AND description LIKE '%$adv_term_det%' but I need to find records that have '%$adv_term_det%' in either name or description

Upvotes: 0

Views: 1678

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Either you are not very experienced with complex boolean logic or you just missed the error. In either case, I would advise you to use parentheses, particularly when mixing AND and OR. I think this is the logic you intend:

WHERE (id IS NOT NULL) AND
      (account_name LIKE '%$account_search%') AND
      (name LIKE '%$adv_term_det%' OR description LIKE '%$adv_term_det%') AND
      (type <> 'online') AND
      (marked_completed_by = '')

Also note that plugging variables directly into the SQL string is dangerous. You should be using parameters for that purpose.

Upvotes: 1

Related Questions