CODEWITHSUNDEEP
phpmysqlsqlsearch
Bambarbia
Bambarbia

Reputation: 1

Remove not found word from mySQL query

I am trying to make search and need to remove word from query, that do not have match.

Lets say there is 3 words: one, two and three. If one is not found, then I need to rerun query with other two words (I get result Where empty_space AND two AND three, so query finds nothing). And so on. But I don't know how to identify unmatched word and run query again with other two words.

EDIT: If I use this code, to count elements in array with $i++, query - one two three:

if(!empty($d)) {
     if($i < 2) {
      $like_str .= " (title LIKE '%".$keyword."%')";
     } else {
      $like_str .= " AND (title LIKE '%".$keyword."%')";
     }
    }

Result: SELECT * FROM shop_products_ WHERE AND (title LIKE '%two%') AND (title LIKE '%three%')

And with words that exist, query - one two

if(!empty($d)) {
     if($key < 1) {
      $like_str .= " (title LIKE '%".$keyword."%')";
     } else {
      $like_str .= " AND (title LIKE '%".$keyword."%')";
     }
    }

Result: SELECT * FROM shop_products_ WHERE (title LIKE '%one%') AND (title LIKE '%two%')

Upvotes: 0

Views: 58

Answers (2)

pid
pid

Reputation: 11597

Simply don't AND the constraints but OR them. Any unmatched word will not interfere with the others, like this:

WHERE w = 'one' OR w = 'two' OR w = 'three'

If one does not exist, it will be identical to:

WHERE w = 'two' OR w = 'three'

Anyways, I'd recommend to NOT paste your SQL query out of strings in PHP, this is very much subject to SQL-injection and a totally abandoned and discouraged practice. What you should use are parameters, depending on the actual implementation (mysqli? PDO?) similar to this:

WHERE w = ? OR w = ? OR w = ?

On the other hand, if you need an arity of n, you may want to use the IN clause:

WHERE w IN ('one', 'two', 'three')

But here you can't use parameters because of technical constraints. More about it here: Binding parameters for WHERE IN clause with PDO

Upvotes: 1

Nightwhistle
Nightwhistle

Reputation: 198

Try to make an array with words that are found, like:

$a = array('two','three');

and then implode it with " AND ".

$matches  = implode(" AND ", $a);

Upvotes: 0

Related Questions