tatty27 tatty27 - 4 months ago 7
MySQL Question

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

Answer

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.