MySQL multiple arguments query

Question

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

Gordon Linoff · Accepted Answer · 2016-07-28 16:26:01Z

1

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.

answered Jul 28, 2016 at 16:26

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tatty27 Over a year ago

Thanks, works great. I am indeed a work in progress and learning everyday. I knew about adding parentheses for queries in other languages but wasn't aware you could use them in SQL too. Just out of curiosity can you clarify what you mean by parameters rather than variables?

Gordon Linoff Over a year ago

Hmmm . . . The place to start is with parameterized queries. This is important when you are doing application development. If you are just playing around now, then it might be a confusion. At some point, though, you should learn about SQL Injection, dynamic SQL, and parameterized queries.

Collectives™ on Stack Overflow

MySQL multiple arguments query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related